|
краткое описание как сделано
1. есть созданы две таблицы
CREATE TABLE "zagal"."tbl_reestr" ("id_reestr" uniqueidentifier NOT NULL DEFAULT newid(*), "id_typepolis" integer DEFAULT NULL, "id_buyer_of" uniqueidentifier DEFAULT NULL, "series" char(5) DEFAULT NULL, "numberpolis" integer DEFAULT NULL, "getdate" date DEFAULT NULL, "agdate" date DEFAULT NULL, "status" integer DEFAULT NULL, "numberact" integer DEFAULT NULL, "id_buyer_to" uniqueidentifier DEFAULT NULL, "representative_firma" varchar(100) DEFAULT NULL, "post_representative_firma" varchar(100) DEFAULT NULL, "representative_buyer" varchar(100) DEFAULT NULL, "post_representative_buyer" varchar(100) DEFAULT NULL , PRIMARY KEY ("id_reestr") , FOREIGN KEY "fk_tbl_rees_reference_tbl_buye" ("id_buyer_of" ) REFERENCES "info"."tbl_buyer" ON DELETE RESTRICT , FOREIGN KEY "fk_tbl_rees_reference_tbl_type" ("id_typepolis" ) REFERENCES "info"."tbl_typepolis" ON DELETE RESTRICT ) ;
CREATE UNIQUE INDEX "ii_reestrid" ON "zagal"."tbl_reestr" ("id_reestr" ) ;
CREATE UNIQUE INDEX "tbl_reestr unique (id_reestr)" ON "zagal"."tbl_reestr" ("id_reestr" ) ;
CREATE TABLE "zagal"."tbl_sclad" ("id_sclad" uniqueidentifier NOT NULL DEFAULT newid(*), "id_typepolis" integer DEFAULT NULL, "id_buyer" uniqueidentifier DEFAULT NULL, "series" char(5) DEFAULT NULL, "numberpolis" integer DEFAULT NULL, "getdate" date DEFAULT NULL, "agdate" date DEFAULT NULL , PRIMARY KEY ("id_sclad") , FOREIGN KEY "fk_tbl_scla_reference_tbl_buye" ("id_buyer" ) REFERENCES "info"."tbl_buyer" ON DELETE RESTRICT ) ;
CREATE UNIQUE INDEX "ii_scladid" ON "zagal"."tbl_sclad" ("id_sclad" ) ;
CREATE UNIQUE INDEX "tbl_sclad unique (id_sclad)" ON "zagal"."tbl_sclad" ("id_sclad" ) ;
2.
На тблице "zagal"."tbl_reestr" есть триггер:
ALTER TRIGGER "tr_ocv_receipt_sclad_firmu" AFTER INSERT
ORDER 1 ON "zagal"."tbl_reestr" REFERENCING NEW AS new_name
FOR EACH ROW /* WHEN( search_condition ) */
BEGIN
declare vi_control integer;
if new_name.status = 1 then
insert into zagal.tbl_sclad(
id_sclad,
id_typepolis,
id_buyer,
series,
numberpolis,
getdate,
agdate
)
values(new_name.id_reestr,
new_name.id_typepolis,
new_name.id_buyer_to,
new_name.series,
new_name.numberpolis,
new_name.getdate,
new_name.agdate
)
elseif new_name.status = 2 /* vudacha polisiv buyer */ then
set vi_control = 0;
select count(s.numberpolis)
into vi_control from zagal.tbl_sclad as s
where s.numberpolis = new_name.numberpolis
and s.id_typepolis = new_name.id_typepolis
;
if vi_control = 0 then
insert into zagal.tbl_sclad(
id_sclad,
id_typepolis,
id_buyer,
series,
numberpolis,
getdate,
agdate
)
values(new_name.id_reestr,
new_name.id_typepolis,
new_name.id_buyer_to,
new_name.series,
new_name.numberpolis,
new_name.getdate,
new_name.agdate
)
else
update zagal.tbl_sclad
set id_buyer = new_name.id_buyer_to, agdate = new_name.agdate
where numberpolis = new_name.numberpolis
and series = new_name.series
and id_typepolis = new_name.id_typepolis
and id_buyer = new_name.id_buyer_of
end if
elseif new_name.status = - 1 then
update zagal.tbl_sclad
set id_buyer = new_name.id_buyer_to, agdate = new_name.agdate,
getdate = new_name.getdate
where numberpolis = new_name.numberpolis
and series = new_name.series
and id_typepolis = new_name.id_typepolis
and id_buyer = new_name.id_buyer_of
end if
END
3. Публикация сверху вниз
CREATE PUBLICATION pu_reestrtobuttom (
TABLE "zagal"."tbl_reestr"
SUBSCRIBE BY id_buyer_to
)
4. Публикация снизу вверх
CREATE PUBLICATION pu_reestrtobuttom (
TABLE "zagal"."tbl_reestr"
)
5. Пользователи подписаны соответствующим образом
|
