PostgreSQL - 确定更新是来自触发器还是用户

Question

在PostgreSQL 13.2中，我有一个包含 5 个字段的表：id、val1、val2、val3、update_origin。

第 1 步：触发器在创建或更新"val1"或"val2"时生成"val3"。此触发器还会使用"触发器更新"值更新字段"update_origin"。

第 2 步：用户也可以直接创建或更新 val3，如果是这样，我会创建一个触发器，该触发器使用"用户更新"值更新字段"update_origin"。

使用此设置，自动触发器更新和手动用户更新之间没有区别，因为没有标识更新源(第二步始终为真，因为PostgreSQL更新是更新)。

如何区分触发器(步骤 1 的示例)或用户(步骤 2 的示例)的更新？

序列

CREATE SEQUENCE demo_id_seq
INCREMENT 1
START 1
MINVALUE 1
MAXVALUE 2147483647
CACHE 1;

桌子

CREATE TABLE demo
(
id integer NOT NULL DEFAULT nextval('demo_id_seq'::regclass),    
val1 character varying COLLATE pg_catalog."default",
val2 character varying COLLATE pg_catalog."default",
val3 character varying COLLATE pg_catalog."default",
update_origin character varying COLLATE pg_catalog."default"
)
TABLESPACE pg_default;

功能#1

CREATE FUNCTION val3_concat()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
AS $BODY$
BEGIN
UPDATE demo
SET val3 = CONCAT(val1, ' ', val2),
update_origin = 'trigger update'
WHERE id = currval('demo_id_seq');
RETURN null;  
END
$BODY$;

触发器 #1

CREATE TRIGGER trigger_val3_concat
AFTER INSERT OR UPDATE OF val1, val2
ON demo
FOR EACH ROW
EXECUTE PROCEDURE val3_concat();

功能#2

CREATE FUNCTION update_origin()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
AS $BODY$
BEGIN
IF OLD.val3 <> NEW.val3 THEN
UPDATE demo
SET update_origin = 'user update'
WHERE id = currval('demo_id_seq');
END IF;
RETURN NEW; 
END
$BODY$;

触发器 #2

CREATE TRIGGER trigger_update_origin
AFTER UPDATE OF val3
ON demo
FOR EACH ROW
EXECUTE PROCEDURE update_origin();

包含INSERT INTO demo (val1, val2) VALUES ('first', 'shot');表的示例将返回1, first, shot, first shot, trigger update。

如果我更新"val1"或 val2"表将返回1, first, shot, first shot, user update或者我希望只有在更新"val3"并且仅更新"val3"时才会这样。

Answer 1

示例：

CREATE TABLE demo
(
id serial NOT NULL PRIMARY KEY,    
val1 varchar,
val2 varchar,
val3 varchar,
update_origin varchar
)
;
CREATE OR REPLACE FUNCTION public.update_origin()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
BEGIN
IF TG_OP = 'UPDATE' THEN
IF OLD.val3 IS DISTINCT FROM NEW.val3 THEN
NEW.update_origin = 'user update';
ELSE
NEW.val3 = NEW.val1 || ' ' || NEW.val2;
NEW.update_origin = 'trigger update';
END IF;
ELSEIF TG_OP = 'INSERT' THEN
IF NEW.val3 IS NOT NULL THEN
NEW.update_origin = 'user update';
ELSE
NEW.val3 = NEW.val1 || ' ' || NEW.val2;
NEW.update_origin = 'trigger update';
END IF;
END IF;
RETURN NEW;
END
$function$
;
CREATE TRIGGER trigger_update_origin
BEFORE INSERT OR UPDATE
ON demo
FOR EACH ROW
EXECUTE PROCEDURE update_origin();
insert into demo (val1, val2) values ('1', '2'); 
INSERT 0 1
test_(aklaver)(5432)=> select * from demo;
id | val1 | val2 | val3 | update_origin  
----+------+------+------+----------------
2 | 1    | 2    | 1 2  | trigger update
insert into demo (val1, val2, val3) values ('3', '4', '5');
INSERT 0 1
test_(aklaver)(5432)=> select * from demo;
id | val1 | val2 | val3 | update_origin  
----+------+------+------+----------------
2 | 1    | 2    | 1 2  | trigger update
3 | 3    | 4    | 5    | user update
update demo set val1 = '6' where id = 2;
UPDATE 1
test_(aklaver)(5432)=> insert into demo (val1, val2, val3) values ('3', '4', '5');
INSERT 0 1
test_(aklaver)(5432)=> select * from demo;
id | val1 | val2 | val3 | update_origin  
----+------+------+------+----------------
3 | 3    | 4    | 5    | user update
2 | 6    | 2    | 6 2  | trigger update
4 | 3    | 4    | 5    | user update

将这两个函数合二为一，并使用TG_OP来区分操作。