-- САМА ТАБЛИЦА
CREATE TABLE "public"."products" (
"id" INTEGER DEFAULT nextval('"products_productid_seq"'::text) NOT NULL,
"productname" VARCHAR(100) NOT NULL,
"productmakerid" BIGINT,
"abbr" VARCHAR(100),
"lifetime" BIGINT,
"havelifetime" SMALLINT DEFAULT 1 NOT NULL,
"productimage" CHAR(254),
"parentid" INTEGER DEFAULT 0 NOT NULL,
"isnode" SMALLINT DEFAULT 0 NOT NULL,
"childcount" INTEGER DEFAULT 0 NOT NULL,
"isactive" BIGINT DEFAULT 1 NOT NULL,
PRIMARY KEY("id")
) WITH OIDS;

CREATE TRIGGER "products_after_delete" AFTER DELETE
ON "public"."products" FOR EACH ROW
EXECUTE PROCEDURE "public"."trg_products_after_delete"();

CREATE TRIGGER "products_after_insert" AFTER INSERT
ON "public"."products" FOR EACH ROW
EXECUTE PROCEDURE "public"."trg_products_after_insert"();

CREATE TRIGGER "products_after_update" AFTER UPDATE
ON "public"."products" FOR EACH ROW
EXECUTE PROCEDURE "public"."trg_products_after_update"();
-- table END ;-))

-- Триггер на удаление
CREATE FUNCTION "public"."trg_products_after_delete" () RETURNS trigger AS'
BEGIN
--if new.parentid <> null THEN
UPDATE products set childcount = childcount-1 where id = old.parentid;
--end if;
return new;
END;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

-- Триггер на добавление
CREATE FUNCTION "public"."trg_products_after_insert" () RETURNS trigger AS'
DECLARE
i integer;
BEGIN
--if new.parentid <> null THEN
UPDATE products set childcount = childcount+1 where id = new.parentid;
--end if;
return new;
END;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

-- И на изменение
-- Нужно проверять не изменился ли после апдейта родитель
-- И ,соответственно , контролировать childcount
BEGIN
if new.parentid <> old.parentid THEN
UPDATE products set childcount = childcount-1 where id = old.parentid;
UPDATE products set childcount = childcount+1 where id = new.parentid;
end if;
return new;
END;

-- AFTER UPDATE
CREATE FUNCTION "public"."trg_products_after_update" () RETURNS trigger AS'
BEGIN
if new.parentid <> old.parentid THEN
UPDATE products set childcount = childcount-1 where id = old.parentid;
UPDATE products set childcount = childcount+1 where id = new.parentid;
end if;
return new;
END;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

-- isnode всё же полезен , если вдруг смутит наличие такого поля , и
-- совершенно субъективное мнение : "boolean - недоотстой" ;-))