Триггер выполняется после инсерта в таблицу строки, в вашем случае после INSERT в "MAIN"."DEVICES" и до INSERT в
"MAIN"."USERS_DEVICES".Вам наверное триггер стоит повесить на "MAIN"."USERS_DEVICES"
On 3/14/18 2:16 PM, Aln Kapa wrote:
> Добрый есть такая структура:
> CREATE TABLE "MAIN"."DEVICES" (
> "ID" BIGSERIAL PRIMARY KEY,
> "EXTRA_DATA" JSONB DEFAULT '{}'
> );
>
> CREATE TABLE "MAIN"."USERS_DEVICES" (
> "ID_DEVICE" BIGINT NOT NULL REFERENCES "MAIN"."DEVICES" ("ID") ON UPDATE CASCADE ON DELETE CASCADE,
> "ID_USER" BIGINT NOT NULL REFERENCES "MAIN"."USERS" ("ID") ON UPDATE CASCADE ON DELETE CASCADE,
> "ACCESS" INTEGER DEFAULT 0,
> CONSTRAINT "UD_PK" PRIMARY KEY ("ID_DEVICE", "ID_USER")
> );
> CREATE OR REPLACE FUNCTION "MAIN"."after_device_update_insert_delete"()
> RETURNS TRIGGER AS
> $$
> BEGIN
> IF TG_OP = 'UPDATE' THEN
> IF OLD."ID" <> NEW."ID" THEN
> RAISE EXCEPTION 'Нельзя менять id';
> END IF;
> PERFORM pg_notify('devices_event_update',json_build_object('id',NEW."ID",
> 'ext',NEW."EXTRA_DATA",
> 'user_access_list',(SELECT json_object_agg("ID_USER", "ACCESS") from "MAIN"."USERS_DEVICES" WHERE "ACCESS" > 0 AND
"ID_DEVICE"=NEW."ID")
> )::text);
> RETURN NEW;
> ELSIF TG_OP = 'INSERT' THEN
> --RAISE EXCEPTION 'ID % SELECT %',NEW."ID", (SELECT "ID_USER" from "MAIN"."USERS_DEVICES" WHERE
"ID_DEVICE"=NEW."ID");
> PERFORM pg_notify('devices_event_update',json_build_object('id',NEW."ID",
> 'ext',NEW."EXTRA_DATA",
> 'user_access_list',(SELECT json_object_agg("ID_USER", "ACCESS") from "MAIN"."USERS_DEVICES" WHERE "ACCESS" > 0 AND
"ID_DEVICE"=NEW."ID")
> )::text);
> RETURN NEW;
> ELSIF TG_OP = 'DELETE' THEN
> PERFORM pg_notify('devices_event_delete',json_build_object('id',OLD."ID",
> 'ext',OLD."EXTRA_DATA",
> 'user_access_list',(SELECT json_object_agg("ID_USER", "ACCESS") from "MAIN"."USERS_DEVICES" WHERE "ACCESS" > 0 AND
"ID_DEVICE"=OLD."ID")
> )::text);
> RETURN OLD;
> END IF;
> END
> $$
> LANGUAGE 'plpgsql' VOLATILE;
> CREATE TRIGGER trig01_after_update_insert_delete AFTER INSERT OR UPDATE OR DELETE ON "MAIN"."DEVICES" FOR EACH ROW
EXECUTEPROCEDURE "MAIN"."after_device_update_insert_delete"();
> Из программы делаю:
> BEGIN
> INSERT в "MAIN"."DEVICES" (узнали "ID")
> INSERT в "MAIN"."USERS_DEVICES"
> COMMIT
> В документации пишут что тригер стартует после COMMIT, но при этом в user_access_list приходит null.
> Каким образом получить в user_access_list нормальные данные, они точно там есть !!!!
>
