-- -- TRIGGERS -- create table pkeys (pkey1 int4 not null, pkey2 text not null); create table fkeys (fkey1 int4, fkey2 text, fkey3 int); create table fkeys2 (fkey21 int4, fkey22 text, pkey23 int not null); create index fkeys_i on fkeys (fkey1, fkey2); create index fkeys2_i on fkeys2 (fkey21, fkey22); create index fkeys2p_i on fkeys2 (pkey23); insert into pkeys values (10, '1'); insert into pkeys values (20, '2'); insert into pkeys values (30, '3'); insert into pkeys values (40, '4'); insert into pkeys values (50, '5'); insert into pkeys values (60, '6'); create unique index pkeys_i on pkeys (pkey1, pkey2); -- -- For fkeys: -- (fkey1, fkey2) --> pkeys (pkey1, pkey2) -- (fkey3) --> fkeys2 (pkey23) -- create trigger check_fkeys_pkey_exist before insert or update on fkeys for each row execute function check_primary_key ('fkey1', 'fkey2', 'pkeys', 'pkey1', 'pkey2'); create trigger check_fkeys_pkey2_exist before insert or update on fkeys for each row execute function check_primary_key ('fkey3', 'fkeys2', 'pkey23'); -- -- For fkeys2: -- (fkey21, fkey22) --> pkeys (pkey1, pkey2) -- create trigger check_fkeys2_pkey_exist before insert or update on fkeys2 for each row execute procedure check_primary_key ('fkey21', 'fkey22', 'pkeys', 'pkey1', 'pkey2'); -- Test comments COMMENT ON TRIGGER check_fkeys2_pkey_bad ON fkeys2 IS 'wrong'; ERROR: trigger "check_fkeys2_pkey_bad" for table "fkeys2" does not exist COMMENT ON TRIGGER check_fkeys2_pkey_exist ON fkeys2 IS 'right'; COMMENT ON TRIGGER check_fkeys2_pkey_exist ON fkeys2 IS NULL; -- -- For pkeys: -- ON DELETE/UPDATE (pkey1, pkey2) CASCADE: -- fkeys (fkey1, fkey2) and fkeys2 (fkey21, fkey22) -- create trigger check_pkeys_fkey_cascade before delete or update on pkeys for each row execute procedure check_foreign_key (2, 'cascade', 'pkey1', 'pkey2', 'fkeys', 'fkey1', 'fkey2', 'fkeys2', 'fkey21', 'fkey22'); -- -- For fkeys2: -- ON DELETE/UPDATE (pkey23) RESTRICT: -- fkeys (fkey3) -- create trigger check_fkeys2_fkey_restrict before delete or update on fkeys2 for each row execute procedure check_foreign_key (1, 'restrict', 'pkey23', 'fkeys', 'fkey3'); insert into fkeys2 values (10, '1', 1); insert into fkeys2 values (30, '3', 2); insert into fkeys2 values (40, '4', 5); insert into fkeys2 values (50, '5', 3); -- no key in pkeys insert into fkeys2 values (70, '5', 3); ERROR: tuple references non-existent key DETAIL: Trigger "check_fkeys2_pkey_exist" found tuple referencing non-existent key in "pkeys". insert into fkeys values (10, '1', 2); insert into fkeys values (30, '3', 3); insert into fkeys values (40, '4', 2); insert into fkeys values (50, '5', 2); -- no key in pkeys insert into fkeys values (70, '5', 1); ERROR: tuple references non-existent key DETAIL: Trigger "check_fkeys_pkey_exist" found tuple referencing non-existent key in "pkeys". -- no key in fkeys2 insert into fkeys values (60, '6', 4); ERROR: tuple references non-existent key DETAIL: Trigger "check_fkeys_pkey2_exist" found tuple referencing non-existent key in "fkeys2". delete from pkeys where pkey1 = 30 and pkey2 = '3'; NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted ERROR: "check_fkeys2_fkey_restrict": tuple is referenced in "fkeys" CONTEXT: SQL statement "delete from fkeys2 where fkey21 = $1 and fkey22 = $2 " delete from pkeys where pkey1 = 40 and pkey2 = '4'; NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys2 are deleted update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 50 and pkey2 = '5'; NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted ERROR: "check_fkeys2_fkey_restrict": tuple is referenced in "fkeys" CONTEXT: SQL statement "delete from fkeys2 where fkey21 = $1 and fkey22 = $2 " update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 10 and pkey2 = '1'; NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys2 are deleted SELECT trigger_name, event_manipulation, event_object_schema, event_object_table, action_order, action_condition, action_orientation, action_timing, action_reference_old_table, action_reference_new_table FROM information_schema.triggers WHERE event_object_table in ('pkeys', 'fkeys', 'fkeys2') ORDER BY trigger_name COLLATE "C", 2; trigger_name | event_manipulation | event_object_schema | event_object_table | action_order | action_condition | action_orientation | action_timing | action_reference_old_table | action_reference_new_table ----------------------------+--------------------+---------------------+--------------------+--------------+------------------+--------------------+---------------+----------------------------+---------------------------- check_fkeys2_fkey_restrict | DELETE | public | fkeys2 | 1 | | ROW | BEFORE | | check_fkeys2_fkey_restrict | UPDATE | public | fkeys2 | 1 | | ROW | BEFORE | | check_fkeys2_pkey_exist | INSERT | public | fkeys2 | 1 | | ROW | BEFORE | | check_fkeys2_pkey_exist | UPDATE | public | fkeys2 | 2 | | ROW | BEFORE | | check_fkeys_pkey2_exist | INSERT | public | fkeys | 1 | | ROW | BEFORE | | check_fkeys_pkey2_exist | UPDATE | public | fkeys | 1 | | ROW | BEFORE | | check_fkeys_pkey_exist | INSERT | public | fkeys | 2 | | ROW | BEFORE | | check_fkeys_pkey_exist | UPDATE | public | fkeys | 2 | | ROW | BEFORE | | check_pkeys_fkey_cascade | DELETE | public | pkeys | 1 | | ROW | BEFORE | | check_pkeys_fkey_cascade | UPDATE | public | pkeys | 1 | | ROW | BEFORE | | (10 rows) DROP TABLE pkeys; DROP TABLE fkeys; DROP TABLE fkeys2; -- Check behavior when trigger returns unmodified trigtuple create table trigtest (f1 int, f2 text); create trigger trigger_return_old before insert or delete or update on trigtest for each row execute procedure trigger_return_old(); insert into trigtest values(1, 'foo'); select * from trigtest; f1 | f2 ----+----- 1 | foo (1 row) update trigtest set f2 = f2 || 'bar'; select * from trigtest; f1 | f2 ----+----- 1 | foo (1 row) delete from trigtest; select * from trigtest; f1 | f2 ----+---- (0 rows) -- Also check what happens when such a trigger runs before or after others create function f1_times_10() returns trigger as $$ begin new.f1 := new.f1 * 10; return new; end $$ language plpgsql; create trigger trigger_alpha before insert or update on trigtest for each row execute procedure f1_times_10(); insert into trigtest values(1, 'foo'); select * from trigtest; f1 | f2 ----+----- 10 | foo (1 row) update trigtest set f2 = f2 || 'bar'; select * from trigtest; f1 | f2 ----+----- 10 | foo (1 row) delete from trigtest; select * from trigtest; f1 | f2 ----+---- (0 rows) create trigger trigger_zed before insert or update on trigtest for each row execute procedure f1_times_10(); insert into trigtest values(1, 'foo'); select * from trigtest; f1 | f2 -----+----- 100 | foo (1 row) update trigtest set f2 = f2 || 'bar'; select * from trigtest; f1 | f2 ------+----- 1000 | foo (1 row) delete from trigtest; select * from trigtest; f1 | f2 ----+---- (0 rows) drop trigger trigger_alpha on trigtest; insert into trigtest values(1, 'foo'); select * from trigtest; f1 | f2 ----+----- 10 | foo (1 row) update trigtest set f2 = f2 || 'bar'; select * from trigtest; f1 | f2 -----+----- 100 | foo (1 row) delete from trigtest; select * from trigtest; f1 | f2 ----+---- (0 rows) drop table trigtest; -- Check behavior with an implicit column default, too (bug #16644) create table trigtest ( a integer, b bool default true not null, c text default 'xyzzy' not null); create trigger trigger_return_old before insert or delete or update on trigtest for each row execute procedure trigger_return_old(); insert into trigtest values(1); select * from trigtest; a | b | c ---+---+------- 1 | t | xyzzy (1 row) alter table trigtest add column d integer default 42 not null; select * from trigtest; a | b | c | d ---+---+-------+---- 1 | t | xyzzy | 42 (1 row) update trigtest set a = 2 where a = 1 returning *; a | b | c | d ---+---+-------+---- 1 | t | xyzzy | 42 (1 row) select * from trigtest; a | b | c | d ---+---+-------+---- 1 | t | xyzzy | 42 (1 row) alter table trigtest drop column b; select * from trigtest; a | c | d ---+-------+---- 1 | xyzzy | 42 (1 row) update trigtest set a = 2 where a = 1 returning *; a | c | d ---+-------+---- 1 | xyzzy | 42 (1 row) select * from trigtest; a | c | d ---+-------+---- 1 | xyzzy | 42 (1 row) drop table trigtest; create sequence ttdummy_seq increment 10 start 0 minvalue 0; create table tttest ( price_id int4, price_val int4, price_on int4, price_off int4 default 999999 ); create trigger ttdummy before delete or update on tttest for each row execute procedure ttdummy (price_on, price_off); create trigger ttserial before insert or update on tttest for each row execute procedure autoinc (price_on, ttdummy_seq); insert into tttest values (1, 1, null); insert into tttest values (2, 2, null); insert into tttest values (3, 3, 0); select * from tttest; price_id | price_val | price_on | price_off ----------+-----------+----------+----------- 1 | 1 | 10 | 999999 2 | 2 | 20 | 999999 3 | 3 | 30 | 999999 (3 rows) delete from tttest where price_id = 2; select * from tttest; price_id | price_val | price_on | price_off ----------+-----------+----------+----------- 1 | 1 | 10 | 999999 3 | 3 | 30 | 999999 2 | 2 | 20 | 40 (3 rows) -- what do we see ? -- get current prices select * from tttest where price_off = 999999; price_id | price_val | price_on | price_off ----------+-----------+----------+----------- 1 | 1 | 10 | 999999 3 | 3 | 30 | 999999 (2 rows) -- change price for price_id == 3 update tttest set price_val = 30 where price_id = 3; select * from tttest; price_id | price_val | price_on | price_off ----------+-----------+----------+----------- 1 | 1 | 10 | 999999 2 | 2 | 20 | 40 3 | 30 | 50 | 999999 3 | 3 | 30 | 50 (4 rows) -- now we want to change pric_id in ALL tuples -- this gets us not what we need update tttest set price_id = 5 where price_id = 3; select * from tttest; price_id | price_val | price_on | price_off ----------+-----------+----------+----------- 1 | 1 | 10 | 999999 2 | 2 | 20 | 40 3 | 3 | 30 | 50 5 | 30 | 60 | 999999 3 | 30 | 50 | 60 (5 rows) -- restore data as before last update: select set_ttdummy(0); set_ttdummy ------------- 1 (1 row) delete from tttest where price_id = 5; update tttest set price_off = 999999 where price_val = 30; select * from tttest; price_id | price_val | price_on | price_off ----------+-----------+----------+----------- 1 | 1 | 10 | 999999 2 | 2 | 20 | 40 3 | 3 | 30 | 50 3 | 30 | 50 | 999999 (4 rows) -- and try change price_id now! update tttest set price_id = 5 where price_id = 3; select * from tttest; price_id | price_val | price_on | price_off ----------+-----------+----------+----------- 1 | 1 | 10 | 999999 2 | 2 | 20 | 40 5 | 3 | 30 | 50 5 | 30 | 50 | 999999 (4 rows) -- isn't it what we need ? select set_ttdummy(1); set_ttdummy ------------- 0 (1 row) -- we want to correct some "date" update tttest set price_on = -1 where price_id = 1; ERROR: ttdummy (tttest): you cannot change price_on and/or price_off columns (use set_ttdummy) -- but this doesn't work -- try in this way select set_ttdummy(0); set_ttdummy ------------- 1 (1 row) update tttest set price_on = -1 where price_id = 1; select * from tttest; price_id | price_val | price_on | price_off ----------+-----------+----------+----------- 2 | 2 | 20 | 40 5 | 3 | 30 | 50 5 | 30 | 50 | 999999 1 | 1 | -1 | 999999 (4 rows) -- isn't it what we need ? -- get price for price_id == 5 as it was @ "date" 35 select * from tttest where price_on <= 35 and price_off > 35 and price_id = 5; price_id | price_val | price_on | price_off ----------+-----------+----------+----------- 5 | 3 | 30 | 50 (1 row) drop table tttest; drop sequence ttdummy_seq; -- -- tests for per-statement triggers -- CREATE TABLE log_table (tstamp timestamp default timeofday()::timestamp); CREATE TABLE main_table (a int unique, b int); COPY main_table (a,b) FROM stdin; CREATE FUNCTION trigger_func() RETURNS trigger LANGUAGE plpgsql AS ' BEGIN RAISE NOTICE ''trigger_func(%) called: action = %, when = %, level = %'', TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL; RETURN NULL; END;'; CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON main_table FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('before_ins_stmt'); CREATE TRIGGER after_ins_stmt_trig AFTER INSERT ON main_table FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('after_ins_stmt'); -- -- if neither 'FOR EACH ROW' nor 'FOR EACH STATEMENT' was specified, -- CREATE TRIGGER should default to 'FOR EACH STATEMENT' -- CREATE TRIGGER after_upd_stmt_trig AFTER UPDATE ON main_table EXECUTE PROCEDURE trigger_func('after_upd_stmt'); -- Both insert and update statement level triggers (before and after) should -- fire. Doesn't fire UPDATE before trigger, but only because one isn't -- defined. INSERT INTO main_table (a, b) VALUES (5, 10) ON CONFLICT (a) DO UPDATE SET b = EXCLUDED.b; NOTICE: trigger_func(before_ins_stmt) called: action = INSERT, when = BEFORE, level = STATEMENT NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT NOTICE: trigger_func(after_ins_stmt) called: action = INSERT, when = AFTER, level = STATEMENT CREATE TRIGGER after_upd_row_trig AFTER UPDATE ON main_table FOR EACH ROW EXECUTE PROCEDURE trigger_func('after_upd_row'); INSERT INTO main_table DEFAULT VALUES; NOTICE: trigger_func(before_ins_stmt) called: action = INSERT, when = BEFORE, level = STATEMENT NOTICE: trigger_func(after_ins_stmt) called: action = INSERT, when = AFTER, level = STATEMENT UPDATE main_table SET a = a + 1 WHERE b < 30; NOTICE: trigger_func(after_upd_row) called: action = UPDATE, when = AFTER, level = ROW NOTICE: trigger_func(after_upd_row) called: action = UPDATE, when = AFTER, level = ROW NOTICE: trigger_func(after_upd_row) called: action = UPDATE, when = AFTER, level = ROW NOTICE: trigger_func(after_upd_row) called: action = UPDATE, when = AFTER, level = ROW NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT -- UPDATE that effects zero rows should still call per-statement trigger UPDATE main_table SET a = a + 2 WHERE b > 100; NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT -- constraint now unneeded ALTER TABLE main_table DROP CONSTRAINT main_table_a_key; -- COPY should fire per-row and per-statement INSERT triggers COPY main_table (a, b) FROM stdin; NOTICE: trigger_func(before_ins_stmt) called: action = INSERT, when = BEFORE, level = STATEMENT NOTICE: trigger_func(after_ins_stmt) called: action = INSERT, when = AFTER, level = STATEMENT SELECT * FROM main_table ORDER BY a, b; a | b ----+---- 6 | 10 21 | 20 30 | 40 31 | 10 50 | 35 50 | 60 81 | 15 | (8 rows) -- -- test triggers with WHEN clause -- CREATE TRIGGER modified_a BEFORE UPDATE OF a ON main_table FOR EACH ROW WHEN (OLD.a <> NEW.a) EXECUTE PROCEDURE trigger_func('modified_a'); CREATE TRIGGER modified_any BEFORE UPDATE OF a ON main_table FOR EACH ROW WHEN (OLD.* IS DISTINCT FROM NEW.*) EXECUTE PROCEDURE trigger_func('modified_any'); CREATE TRIGGER insert_a AFTER INSERT ON main_table FOR EACH ROW WHEN (NEW.a = 123) EXECUTE PROCEDURE trigger_func('insert_a'); CREATE TRIGGER delete_a AFTER DELETE ON main_table FOR EACH ROW WHEN (OLD.a = 123) EXECUTE PROCEDURE trigger_func('delete_a'); CREATE TRIGGER insert_when BEFORE INSERT ON main_table FOR EACH STATEMENT WHEN (true) EXECUTE PROCEDURE trigger_func('insert_when'); CREATE TRIGGER delete_when AFTER DELETE ON main_table FOR EACH STATEMENT WHEN (true) EXECUTE PROCEDURE trigger_func('delete_when'); SELECT trigger_name, event_manipulation, event_object_schema, event_object_table, action_order, action_condition, action_orientation, action_timing, action_reference_old_table, action_reference_new_table FROM information_schema.triggers WHERE event_object_table IN ('main_table') ORDER BY trigger_name COLLATE "C", 2; trigger_name | event_manipulation | event_object_schema | event_object_table | action_order | action_condition | action_orientation | action_timing | action_reference_old_table | action_reference_new_table ----------------------+--------------------+---------------------+--------------------+--------------+--------------------------------+--------------------+---------------+----------------------------+---------------------------- after_ins_stmt_trig | INSERT | public | main_table | 1 | | STATEMENT | AFTER | | after_upd_row_trig | UPDATE | public | main_table | 1 | | ROW | AFTER | | after_upd_stmt_trig | UPDATE | public | main_table | 1 | | STATEMENT | AFTER | | before_ins_stmt_trig | INSERT | public | main_table | 1 | | STATEMENT | BEFORE | | delete_a | DELETE | public | main_table | 1 | (old.a = 123) | ROW | AFTER | | delete_when | DELETE | public | main_table | 1 | true | STATEMENT | AFTER | | insert_a | INSERT | public | main_table | 1 | (new.a = 123) | ROW | AFTER | | insert_when | INSERT | public | main_table | 2 | true | STATEMENT | BEFORE | | modified_a | UPDATE | public | main_table | 1 | (old.a <> new.a) | ROW | BEFORE | | modified_any | UPDATE | public | main_table | 2 | (old.* IS DISTINCT FROM new.*) | ROW | BEFORE | | (10 rows) INSERT INTO main_table (a) VALUES (123), (456); NOTICE: trigger_func(before_ins_stmt) called: action = INSERT, when = BEFORE, level = STATEMENT NOTICE: trigger_func(insert_when) called: action = INSERT, when = BEFORE, level = STATEMENT NOTICE: trigger_func(insert_a) called: action = INSERT, when = AFTER, level = ROW NOTICE: trigger_func(after_ins_stmt) called: action = INSERT, when = AFTER, level = STATEMENT COPY main_table FROM stdin; NOTICE: trigger_func(before_ins_stmt) called: action = INSERT, when = BEFORE, level = STATEMENT NOTICE: trigger_func(insert_when) called: action = INSERT, when = BEFORE, level = STATEMENT NOTICE: trigger_func(insert_a) called: action = INSERT, when = AFTER, level = ROW NOTICE: trigger_func(after_ins_stmt) called: action = INSERT, when = AFTER, level = STATEMENT DELETE FROM main_table WHERE a IN (123, 456); NOTICE: trigger_func(delete_a) called: action = DELETE, when = AFTER, level = ROW NOTICE: trigger_func(delete_a) called: action = DELETE, when = AFTER, level = ROW NOTICE: trigger_func(delete_when) called: action = DELETE, when = AFTER, level = STATEMENT UPDATE main_table SET a = 50, b = 60; NOTICE: trigger_func(modified_any) called: action = UPDATE, when = BEFORE, level = ROW NOTICE: trigger_func(modified_any) called: action = UPDATE, when = BEFORE, level = ROW NOTICE: trigger_func(modified_a) called: action = UPDATE, when = BEFORE, level = ROW NOTICE: trigger_func(modified_a) called: action = UPDATE, when = BEFORE, level = ROW NOTICE: trigger_func(modified_a) called: action = UPDATE, when = BEFORE, level = ROW NOTICE: trigger_func(modified_a) called: action = UPDATE, when = BEFORE, level = ROW NOTICE: trigger_func(modified_a) called: action = UPDATE, when = BEFORE, level = ROW NOTICE: trigger_func(after_upd_row) called: action = UPDATE, when = AFTER, level = ROW NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT SELECT * FROM main_table ORDER BY a, b; a | b ----+---- 6 | 10 21 | 20 30 | 40 31 | 10 50 | 35 50 | 60 81 | 15 | (8 rows) SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_a'; pg_get_triggerdef ------------------------------------------------------------------------------------------------------------------------------------------- CREATE TRIGGER modified_a BEFORE UPDATE OF a ON main_table FOR EACH ROW WHEN (old.a <> new.a) EXECUTE FUNCTION trigger_func('modified_a') (1 row) SELECT pg_get_triggerdef(oid, false) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_a'; pg_get_triggerdef ---------------------------------------------------------------------------------------------------------------------------------------------------- CREATE TRIGGER modified_a BEFORE UPDATE OF a ON public.main_table FOR EACH ROW WHEN ((old.a <> new.a)) EXECUTE FUNCTION trigger_func('modified_a') (1 row) SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_any'; pg_get_triggerdef ------------------------------------------------------------------------------------------------------------------------------------------------------------- CREATE TRIGGER modified_any BEFORE UPDATE OF a ON main_table FOR EACH ROW WHEN (old.* IS DISTINCT FROM new.*) EXECUTE FUNCTION trigger_func('modified_any') (1 row) -- Test RENAME TRIGGER ALTER TRIGGER modified_a ON main_table RENAME TO modified_modified_a; SELECT count(*) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_a'; count ------- 0 (1 row) SELECT count(*) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_modified_a'; count ------- 1 (1 row) DROP TRIGGER modified_modified_a ON main_table; DROP TRIGGER modified_any ON main_table; DROP TRIGGER insert_a ON main_table; DROP TRIGGER delete_a ON main_table; DROP TRIGGER insert_when ON main_table; DROP TRIGGER delete_when ON main_table; -- Test WHEN condition accessing system columns. create table table_with_oids(a int); insert into table_with_oids values (1); create trigger oid_unchanged_trig after update on table_with_oids for each row when (new.tableoid = old.tableoid AND new.tableoid <> 0) execute procedure trigger_func('after_upd_oid_unchanged'); update table_with_oids set a = a + 1; NOTICE: trigger_func(after_upd_oid_unchanged) called: action = UPDATE, when = AFTER, level = ROW drop table table_with_oids; -- Test column-level triggers DROP TRIGGER after_upd_row_trig ON main_table; CREATE TRIGGER before_upd_a_row_trig BEFORE UPDATE OF a ON main_table FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_upd_a_row'); CREATE TRIGGER after_upd_b_row_trig AFTER UPDATE OF b ON main_table FOR EACH ROW EXECUTE PROCEDURE trigger_func('after_upd_b_row'); CREATE TRIGGER after_upd_a_b_row_trig AFTER UPDATE OF a, b ON main_table FOR EACH ROW EXECUTE PROCEDURE trigger_func('after_upd_a_b_row'); CREATE TRIGGER before_upd_a_stmt_trig BEFORE UPDATE OF a ON main_table FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('before_upd_a_stmt'); CREATE TRIGGER after_upd_b_stmt_trig AFTER UPDATE OF b ON main_table FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('after_upd_b_stmt'); SELECT pg_get_triggerdef(oid) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'after_upd_a_b_row_trig'; pg_get_triggerdef ------------------------------------------------------------------------------------------------------------------------------------------------- CREATE TRIGGER after_upd_a_b_row_trig AFTER UPDATE OF a, b ON public.main_table FOR EACH ROW EXECUTE FUNCTION trigger_func('after_upd_a_b_row') (1 row) UPDATE main_table SET a = 50; NOTICE: trigger_func(before_upd_a_stmt) called: action = UPDATE, when = BEFORE, level = STATEMENT NOTICE: trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW NOTICE: trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW NOTICE: trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW NOTICE: trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW NOTICE: trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW NOTICE: trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW NOTICE: trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW NOTICE: trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT UPDATE main_table SET b = 10; NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW NOTICE: trigger_func(after_upd_b_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT -- -- Test case for bug with BEFORE trigger followed by AFTER trigger with WHEN -- CREATE TABLE some_t (some_col boolean NOT NULL); CREATE FUNCTION dummy_update_func() RETURNS trigger AS $$ BEGIN RAISE NOTICE 'dummy_update_func(%) called: action = %, old = %, new = %', TG_ARGV[0], TG_OP, OLD, NEW; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER some_trig_before BEFORE UPDATE ON some_t FOR EACH ROW EXECUTE PROCEDURE dummy_update_func('before'); CREATE TRIGGER some_trig_aftera AFTER UPDATE ON some_t FOR EACH ROW WHEN (NOT OLD.some_col AND NEW.some_col) EXECUTE PROCEDURE dummy_update_func('aftera'); CREATE TRIGGER some_trig_afterb AFTER UPDATE ON some_t FOR EACH ROW WHEN (NOT NEW.some_col) EXECUTE PROCEDURE dummy_update_func('afterb'); INSERT INTO some_t VALUES (TRUE); UPDATE some_t SET some_col = TRUE; NOTICE: dummy_update_func(before) called: action = UPDATE, old = (t), new = (t) UPDATE some_t SET some_col = FALSE; NOTICE: dummy_update_func(before) called: action = UPDATE, old = (t), new = (f) NOTICE: dummy_update_func(afterb) called: action = UPDATE, old = (t), new = (f) UPDATE some_t SET some_col = TRUE; NOTICE: dummy_update_func(before) called: action = UPDATE, old = (f), new = (t) NOTICE: dummy_update_func(aftera) called: action = UPDATE, old = (f), new = (t) DROP TABLE some_t; -- bogus cases CREATE TRIGGER error_upd_and_col BEFORE UPDATE OR UPDATE OF a ON main_table FOR EACH ROW EXECUTE PROCEDURE trigger_func('error_upd_and_col'); ERROR: duplicate trigger events specified at or near "ON" LINE 1: ...ER error_upd_and_col BEFORE UPDATE OR UPDATE OF a ON main_ta... ^ CREATE TRIGGER error_upd_a_a BEFORE UPDATE OF a, a ON main_table FOR EACH ROW EXECUTE PROCEDURE trigger_func('error_upd_a_a'); ERROR: column "a" specified more than once CREATE TRIGGER error_ins_a BEFORE INSERT OF a ON main_table FOR EACH ROW EXECUTE PROCEDURE trigger_func('error_ins_a'); ERROR: syntax error at or near "OF" LINE 1: CREATE TRIGGER error_ins_a BEFORE INSERT OF a ON main_table ^ CREATE TRIGGER error_ins_when BEFORE INSERT OR UPDATE ON main_table FOR EACH ROW WHEN (OLD.a <> NEW.a) EXECUTE PROCEDURE trigger_func('error_ins_old'); ERROR: INSERT trigger's WHEN condition cannot reference OLD values LINE 2: FOR EACH ROW WHEN (OLD.a <> NEW.a) ^ CREATE TRIGGER error_del_when BEFORE DELETE OR UPDATE ON main_table FOR EACH ROW WHEN (OLD.a <> NEW.a) EXECUTE PROCEDURE trigger_func('error_del_new'); ERROR: DELETE trigger's WHEN condition cannot reference NEW values LINE 2: FOR EACH ROW WHEN (OLD.a <> NEW.a) ^ CREATE TRIGGER error_del_when BEFORE INSERT OR UPDATE ON main_table FOR EACH ROW WHEN (NEW.tableoid <> 0) EXECUTE PROCEDURE trigger_func('error_when_sys_column'); ERROR: BEFORE trigger's WHEN condition cannot reference NEW system columns LINE 2: FOR EACH ROW WHEN (NEW.tableoid <> 0) ^ CREATE TRIGGER error_stmt_when BEFORE UPDATE OF a ON main_table FOR EACH STATEMENT WHEN (OLD.* IS DISTINCT FROM NEW.*) EXECUTE PROCEDURE trigger_func('error_stmt_when'); ERROR: statement trigger's WHEN condition cannot reference column values LINE 2: FOR EACH STATEMENT WHEN (OLD.* IS DISTINCT FROM NEW.*) ^ -- check dependency restrictions ALTER TABLE main_table DROP COLUMN b; ERROR: cannot drop column b of table main_table because other objects depend on it DETAIL: trigger after_upd_b_row_trig on table main_table depends on column b of table main_table trigger after_upd_a_b_row_trig on table main_table depends on column b of table main_table trigger after_upd_b_stmt_trig on table main_table depends on column b of table main_table HINT: Use DROP ... CASCADE to drop the dependent objects too. -- this should succeed, but we'll roll it back to keep the triggers around begin; DROP TRIGGER after_upd_a_b_row_trig ON main_table; DROP TRIGGER after_upd_b_row_trig ON main_table; DROP TRIGGER after_upd_b_stmt_trig ON main_table; ALTER TABLE main_table DROP COLUMN b; rollback; -- Test enable/disable triggers create table trigtest (i serial primary key); -- test that disabling RI triggers works create table trigtest2 (i int references trigtest(i) on delete cascade); create function trigtest() returns trigger as $$ begin raise notice '% % % %', TG_RELNAME, TG_OP, TG_WHEN, TG_LEVEL; return new; end;$$ language plpgsql; create trigger trigtest_b_row_tg before insert or update or delete on trigtest for each row execute procedure trigtest(); create trigger trigtest_a_row_tg after insert or update or delete on trigtest for each row execute procedure trigtest(); create trigger trigtest_b_stmt_tg before insert or update or delete on trigtest for each statement execute procedure trigtest(); create trigger trigtest_a_stmt_tg after insert or update or delete on trigtest for each statement execute procedure trigtest(); insert into trigtest default values; NOTICE: trigtest INSERT BEFORE STATEMENT NOTICE: trigtest INSERT BEFORE ROW NOTICE: trigtest INSERT AFTER ROW NOTICE: trigtest INSERT AFTER STATEMENT alter table trigtest disable trigger trigtest_b_row_tg; insert into trigtest default values; NOTICE: trigtest INSERT BEFORE STATEMENT NOTICE: trigtest INSERT AFTER ROW NOTICE: trigtest INSERT AFTER STATEMENT alter table trigtest disable trigger user; insert into trigtest default values; alter table trigtest enable trigger trigtest_a_stmt_tg; insert into trigtest default values; NOTICE: trigtest INSERT AFTER STATEMENT set session_replication_role = replica; insert into trigtest default values; -- does not trigger alter table trigtest enable always trigger trigtest_a_stmt_tg; insert into trigtest default values; -- now it does NOTICE: trigtest INSERT AFTER STATEMENT reset session_replication_role; insert into trigtest2 values(1); insert into trigtest2 values(2); delete from trigtest where i=2; NOTICE: trigtest DELETE AFTER STATEMENT select * from trigtest2; i --- 1 (1 row) alter table trigtest disable trigger all; delete from trigtest where i=1; select * from trigtest2; i --- 1 (1 row) -- ensure we still insert, even when all triggers are disabled insert into trigtest default values; select * from trigtest; i --- 3 4 5 6 7 (5 rows) drop table trigtest2; drop table trigtest; -- dump trigger data CREATE TABLE trigger_test ( i int, v varchar ); CREATE OR REPLACE FUNCTION trigger_data() RETURNS trigger LANGUAGE plpgsql AS $$ declare argstr text; relid text; begin relid := TG_relid::regclass; -- plpgsql can't discover its trigger data in a hash like perl and python -- can, or by a sort of reflection like tcl can, -- so we have to hard code the names. raise NOTICE 'TG_NAME: %', TG_name; raise NOTICE 'TG_WHEN: %', TG_when; raise NOTICE 'TG_LEVEL: %', TG_level; raise NOTICE 'TG_OP: %', TG_op; raise NOTICE 'TG_RELID::regclass: %', relid; raise NOTICE 'TG_RELNAME: %', TG_relname; raise NOTICE 'TG_TABLE_NAME: %', TG_table_name; raise NOTICE 'TG_TABLE_SCHEMA: %', TG_table_schema; raise NOTICE 'TG_NARGS: %', TG_nargs; argstr := '['; for i in 0 .. TG_nargs - 1 loop if i > 0 then argstr := argstr || ', '; end if; argstr := argstr || TG_argv[i]; end loop; argstr := argstr || ']'; raise NOTICE 'TG_ARGV: %', argstr; if TG_OP != 'INSERT' then raise NOTICE 'OLD: %', OLD; end if; if TG_OP != 'DELETE' then raise NOTICE 'NEW: %', NEW; end if; if TG_OP = 'DELETE' then return OLD; else return NEW; end if; end; $$; CREATE TRIGGER show_trigger_data_trig BEFORE INSERT OR UPDATE OR DELETE ON trigger_test FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); insert into trigger_test values(1,'insert'); NOTICE: TG_NAME: show_trigger_data_trig NOTICE: TG_WHEN: BEFORE NOTICE: TG_LEVEL: ROW NOTICE: TG_OP: INSERT NOTICE: TG_RELID::regclass: trigger_test NOTICE: TG_RELNAME: trigger_test NOTICE: TG_TABLE_NAME: trigger_test NOTICE: TG_TABLE_SCHEMA: public NOTICE: TG_NARGS: 2 NOTICE: TG_ARGV: [23, skidoo] NOTICE: NEW: (1,insert) update trigger_test set v = 'update' where i = 1; NOTICE: TG_NAME: show_trigger_data_trig NOTICE: TG_WHEN: BEFORE NOTICE: TG_LEVEL: ROW NOTICE: TG_OP: UPDATE NOTICE: TG_RELID::regclass: trigger_test NOTICE: TG_RELNAME: trigger_test NOTICE: TG_TABLE_NAME: trigger_test NOTICE: TG_TABLE_SCHEMA: public NOTICE: TG_NARGS: 2 NOTICE: TG_ARGV: [23, skidoo] NOTICE: OLD: (1,insert) NOTICE: NEW: (1,update) delete from trigger_test; NOTICE: TG_NAME: show_trigger_data_trig NOTICE: TG_WHEN: BEFORE NOTICE: TG_LEVEL: ROW NOTICE: TG_OP: DELETE NOTICE: TG_RELID::regclass: trigger_test NOTICE: TG_RELNAME: trigger_test NOTICE: TG_TABLE_NAME: trigger_test NOTICE: TG_TABLE_SCHEMA: public NOTICE: TG_NARGS: 2 NOTICE: TG_ARGV: [23, skidoo] NOTICE: OLD: (1,update) DROP TRIGGER show_trigger_data_trig on trigger_test; DROP FUNCTION trigger_data(); DROP TABLE trigger_test; -- -- Test use of row comparisons on OLD/NEW -- CREATE TABLE trigger_test (f1 int, f2 text, f3 text); -- this is the obvious (and wrong...) way to compare rows CREATE FUNCTION mytrigger() RETURNS trigger LANGUAGE plpgsql as $$ begin if row(old.*) = row(new.*) then raise notice 'row % not changed', new.f1; else raise notice 'row % changed', new.f1; end if; return new; end$$; CREATE TRIGGER t BEFORE UPDATE ON trigger_test FOR EACH ROW EXECUTE PROCEDURE mytrigger(); INSERT INTO trigger_test VALUES(1, 'foo', 'bar'); INSERT INTO trigger_test VALUES(2, 'baz', 'quux'); UPDATE trigger_test SET f3 = 'bar'; NOTICE: row 1 not changed NOTICE: row 2 changed UPDATE trigger_test SET f3 = NULL; NOTICE: row 1 changed NOTICE: row 2 changed -- this demonstrates that the above isn't really working as desired: UPDATE trigger_test SET f3 = NULL; NOTICE: row 1 changed NOTICE: row 2 changed -- the right way when considering nulls is CREATE OR REPLACE FUNCTION mytrigger() RETURNS trigger LANGUAGE plpgsql as $$ begin if row(old.*) is distinct from row(new.*) then raise notice 'row % changed', new.f1; else raise notice 'row % not changed', new.f1; end if; return new; end$$; UPDATE trigger_test SET f3 = 'bar'; NOTICE: row 1 changed NOTICE: row 2 changed UPDATE trigger_test SET f3 = NULL; NOTICE: row 1 changed NOTICE: row 2 changed UPDATE trigger_test SET f3 = NULL; NOTICE: row 1 not changed NOTICE: row 2 not changed DROP TABLE trigger_test; DROP FUNCTION mytrigger(); -- Test snapshot management in serializable transactions involving triggers -- per bug report in 6bc73d4c0910042358k3d1adff3qa36f8df75198ecea@mail.gmail.com CREATE FUNCTION serializable_update_trig() RETURNS trigger LANGUAGE plpgsql AS $$ declare rec record; begin new.description = 'updated in trigger'; return new; end; $$; CREATE TABLE serializable_update_tab ( id int, filler text, description text ); CREATE TRIGGER serializable_update_trig BEFORE UPDATE ON serializable_update_tab FOR EACH ROW EXECUTE PROCEDURE serializable_update_trig(); INSERT INTO serializable_update_tab SELECT a, repeat('xyzxz', 100), 'new' FROM generate_series(1, 50) a; BEGIN; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; UPDATE serializable_update_tab SET description = 'no no', id = 1 WHERE id = 1; COMMIT; SELECT description FROM serializable_update_tab WHERE id = 1; description -------------------- updated in trigger (1 row) DROP TABLE serializable_update_tab; -- minimal update trigger CREATE TABLE min_updates_test ( f1 text, f2 int, f3 int); INSERT INTO min_updates_test VALUES ('a',1,2),('b','2',null); CREATE TRIGGER z_min_update BEFORE UPDATE ON min_updates_test FOR EACH ROW EXECUTE PROCEDURE suppress_redundant_updates_trigger(); \set QUIET false UPDATE min_updates_test SET f1 = f1; UPDATE 0 UPDATE min_updates_test SET f2 = f2 + 1; UPDATE 2 UPDATE min_updates_test SET f3 = 2 WHERE f3 is null; UPDATE 1 \set QUIET true SELECT * FROM min_updates_test; f1 | f2 | f3 ----+----+---- a | 2 | 2 b | 3 | 2 (2 rows) DROP TABLE min_updates_test; -- -- Test triggers on views -- CREATE VIEW main_view AS SELECT a, b FROM main_table; -- VIEW trigger function CREATE OR REPLACE FUNCTION view_trigger() RETURNS trigger LANGUAGE plpgsql AS $$ declare argstr text := ''; begin for i in 0 .. TG_nargs - 1 loop if i > 0 then argstr := argstr || ', '; end if; argstr := argstr || TG_argv[i]; end loop; raise notice '% % % % (%)', TG_RELNAME, TG_WHEN, TG_OP, TG_LEVEL, argstr; if TG_LEVEL = 'ROW' then if TG_OP = 'INSERT' then raise NOTICE 'NEW: %', NEW; INSERT INTO main_table VALUES (NEW.a, NEW.b); RETURN NEW; end if; if TG_OP = 'UPDATE' then raise NOTICE 'OLD: %, NEW: %', OLD, NEW; UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b; if NOT FOUND then RETURN NULL; end if; RETURN NEW; end if; if TG_OP = 'DELETE' then raise NOTICE 'OLD: %', OLD; DELETE FROM main_table WHERE a = OLD.a AND b = OLD.b; if NOT FOUND then RETURN NULL; end if; RETURN OLD; end if; end if; RETURN NULL; end; $$; -- Before row triggers aren't allowed on views CREATE TRIGGER invalid_trig BEFORE INSERT ON main_view FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_ins_row'); ERROR: "main_view" is a view DETAIL: Views cannot have row-level BEFORE or AFTER triggers. CREATE TRIGGER invalid_trig BEFORE UPDATE ON main_view FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_upd_row'); ERROR: "main_view" is a view DETAIL: Views cannot have row-level BEFORE or AFTER triggers. CREATE TRIGGER invalid_trig BEFORE DELETE ON main_view FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_del_row'); ERROR: "main_view" is a view DETAIL: Views cannot have row-level BEFORE or AFTER triggers. -- After row triggers aren't allowed on views CREATE TRIGGER invalid_trig AFTER INSERT ON main_view FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_ins_row'); ERROR: "main_view" is a view DETAIL: Views cannot have row-level BEFORE or AFTER triggers. CREATE TRIGGER invalid_trig AFTER UPDATE ON main_view FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_upd_row'); ERROR: "main_view" is a view DETAIL: Views cannot have row-level BEFORE or AFTER triggers. CREATE TRIGGER invalid_trig AFTER DELETE ON main_view FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_del_row'); ERROR: "main_view" is a view DETAIL: Views cannot have row-level BEFORE or AFTER triggers. -- Truncate triggers aren't allowed on views CREATE TRIGGER invalid_trig BEFORE TRUNCATE ON main_view EXECUTE PROCEDURE trigger_func('before_tru_row'); ERROR: "main_view" is a view DETAIL: Views cannot have TRUNCATE triggers. CREATE TRIGGER invalid_trig AFTER TRUNCATE ON main_view EXECUTE PROCEDURE trigger_func('before_tru_row'); ERROR: "main_view" is a view DETAIL: Views cannot have TRUNCATE triggers. -- INSTEAD OF triggers aren't allowed on tables CREATE TRIGGER invalid_trig INSTEAD OF INSERT ON main_table FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_ins'); ERROR: "main_table" is a table DETAIL: Tables cannot have INSTEAD OF triggers. CREATE TRIGGER invalid_trig INSTEAD OF UPDATE ON main_table FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd'); ERROR: "main_table" is a table DETAIL: Tables cannot have INSTEAD OF triggers. CREATE TRIGGER invalid_trig INSTEAD OF DELETE ON main_table FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_del'); ERROR: "main_table" is a table DETAIL: Tables cannot have INSTEAD OF triggers. -- Don't support WHEN clauses with INSTEAD OF triggers CREATE TRIGGER invalid_trig INSTEAD OF UPDATE ON main_view FOR EACH ROW WHEN (OLD.a <> NEW.a) EXECUTE PROCEDURE view_trigger('instead_of_upd'); ERROR: INSTEAD OF triggers cannot have WHEN conditions -- Don't support column-level INSTEAD OF triggers CREATE TRIGGER invalid_trig INSTEAD OF UPDATE OF a ON main_view FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd'); ERROR: INSTEAD OF triggers cannot have column lists -- Don't support statement-level INSTEAD OF triggers CREATE TRIGGER invalid_trig INSTEAD OF UPDATE ON main_view EXECUTE PROCEDURE view_trigger('instead_of_upd'); ERROR: INSTEAD OF triggers must be FOR EACH ROW -- Valid INSTEAD OF triggers CREATE TRIGGER instead_of_insert_trig INSTEAD OF INSERT ON main_view FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_ins'); CREATE TRIGGER instead_of_update_trig INSTEAD OF UPDATE ON main_view FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd'); CREATE TRIGGER instead_of_delete_trig INSTEAD OF DELETE ON main_view FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_del'); -- Valid BEFORE statement VIEW triggers CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_ins_stmt'); CREATE TRIGGER before_upd_stmt_trig BEFORE UPDATE ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_upd_stmt'); CREATE TRIGGER before_del_stmt_trig BEFORE DELETE ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_del_stmt'); -- Valid AFTER statement VIEW triggers CREATE TRIGGER after_ins_stmt_trig AFTER INSERT ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_ins_stmt'); CREATE TRIGGER after_upd_stmt_trig AFTER UPDATE ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_upd_stmt'); CREATE TRIGGER after_del_stmt_trig AFTER DELETE ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_del_stmt'); \set QUIET false -- Insert into view using trigger INSERT INTO main_view VALUES (20, 30); NOTICE: main_view BEFORE INSERT STATEMENT (before_view_ins_stmt) NOTICE: main_view INSTEAD OF INSERT ROW (instead_of_ins) NOTICE: NEW: (20,30) NOTICE: trigger_func(before_ins_stmt) called: action = INSERT, when = BEFORE, level = STATEMENT NOTICE: trigger_func(after_ins_stmt) called: action = INSERT, when = AFTER, level = STATEMENT NOTICE: main_view AFTER INSERT STATEMENT (after_view_ins_stmt) INSERT 0 1 INSERT INTO main_view VALUES (21, 31) RETURNING a, b; NOTICE: main_view BEFORE INSERT STATEMENT (before_view_ins_stmt) NOTICE: main_view INSTEAD OF INSERT ROW (instead_of_ins) NOTICE: NEW: (21,31) NOTICE: trigger_func(before_ins_stmt) called: action = INSERT, when = BEFORE, level = STATEMENT NOTICE: trigger_func(after_ins_stmt) called: action = INSERT, when = AFTER, level = STATEMENT NOTICE: main_view AFTER INSERT STATEMENT (after_view_ins_stmt) a | b ----+---- 21 | 31 (1 row) INSERT 0 1 -- Table trigger will prevent updates UPDATE main_view SET b = 31 WHERE a = 20; NOTICE: main_view BEFORE UPDATE STATEMENT (before_view_upd_stmt) NOTICE: main_view INSTEAD OF UPDATE ROW (instead_of_upd) NOTICE: OLD: (20,30), NEW: (20,31) NOTICE: trigger_func(before_upd_a_stmt) called: action = UPDATE, when = BEFORE, level = STATEMENT NOTICE: trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW NOTICE: trigger_func(after_upd_b_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT NOTICE: main_view AFTER UPDATE STATEMENT (after_view_upd_stmt) UPDATE 0 UPDATE main_view SET b = 32 WHERE a = 21 AND b = 31 RETURNING a, b; NOTICE: main_view BEFORE UPDATE STATEMENT (before_view_upd_stmt) NOTICE: main_view INSTEAD OF UPDATE ROW (instead_of_upd) NOTICE: OLD: (21,31), NEW: (21,32) NOTICE: trigger_func(before_upd_a_stmt) called: action = UPDATE, when = BEFORE, level = STATEMENT NOTICE: trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW NOTICE: trigger_func(after_upd_b_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT NOTICE: main_view AFTER UPDATE STATEMENT (after_view_upd_stmt) a | b ---+--- (0 rows) UPDATE 0 -- Remove table trigger to allow updates DROP TRIGGER before_upd_a_row_trig ON main_table; DROP TRIGGER UPDATE main_view SET b = 31 WHERE a = 20; NOTICE: main_view BEFORE UPDATE STATEMENT (before_view_upd_stmt) NOTICE: main_view INSTEAD OF UPDATE ROW (instead_of_upd) NOTICE: OLD: (20,30), NEW: (20,31) NOTICE: trigger_func(before_upd_a_stmt) called: action = UPDATE, when = BEFORE, level = STATEMENT NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW NOTICE: trigger_func(after_upd_b_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT NOTICE: main_view AFTER UPDATE STATEMENT (after_view_upd_stmt) UPDATE 1 UPDATE main_view SET b = 32 WHERE a = 21 AND b = 31 RETURNING a, b; NOTICE: main_view BEFORE UPDATE STATEMENT (before_view_upd_stmt) NOTICE: main_view INSTEAD OF UPDATE ROW (instead_of_upd) NOTICE: OLD: (21,31), NEW: (21,32) NOTICE: trigger_func(before_upd_a_stmt) called: action = UPDATE, when = BEFORE, level = STATEMENT NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW NOTICE: trigger_func(after_upd_b_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT NOTICE: main_view AFTER UPDATE STATEMENT (after_view_upd_stmt) a | b ----+---- 21 | 32 (1 row) UPDATE 1 -- Before and after stmt triggers should fire even when no rows are affected UPDATE main_view SET b = 0 WHERE false; NOTICE: main_view BEFORE UPDATE STATEMENT (before_view_upd_stmt) NOTICE: main_view AFTER UPDATE STATEMENT (after_view_upd_stmt) UPDATE 0 -- Delete from view using trigger DELETE FROM main_view WHERE a IN (20,21); NOTICE: main_view BEFORE DELETE STATEMENT (before_view_del_stmt) NOTICE: main_view INSTEAD OF DELETE ROW (instead_of_del) NOTICE: OLD: (21,10) NOTICE: main_view INSTEAD OF DELETE ROW (instead_of_del) NOTICE: OLD: (20,31) NOTICE: main_view INSTEAD OF DELETE ROW (instead_of_del) NOTICE: OLD: (21,32) NOTICE: main_view AFTER DELETE STATEMENT (after_view_del_stmt) DELETE 3 DELETE FROM main_view WHERE a = 31 RETURNING a, b; NOTICE: main_view BEFORE DELETE STATEMENT (before_view_del_stmt) NOTICE: main_view INSTEAD OF DELETE ROW (instead_of_del) NOTICE: OLD: (31,10) NOTICE: main_view AFTER DELETE STATEMENT (after_view_del_stmt) a | b ----+---- 31 | 10 (1 row) DELETE 1 \set QUIET true -- Describe view should list triggers \d main_view View "public.main_view" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- a | integer | | | b | integer | | | Triggers: after_del_stmt_trig AFTER DELETE ON main_view FOR EACH STATEMENT EXECUTE FUNCTION view_trigger('after_view_del_stmt') after_ins_stmt_trig AFTER INSERT ON main_view FOR EACH STATEMENT EXECUTE FUNCTION view_trigger('after_view_ins_stmt') after_upd_stmt_trig AFTER UPDATE ON main_view FOR EACH STATEMENT EXECUTE FUNCTION view_trigger('after_view_upd_stmt') before_del_stmt_trig BEFORE DELETE ON main_view FOR EACH STATEMENT EXECUTE FUNCTION view_trigger('before_view_del_stmt') before_ins_stmt_trig BEFORE INSERT ON main_view FOR EACH STATEMENT EXECUTE FUNCTION view_trigger('before_view_ins_stmt') before_upd_stmt_trig BEFORE UPDATE ON main_view FOR EACH STATEMENT EXECUTE FUNCTION view_trigger('before_view_upd_stmt') instead_of_delete_trig INSTEAD OF DELETE ON main_view FOR EACH ROW EXECUTE FUNCTION view_trigger('instead_of_del') instead_of_insert_trig INSTEAD OF INSERT ON main_view FOR EACH ROW EXECUTE FUNCTION view_trigger('instead_of_ins') instead_of_update_trig INSTEAD OF UPDATE ON main_view FOR EACH ROW EXECUTE FUNCTION view_trigger('instead_of_upd') -- Test dropping view triggers DROP TRIGGER instead_of_insert_trig ON main_view; DROP TRIGGER instead_of_delete_trig ON main_view; \d+ main_view View "public.main_view" Column | Type | Collation | Nullable | Default | Storage | Description --------+---------+-----------+----------+---------+---------+------------- a | integer | | | | plain | b | integer | | | | plain | View definition: SELECT main_table.a, main_table.b FROM main_table; Triggers: after_del_stmt_trig AFTER DELETE ON main_view FOR EACH STATEMENT EXECUTE FUNCTION view_trigger('after_view_del_stmt') after_ins_stmt_trig AFTER INSERT ON main_view FOR EACH STATEMENT EXECUTE FUNCTION view_trigger('after_view_ins_stmt') after_upd_stmt_trig AFTER UPDATE ON main_view FOR EACH STATEMENT EXECUTE FUNCTION view_trigger('after_view_upd_stmt') before_del_stmt_trig BEFORE DELETE ON main_view FOR EACH STATEMENT EXECUTE FUNCTION view_trigger('before_view_del_stmt') before_ins_stmt_trig BEFORE INSERT ON main_view FOR EACH STATEMENT EXECUTE FUNCTION view_trigger('before_view_ins_stmt') before_upd_stmt_trig BEFORE UPDATE ON main_view FOR EACH STATEMENT EXECUTE FUNCTION view_trigger('before_view_upd_stmt') instead_of_update_trig INSTEAD OF UPDATE ON main_view FOR EACH ROW EXECUTE FUNCTION view_trigger('instead_of_upd') DROP VIEW main_view; -- -- Test triggers on a join view -- CREATE TABLE country_table ( country_id serial primary key, country_name text unique not null, continent text not null ); INSERT INTO country_table (country_name, continent) VALUES ('Japan', 'Asia'), ('UK', 'Europe'), ('USA', 'North America') RETURNING *; country_id | country_name | continent ------------+--------------+--------------- 1 | Japan | Asia 2 | UK | Europe 3 | USA | North America (3 rows) CREATE TABLE city_table ( city_id serial primary key, city_name text not null, population bigint, country_id int references country_table ); CREATE VIEW city_view AS SELECT city_id, city_name, population, country_name, continent FROM city_table ci LEFT JOIN country_table co ON co.country_id = ci.country_id; CREATE FUNCTION city_insert() RETURNS trigger LANGUAGE plpgsql AS $$ declare ctry_id int; begin if NEW.country_name IS NOT NULL then SELECT country_id, continent INTO ctry_id, NEW.continent FROM country_table WHERE country_name = NEW.country_name; if NOT FOUND then raise exception 'No such country: "%"', NEW.country_name; end if; else NEW.continent := NULL; end if; if NEW.city_id IS NOT NULL then INSERT INTO city_table VALUES(NEW.city_id, NEW.city_name, NEW.population, ctry_id); else INSERT INTO city_table(city_name, population, country_id) VALUES(NEW.city_name, NEW.population, ctry_id) RETURNING city_id INTO NEW.city_id; end if; RETURN NEW; end; $$; CREATE TRIGGER city_insert_trig INSTEAD OF INSERT ON city_view FOR EACH ROW EXECUTE PROCEDURE city_insert(); CREATE FUNCTION city_delete() RETURNS trigger LANGUAGE plpgsql AS $$ begin DELETE FROM city_table WHERE city_id = OLD.city_id; if NOT FOUND then RETURN NULL; end if; RETURN OLD; end; $$; CREATE TRIGGER city_delete_trig INSTEAD OF DELETE ON city_view FOR EACH ROW EXECUTE PROCEDURE city_delete(); CREATE FUNCTION city_update() RETURNS trigger LANGUAGE plpgsql AS $$ declare ctry_id int; begin if NEW.country_name IS DISTINCT FROM OLD.country_name then SELECT country_id, continent INTO ctry_id, NEW.continent FROM country_table WHERE country_name = NEW.country_name; if NOT FOUND then raise exception 'No such country: "%"', NEW.country_name; end if; UPDATE city_table SET city_name = NEW.city_name, population = NEW.population, country_id = ctry_id WHERE city_id = OLD.city_id; else UPDATE city_table SET city_name = NEW.city_name, population = NEW.population WHERE city_id = OLD.city_id; NEW.continent := OLD.continent; end if; if NOT FOUND then RETURN NULL; end if; RETURN NEW; end; $$; CREATE TRIGGER city_update_trig INSTEAD OF UPDATE ON city_view FOR EACH ROW EXECUTE PROCEDURE city_update(); \set QUIET false -- INSERT .. RETURNING INSERT INTO city_view(city_name) VALUES('Tokyo') RETURNING *; city_id | city_name | population | country_name | continent ---------+-----------+------------+--------------+----------- 1 | Tokyo | | | (1 row) INSERT 0 1 INSERT INTO city_view(city_name, population) VALUES('London', 7556900) RETURNING *; city_id | city_name | population | country_name | continent ---------+-----------+------------+--------------+----------- 2 | London | 7556900 | | (1 row) INSERT 0 1 INSERT INTO city_view(city_name, country_name) VALUES('Washington DC', 'USA') RETURNING *; city_id | city_name | population | country_name | continent ---------+---------------+------------+--------------+--------------- 3 | Washington DC | | USA | North America (1 row) INSERT 0 1 INSERT INTO city_view(city_id, city_name) VALUES(123456, 'New York') RETURNING *; city_id | city_name | population | country_name | continent ---------+-----------+------------+--------------+----------- 123456 | New York | | | (1 row) INSERT 0 1 INSERT INTO city_view VALUES(234567, 'Birmingham', 1016800, 'UK', 'EU') RETURNING *; city_id | city_name | population | country_name | continent ---------+------------+------------+--------------+----------- 234567 | Birmingham | 1016800 | UK | Europe (1 row) INSERT 0 1 -- UPDATE .. RETURNING UPDATE city_view SET country_name = 'Japon' WHERE city_name = 'Tokyo'; -- error ERROR: No such country: "Japon" CONTEXT: PL/pgSQL function city_update() line 9 at RAISE UPDATE city_view SET country_name = 'Japan' WHERE city_name = 'Takyo'; -- no match UPDATE 0 UPDATE city_view SET country_name = 'Japan' WHERE city_name = 'Tokyo' RETURNING *; -- OK city_id | city_name | population | country_name | continent ---------+-----------+------------+--------------+----------- 1 | Tokyo | | Japan | Asia (1 row) UPDATE 1 UPDATE city_view SET population = 13010279 WHERE city_name = 'Tokyo' RETURNING *; city_id | city_name | population | country_name | continent ---------+-----------+------------+--------------+----------- 1 | Tokyo | 13010279 | Japan | Asia (1 row) UPDATE 1 UPDATE city_view SET country_name = 'UK' WHERE city_name = 'New York' RETURNING *; city_id | city_name | population | country_name | continent ---------+-----------+------------+--------------+----------- 123456 | New York | | UK | Europe (1 row) UPDATE 1 UPDATE city_view SET country_name = 'USA', population = 8391881 WHERE city_name = 'New York' RETURNING *; city_id | city_name | population | country_name | continent ---------+-----------+------------+--------------+--------------- 123456 | New York | 8391881 | USA | North America (1 row) UPDATE 1 UPDATE city_view SET continent = 'EU' WHERE continent = 'Europe' RETURNING *; city_id | city_name | population | country_name | continent ---------+------------+------------+--------------+----------- 234567 | Birmingham | 1016800 | UK | Europe (1 row) UPDATE 1 UPDATE city_view v1 SET country_name = v2.country_name FROM city_view v2 WHERE v2.city_name = 'Birmingham' AND v1.city_name = 'London' RETURNING *; city_id | city_name | population | country_name | continent | city_id | city_name | population | country_name | continent ---------+-----------+------------+--------------+-----------+---------+------------+------------+--------------+----------- 2 | London | 7556900 | UK | Europe | 234567 | Birmingham | 1016800 | UK | Europe (1 row) UPDATE 1 -- DELETE .. RETURNING DELETE FROM city_view WHERE city_name = 'Birmingham' RETURNING *; city_id | city_name | population | country_name | continent ---------+------------+------------+--------------+----------- 234567 | Birmingham | 1016800 | UK | Europe (1 row) DELETE 1 \set QUIET true -- read-only view with WHERE clause CREATE VIEW european_city_view AS SELECT * FROM city_view WHERE continent = 'Europe'; SELECT count(*) FROM european_city_view; count ------- 1 (1 row) CREATE FUNCTION no_op_trig_fn() RETURNS trigger LANGUAGE plpgsql AS 'begin RETURN NULL; end'; CREATE TRIGGER no_op_trig INSTEAD OF INSERT OR UPDATE OR DELETE ON european_city_view FOR EACH ROW EXECUTE PROCEDURE no_op_trig_fn(); \set QUIET false INSERT INTO european_city_view VALUES (0, 'x', 10000, 'y', 'z'); INSERT 0 0 UPDATE european_city_view SET population = 10000; UPDATE 0 DELETE FROM european_city_view; DELETE 0 \set QUIET true -- rules bypassing no-op triggers CREATE RULE european_city_insert_rule AS ON INSERT TO european_city_view DO INSTEAD INSERT INTO city_view VALUES (NEW.city_id, NEW.city_name, NEW.population, NEW.country_name, NEW.continent) RETURNING *; CREATE RULE european_city_update_rule AS ON UPDATE TO european_city_view DO INSTEAD UPDATE city_view SET city_name = NEW.city_name, population = NEW.population, country_name = NEW.country_name WHERE city_id = OLD.city_id RETURNING NEW.*; CREATE RULE european_city_delete_rule AS ON DELETE TO european_city_view DO INSTEAD DELETE FROM city_view WHERE city_id = OLD.city_id RETURNING *; \set QUIET false -- INSERT not limited by view's WHERE clause, but UPDATE AND DELETE are INSERT INTO european_city_view(city_name, country_name) VALUES ('Cambridge', 'USA') RETURNING *; city_id | city_name | population | country_name | continent ---------+-----------+------------+--------------+--------------- 4 | Cambridge | | USA | North America (1 row) INSERT 0 1 UPDATE european_city_view SET country_name = 'UK' WHERE city_name = 'Cambridge'; UPDATE 0 DELETE FROM european_city_view WHERE city_name = 'Cambridge'; DELETE 0 -- UPDATE and DELETE via rule and trigger UPDATE city_view SET country_name = 'UK' WHERE city_name = 'Cambridge' RETURNING *; city_id | city_name | population | country_name | continent ---------+-----------+------------+--------------+----------- 4 | Cambridge | | UK | Europe (1 row) UPDATE 1 UPDATE european_city_view SET population = 122800 WHERE city_name = 'Cambridge' RETURNING *; city_id | city_name | population | country_name | continent ---------+-----------+------------+--------------+----------- 4 | Cambridge | 122800 | UK | Europe (1 row) UPDATE 1 DELETE FROM european_city_view WHERE city_name = 'Cambridge' RETURNING *; city_id | city_name | population | country_name | continent ---------+-----------+------------+--------------+----------- 4 | Cambridge | 122800 | UK | Europe (1 row) DELETE 1 -- join UPDATE test UPDATE city_view v SET population = 599657 FROM city_table ci, country_table co WHERE ci.city_name = 'Washington DC' and co.country_name = 'USA' AND v.city_id = ci.city_id AND v.country_name = co.country_name RETURNING co.country_id, v.country_name, v.city_id, v.city_name, v.population; country_id | country_name | city_id | city_name | population ------------+--------------+---------+---------------+------------ 3 | USA | 3 | Washington DC | 599657 (1 row) UPDATE 1 \set QUIET true SELECT * FROM city_view; city_id | city_name | population | country_name | continent ---------+---------------+------------+--------------+--------------- 1 | Tokyo | 13010279 | Japan | Asia 123456 | New York | 8391881 | USA | North America 2 | London | 7556900 | UK | Europe 3 | Washington DC | 599657 | USA | North America (4 rows) DROP TABLE city_table CASCADE; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to view city_view drop cascades to view european_city_view DROP TABLE country_table; -- Test pg_trigger_depth() create table depth_a (id int not null primary key); create table depth_b (id int not null primary key); create table depth_c (id int not null primary key); create function depth_a_tf() returns trigger language plpgsql as $$ begin raise notice '%: depth = %', tg_name, pg_trigger_depth(); insert into depth_b values (new.id); raise notice '%: depth = %', tg_name, pg_trigger_depth(); return new; end; $$; create trigger depth_a_tr before insert on depth_a for each row execute procedure depth_a_tf(); create function depth_b_tf() returns trigger language plpgsql as $$ begin raise notice '%: depth = %', tg_name, pg_trigger_depth(); begin execute 'insert into depth_c values (' || new.id::text || ')'; exception when sqlstate 'U9999' then raise notice 'SQLSTATE = U9999: depth = %', pg_trigger_depth(); end; raise notice '%: depth = %', tg_name, pg_trigger_depth(); if new.id = 1 then execute 'insert into depth_c values (' || new.id::text || ')'; end if; return new; end; $$; create trigger depth_b_tr before insert on depth_b for each row execute procedure depth_b_tf(); create function depth_c_tf() returns trigger language plpgsql as $$ begin raise notice '%: depth = %', tg_name, pg_trigger_depth(); if new.id = 1 then raise exception sqlstate 'U9999'; end if; raise notice '%: depth = %', tg_name, pg_trigger_depth(); return new; end; $$; create trigger depth_c_tr before insert on depth_c for each row execute procedure depth_c_tf(); select pg_trigger_depth(); pg_trigger_depth ------------------ 0 (1 row) insert into depth_a values (1); NOTICE: depth_a_tr: depth = 1 NOTICE: depth_b_tr: depth = 2 NOTICE: depth_c_tr: depth = 3 NOTICE: SQLSTATE = U9999: depth = 2 NOTICE: depth_b_tr: depth = 2 NOTICE: depth_c_tr: depth = 3 ERROR: U9999 CONTEXT: PL/pgSQL function depth_c_tf() line 5 at RAISE SQL statement "insert into depth_c values (1)" PL/pgSQL function depth_b_tf() line 12 at EXECUTE SQL statement "insert into depth_b values (new.id)" PL/pgSQL function depth_a_tf() line 4 at SQL statement select pg_trigger_depth(); pg_trigger_depth ------------------ 0 (1 row) insert into depth_a values (2); NOTICE: depth_a_tr: depth = 1 NOTICE: depth_b_tr: depth = 2 NOTICE: depth_c_tr: depth = 3 NOTICE: depth_c_tr: depth = 3 NOTICE: depth_b_tr: depth = 2 NOTICE: depth_a_tr: depth = 1 select pg_trigger_depth(); pg_trigger_depth ------------------ 0 (1 row) drop table depth_a, depth_b, depth_c; drop function depth_a_tf(); drop function depth_b_tf(); drop function depth_c_tf(); -- -- Test updates to rows during firing of BEFORE ROW triggers. -- As of 9.2, such cases should be rejected (see bug #6123). -- create temp table parent ( aid int not null primary key, val1 text, val2 text, val3 text, val4 text, bcnt int not null default 0); create temp table child ( bid int not null primary key, aid int not null, val1 text); create function parent_upd_func() returns trigger language plpgsql as $$ begin if old.val1 <> new.val1 then new.val2 = new.val1; delete from child where child.aid = new.aid and child.val1 = new.val1; end if; return new; end; $$; create trigger parent_upd_trig before update on parent for each row execute procedure parent_upd_func(); create function parent_del_func() returns trigger language plpgsql as $$ begin delete from child where aid = old.aid; return old; end; $$; create trigger parent_del_trig before delete on parent for each row execute procedure parent_del_func(); create function child_ins_func() returns trigger language plpgsql as $$ begin update parent set bcnt = bcnt + 1 where aid = new.aid; return new; end; $$; create trigger child_ins_trig after insert on child for each row execute procedure child_ins_func(); create function child_del_func() returns trigger language plpgsql as $$ begin update parent set bcnt = bcnt - 1 where aid = old.aid; return old; end; $$; create trigger child_del_trig after delete on child for each row execute procedure child_del_func(); insert into parent values (1, 'a', 'a', 'a', 'a', 0); insert into child values (10, 1, 'b'); select * from parent; select * from child; aid | val1 | val2 | val3 | val4 | bcnt -----+------+------+------+------+------ 1 | a | a | a | a | 1 (1 row) bid | aid | val1 -----+-----+------ 10 | 1 | b (1 row) update parent set val1 = 'b' where aid = 1; -- should fail ERROR: tuple to be updated was already modified by an operation triggered by the current command HINT: Consider using an AFTER trigger instead of a BEFORE trigger to propagate changes to other rows. select * from parent; select * from child; aid | val1 | val2 | val3 | val4 | bcnt -----+------+------+------+------+------ 1 | a | a | a | a | 1 (1 row) bid | aid | val1 -----+-----+------ 10 | 1 | b (1 row) delete from parent where aid = 1; -- should fail ERROR: tuple to be deleted was already modified by an operation triggered by the current command HINT: Consider using an AFTER trigger instead of a BEFORE trigger to propagate changes to other rows. select * from parent; select * from child; aid | val1 | val2 | val3 | val4 | bcnt -----+------+------+------+------+------ 1 | a | a | a | a | 1 (1 row) bid | aid | val1 -----+-----+------ 10 | 1 | b (1 row) -- replace the trigger function with one that restarts the deletion after -- having modified a child create or replace function parent_del_func() returns trigger language plpgsql as $$ begin delete from child where aid = old.aid; if found then delete from parent where aid = old.aid; return null; -- cancel outer deletion end if; return old; end; $$; delete from parent where aid = 1; select * from parent; select * from child; aid | val1 | val2 | val3 | val4 | bcnt -----+------+------+------+------+------ (0 rows) bid | aid | val1 -----+-----+------ (0 rows) drop table parent, child; drop function parent_upd_func(); drop function parent_del_func(); drop function child_ins_func(); drop function child_del_func(); -- similar case, but with a self-referencing FK so that parent and child -- rows can be affected by a single operation create temp table self_ref_trigger ( id int primary key, parent int references self_ref_trigger, data text, nchildren int not null default 0 ); create function self_ref_trigger_ins_func() returns trigger language plpgsql as $$ begin if new.parent is not null then update self_ref_trigger set nchildren = nchildren + 1 where id = new.parent; end if; return new; end; $$; create trigger self_ref_trigger_ins_trig before insert on self_ref_trigger for each row execute procedure self_ref_trigger_ins_func(); create function self_ref_trigger_del_func() returns trigger language plpgsql as $$ begin if old.parent is not null then update self_ref_trigger set nchildren = nchildren - 1 where id = old.parent; end if; return old; end; $$; create trigger self_ref_trigger_del_trig before delete on self_ref_trigger for each row execute procedure self_ref_trigger_del_func(); insert into self_ref_trigger values (1, null, 'root'); insert into self_ref_trigger values (2, 1, 'root child A'); insert into self_ref_trigger values (3, 1, 'root child B'); insert into self_ref_trigger values (4, 2, 'grandchild 1'); insert into self_ref_trigger values (5, 3, 'grandchild 2'); update self_ref_trigger set data = 'root!' where id = 1; select * from self_ref_trigger; id | parent | data | nchildren ----+--------+--------------+----------- 2 | 1 | root child A | 1 4 | 2 | grandchild 1 | 0 3 | 1 | root child B | 1 5 | 3 | grandchild 2 | 0 1 | | root! | 2 (5 rows) delete from self_ref_trigger; ERROR: tuple to be updated was already modified by an operation triggered by the current command HINT: Consider using an AFTER trigger instead of a BEFORE trigger to propagate changes to other rows. select * from self_ref_trigger; id | parent | data | nchildren ----+--------+--------------+----------- 2 | 1 | root child A | 1 4 | 2 | grandchild 1 | 0 3 | 1 | root child B | 1 5 | 3 | grandchild 2 | 0 1 | | root! | 2 (5 rows) drop table self_ref_trigger; drop function self_ref_trigger_ins_func(); drop function self_ref_trigger_del_func(); -- -- Check that statement triggers work correctly even with all children excluded -- create table stmt_trig_on_empty_upd (a int); create table stmt_trig_on_empty_upd1 () inherits (stmt_trig_on_empty_upd); create function update_stmt_notice() returns trigger as $$ begin raise notice 'updating %', TG_TABLE_NAME; return null; end; $$ language plpgsql; create trigger before_stmt_trigger before update on stmt_trig_on_empty_upd execute procedure update_stmt_notice(); create trigger before_stmt_trigger before update on stmt_trig_on_empty_upd1 execute procedure update_stmt_notice(); -- inherited no-op update update stmt_trig_on_empty_upd set a = a where false returning a+1 as aa; NOTICE: updating stmt_trig_on_empty_upd aa ---- (0 rows) -- simple no-op update update stmt_trig_on_empty_upd1 set a = a where false returning a+1 as aa; NOTICE: updating stmt_trig_on_empty_upd1 aa ---- (0 rows) drop table stmt_trig_on_empty_upd cascade; NOTICE: drop cascades to table stmt_trig_on_empty_upd1 drop function update_stmt_notice(); -- -- Check that index creation (or DDL in general) is prohibited in a trigger -- create table trigger_ddl_table ( col1 integer, col2 integer ); create function trigger_ddl_func() returns trigger as $$ begin alter table trigger_ddl_table add primary key (col1); return new; end$$ language plpgsql; create trigger trigger_ddl_func before insert on trigger_ddl_table for each row execute procedure trigger_ddl_func(); insert into trigger_ddl_table values (1, 42); -- fail ERROR: cannot ALTER TABLE "trigger_ddl_table" because it is being used by active queries in this session CONTEXT: SQL statement "alter table trigger_ddl_table add primary key (col1)" PL/pgSQL function trigger_ddl_func() line 3 at SQL statement create or replace function trigger_ddl_func() returns trigger as $$ begin create index on trigger_ddl_table (col2); return new; end$$ language plpgsql; insert into trigger_ddl_table values (1, 42); -- fail ERROR: cannot CREATE INDEX "trigger_ddl_table" because it is being used by active queries in this session CONTEXT: SQL statement "create index on trigger_ddl_table (col2)" PL/pgSQL function trigger_ddl_func() line 3 at SQL statement drop table trigger_ddl_table; drop function trigger_ddl_func(); -- -- Verify behavior of before and after triggers with INSERT...ON CONFLICT -- DO UPDATE -- create table upsert (key int4 primary key, color text); create function upsert_before_func() returns trigger language plpgsql as $$ begin if (TG_OP = 'UPDATE') then raise warning 'before update (old): %', old.*::text; raise warning 'before update (new): %', new.*::text; elsif (TG_OP = 'INSERT') then raise warning 'before insert (new): %', new.*::text; if new.key % 2 = 0 then new.key := new.key + 1; new.color := new.color || ' trig modified'; raise warning 'before insert (new, modified): %', new.*::text; end if; end if; return new; end; $$; create trigger upsert_before_trig before insert or update on upsert for each row execute procedure upsert_before_func(); create function upsert_after_func() returns trigger language plpgsql as $$ begin if (TG_OP = 'UPDATE') then raise warning 'after update (old): %', old.*::text; raise warning 'after update (new): %', new.*::text; elsif (TG_OP = 'INSERT') then raise warning 'after insert (new): %', new.*::text; end if; return null; end; $$; create trigger upsert_after_trig after insert or update on upsert for each row execute procedure upsert_after_func(); insert into upsert values(1, 'black') on conflict (key) do update set color = 'updated ' || upsert.color; WARNING: before insert (new): (1,black) WARNING: after insert (new): (1,black) insert into upsert values(2, 'red') on conflict (key) do update set color = 'updated ' || upsert.color; WARNING: before insert (new): (2,red) WARNING: before insert (new, modified): (3,"red trig modified") WARNING: after insert (new): (3,"red trig modified") insert into upsert values(3, 'orange') on conflict (key) do update set color = 'updated ' || upsert.color; WARNING: before insert (new): (3,orange) WARNING: before update (old): (3,"red trig modified") WARNING: before update (new): (3,"updated red trig modified") WARNING: after update (old): (3,"red trig modified") WARNING: after update (new): (3,"updated red trig modified") insert into upsert values(4, 'green') on conflict (key) do update set color = 'updated ' || upsert.color; WARNING: before insert (new): (4,green) WARNING: before insert (new, modified): (5,"green trig modified") WARNING: after insert (new): (5,"green trig modified") insert into upsert values(5, 'purple') on conflict (key) do update set color = 'updated ' || upsert.color; WARNING: before insert (new): (5,purple) WARNING: before update (old): (5,"green trig modified") WARNING: before update (new): (5,"updated green trig modified") WARNING: after update (old): (5,"green trig modified") WARNING: after update (new): (5,"updated green trig modified") insert into upsert values(6, 'white') on conflict (key) do update set color = 'updated ' || upsert.color; WARNING: before insert (new): (6,white) WARNING: before insert (new, modified): (7,"white trig modified") WARNING: after insert (new): (7,"white trig modified") insert into upsert values(7, 'pink') on conflict (key) do update set color = 'updated ' || upsert.color; WARNING: before insert (new): (7,pink) WARNING: before update (old): (7,"white trig modified") WARNING: before update (new): (7,"updated white trig modified") WARNING: after update (old): (7,"white trig modified") WARNING: after update (new): (7,"updated white trig modified") insert into upsert values(8, 'yellow') on conflict (key) do update set color = 'updated ' || upsert.color; WARNING: before insert (new): (8,yellow) WARNING: before insert (new, modified): (9,"yellow trig modified") WARNING: after insert (new): (9,"yellow trig modified") select * from upsert; key | color -----+----------------------------- 1 | black 3 | updated red trig modified 5 | updated green trig modified 7 | updated white trig modified 9 | yellow trig modified (5 rows) drop table upsert; drop function upsert_before_func(); drop function upsert_after_func(); -- -- Verify that triggers with transition tables are not allowed on -- views -- create table my_table (i int); create view my_view as select * from my_table; create function my_trigger_function() returns trigger as $$ begin end; $$ language plpgsql; create trigger my_trigger after update on my_view referencing old table as old_table for each statement execute procedure my_trigger_function(); ERROR: "my_view" is a view DETAIL: Triggers on views cannot have transition tables. drop function my_trigger_function(); drop view my_view; drop table my_table; -- -- Verify cases that are unsupported with partitioned tables -- create table parted_trig (a int) partition by list (a); create function trigger_nothing() returns trigger language plpgsql as $$ begin end; $$; create trigger failed instead of update on parted_trig for each row execute procedure trigger_nothing(); ERROR: "parted_trig" is a table DETAIL: Tables cannot have INSTEAD OF triggers. create trigger failed after update on parted_trig referencing old table as old_table for each row execute procedure trigger_nothing(); ERROR: "parted_trig" is a partitioned table DETAIL: Triggers on partitioned tables cannot have transition tables. drop table parted_trig; -- -- Verify trigger creation for partitioned tables, and drop behavior -- create table trigpart (a int, b int) partition by range (a); create table trigpart1 partition of trigpart for values from (0) to (1000); create trigger trg1 after insert on trigpart for each row execute procedure trigger_nothing(); create table trigpart2 partition of trigpart for values from (1000) to (2000); create table trigpart3 (like trigpart); alter table trigpart attach partition trigpart3 for values from (2000) to (3000); create table trigpart4 partition of trigpart for values from (3000) to (4000) partition by range (a); create table trigpart41 partition of trigpart4 for values from (3000) to (3500); create table trigpart42 (like trigpart); alter table trigpart4 attach partition trigpart42 for values from (3500) to (4000); select tgrelid::regclass, tgname, tgfoid::regproc from pg_trigger where tgrelid::regclass::text like 'trigpart%' order by tgrelid::regclass::text; tgrelid | tgname | tgfoid ------------+--------+----------------- trigpart | trg1 | trigger_nothing trigpart1 | trg1 | trigger_nothing trigpart2 | trg1 | trigger_nothing trigpart3 | trg1 | trigger_nothing trigpart4 | trg1 | trigger_nothing trigpart41 | trg1 | trigger_nothing trigpart42 | trg1 | trigger_nothing (7 rows) drop trigger trg1 on trigpart1; -- fail ERROR: cannot drop trigger trg1 on table trigpart1 because trigger trg1 on table trigpart requires it HINT: You can drop trigger trg1 on table trigpart instead. drop trigger trg1 on trigpart2; -- fail ERROR: cannot drop trigger trg1 on table trigpart2 because trigger trg1 on table trigpart requires it HINT: You can drop trigger trg1 on table trigpart instead. drop trigger trg1 on trigpart3; -- fail ERROR: cannot drop trigger trg1 on table trigpart3 because trigger trg1 on table trigpart requires it HINT: You can drop trigger trg1 on table trigpart instead. drop table trigpart2; -- ok, trigger should be gone in that partition select tgrelid::regclass, tgname, tgfoid::regproc from pg_trigger where tgrelid::regclass::text like 'trigpart%' order by tgrelid::regclass::text; tgrelid | tgname | tgfoid ------------+--------+----------------- trigpart | trg1 | trigger_nothing trigpart1 | trg1 | trigger_nothing trigpart3 | trg1 | trigger_nothing trigpart4 | trg1 | trigger_nothing trigpart41 | trg1 | trigger_nothing trigpart42 | trg1 | trigger_nothing (6 rows) drop trigger trg1 on trigpart; -- ok, all gone select tgrelid::regclass, tgname, tgfoid::regproc from pg_trigger where tgrelid::regclass::text like 'trigpart%' order by tgrelid::regclass::text; tgrelid | tgname | tgfoid ---------+--------+-------- (0 rows) -- check detach behavior create trigger trg1 after insert on trigpart for each row execute procedure trigger_nothing(); \d trigpart3 Table "public.trigpart3" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- a | integer | | | b | integer | | | Partition of: trigpart FOR VALUES FROM (2000) TO (3000) Triggers: trg1 AFTER INSERT ON trigpart3 FOR EACH ROW EXECUTE FUNCTION trigger_nothing(), ON TABLE trigpart alter table trigpart detach partition trigpart3; drop trigger trg1 on trigpart3; -- fail due to "does not exist" ERROR: trigger "trg1" for table "trigpart3" does not exist alter table trigpart detach partition trigpart4; drop trigger trg1 on trigpart41; -- fail due to "does not exist" ERROR: trigger "trg1" for table "trigpart41" does not exist drop table trigpart4; alter table trigpart attach partition trigpart3 for values from (2000) to (3000); alter table trigpart detach partition trigpart3; alter table trigpart attach partition trigpart3 for values from (2000) to (3000); drop table trigpart3; select tgrelid::regclass::text, tgname, tgfoid::regproc, tgenabled, tgisinternal from pg_trigger where tgname ~ '^trg1' order by 1; tgrelid | tgname | tgfoid | tgenabled | tgisinternal -----------+--------+-----------------+-----------+-------------- trigpart | trg1 | trigger_nothing | O | f trigpart1 | trg1 | trigger_nothing | O | t (2 rows) create table trigpart3 (like trigpart); create trigger trg1 after insert on trigpart3 for each row execute procedure trigger_nothing(); \d trigpart3 Table "public.trigpart3" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- a | integer | | | b | integer | | | Triggers: trg1 AFTER INSERT ON trigpart3 FOR EACH ROW EXECUTE FUNCTION trigger_nothing() alter table trigpart attach partition trigpart3 FOR VALUES FROM (2000) to (3000); -- fail ERROR: trigger "trg1" for relation "trigpart3" already exists drop table trigpart3; drop table trigpart; drop function trigger_nothing(); -- -- Verify that triggers are fired for partitioned tables -- create table parted_stmt_trig (a int) partition by list (a); create table parted_stmt_trig1 partition of parted_stmt_trig for values in (1); create table parted_stmt_trig2 partition of parted_stmt_trig for values in (2); create table parted2_stmt_trig (a int) partition by list (a); create table parted2_stmt_trig1 partition of parted2_stmt_trig for values in (1); create table parted2_stmt_trig2 partition of parted2_stmt_trig for values in (2); create or replace function trigger_notice() returns trigger as $$ begin raise notice 'trigger % on % % % for %', TG_NAME, TG_TABLE_NAME, TG_WHEN, TG_OP, TG_LEVEL; if TG_LEVEL = 'ROW' then return NEW; end if; return null; end; $$ language plpgsql; -- insert/update/delete statement-level triggers on the parent create trigger trig_ins_before before insert on parted_stmt_trig for each statement execute procedure trigger_notice(); create trigger trig_ins_after after insert on parted_stmt_trig for each statement execute procedure trigger_notice(); create trigger trig_upd_before before update on parted_stmt_trig for each statement execute procedure trigger_notice(); create trigger trig_upd_after after update on parted_stmt_trig for each statement execute procedure trigger_notice(); create trigger trig_del_before before delete on parted_stmt_trig for each statement execute procedure trigger_notice(); create trigger trig_del_after after delete on parted_stmt_trig for each statement execute procedure trigger_notice(); -- insert/update/delete row-level triggers on the parent create trigger trig_ins_after_parent after insert on parted_stmt_trig for each row execute procedure trigger_notice(); create trigger trig_upd_after_parent after update on parted_stmt_trig for each row execute procedure trigger_notice(); create trigger trig_del_after_parent after delete on parted_stmt_trig for each row execute procedure trigger_notice(); -- insert/update/delete row-level triggers on the first partition create trigger trig_ins_before_child before insert on parted_stmt_trig1 for each row execute procedure trigger_notice(); create trigger trig_ins_after_child after insert on parted_stmt_trig1 for each row execute procedure trigger_notice(); create trigger trig_upd_before_child before update on parted_stmt_trig1 for each row execute procedure trigger_notice(); create trigger trig_upd_after_child after update on parted_stmt_trig1 for each row execute procedure trigger_notice(); create trigger trig_del_before_child before delete on parted_stmt_trig1 for each row execute procedure trigger_notice(); create trigger trig_del_after_child after delete on parted_stmt_trig1 for each row execute procedure trigger_notice(); -- insert/update/delete statement-level triggers on the parent create trigger trig_ins_before_3 before insert on parted2_stmt_trig for each statement execute procedure trigger_notice(); create trigger trig_ins_after_3 after insert on parted2_stmt_trig for each statement execute procedure trigger_notice(); create trigger trig_upd_before_3 before update on parted2_stmt_trig for each statement execute procedure trigger_notice(); create trigger trig_upd_after_3 after update on parted2_stmt_trig for each statement execute procedure trigger_notice(); create trigger trig_del_before_3 before delete on parted2_stmt_trig for each statement execute procedure trigger_notice(); create trigger trig_del_after_3 after delete on parted2_stmt_trig for each statement execute procedure trigger_notice(); with ins (a) as ( insert into parted2_stmt_trig values (1), (2) returning a ) insert into parted_stmt_trig select a from ins returning tableoid::regclass, a; NOTICE: trigger trig_ins_before on parted_stmt_trig BEFORE INSERT for STATEMENT NOTICE: trigger trig_ins_before_3 on parted2_stmt_trig BEFORE INSERT for STATEMENT NOTICE: trigger trig_ins_before_child on parted_stmt_trig1 BEFORE INSERT for ROW NOTICE: trigger trig_ins_after_child on parted_stmt_trig1 AFTER INSERT for ROW NOTICE: trigger trig_ins_after_parent on parted_stmt_trig1 AFTER INSERT for ROW NOTICE: trigger trig_ins_after_parent on parted_stmt_trig2 AFTER INSERT for ROW NOTICE: trigger trig_ins_after_3 on parted2_stmt_trig AFTER INSERT for STATEMENT NOTICE: trigger trig_ins_after on parted_stmt_trig AFTER INSERT for STATEMENT tableoid | a -------------------+--- parted_stmt_trig1 | 1 parted_stmt_trig2 | 2 (2 rows) with upd as ( update parted2_stmt_trig set a = a ) update parted_stmt_trig set a = a; NOTICE: trigger trig_upd_before on parted_stmt_trig BEFORE UPDATE for STATEMENT NOTICE: trigger trig_upd_before_child on parted_stmt_trig1 BEFORE UPDATE for ROW NOTICE: trigger trig_upd_before_3 on parted2_stmt_trig BEFORE UPDATE for STATEMENT NOTICE: trigger trig_upd_after_child on parted_stmt_trig1 AFTER UPDATE for ROW NOTICE: trigger trig_upd_after_parent on parted_stmt_trig1 AFTER UPDATE for ROW NOTICE: trigger trig_upd_after_parent on parted_stmt_trig2 AFTER UPDATE for ROW NOTICE: trigger trig_upd_after on parted_stmt_trig AFTER UPDATE for STATEMENT NOTICE: trigger trig_upd_after_3 on parted2_stmt_trig AFTER UPDATE for STATEMENT delete from parted_stmt_trig; NOTICE: trigger trig_del_before on parted_stmt_trig BEFORE DELETE for STATEMENT NOTICE: trigger trig_del_before_child on parted_stmt_trig1 BEFORE DELETE for ROW NOTICE: trigger trig_del_after_parent on parted_stmt_trig2 AFTER DELETE for ROW NOTICE: trigger trig_del_after on parted_stmt_trig AFTER DELETE for STATEMENT -- insert via copy on the parent copy parted_stmt_trig(a) from stdin; NOTICE: trigger trig_ins_before on parted_stmt_trig BEFORE INSERT for STATEMENT NOTICE: trigger trig_ins_before_child on parted_stmt_trig1 BEFORE INSERT for ROW NOTICE: trigger trig_ins_after_child on parted_stmt_trig1 AFTER INSERT for ROW NOTICE: trigger trig_ins_after_parent on parted_stmt_trig1 AFTER INSERT for ROW NOTICE: trigger trig_ins_after_parent on parted_stmt_trig2 AFTER INSERT for ROW NOTICE: trigger trig_ins_after on parted_stmt_trig AFTER INSERT for STATEMENT -- insert via copy on the first partition copy parted_stmt_trig1(a) from stdin; NOTICE: trigger trig_ins_before_child on parted_stmt_trig1 BEFORE INSERT for ROW NOTICE: trigger trig_ins_after_child on parted_stmt_trig1 AFTER INSERT for ROW NOTICE: trigger trig_ins_after_parent on parted_stmt_trig1 AFTER INSERT for ROW -- Disabling a trigger in the parent table should disable children triggers too alter table parted_stmt_trig disable trigger trig_ins_after_parent; insert into parted_stmt_trig values (1); NOTICE: trigger trig_ins_before on parted_stmt_trig BEFORE INSERT for STATEMENT NOTICE: trigger trig_ins_before_child on parted_stmt_trig1 BEFORE INSERT for ROW NOTICE: trigger trig_ins_after_child on parted_stmt_trig1 AFTER INSERT for ROW NOTICE: trigger trig_ins_after on parted_stmt_trig AFTER INSERT for STATEMENT alter table parted_stmt_trig enable trigger trig_ins_after_parent; insert into parted_stmt_trig values (1); NOTICE: trigger trig_ins_before on parted_stmt_trig BEFORE INSERT for STATEMENT NOTICE: trigger trig_ins_before_child on parted_stmt_trig1 BEFORE INSERT for ROW NOTICE: trigger trig_ins_after_child on parted_stmt_trig1 AFTER INSERT for ROW NOTICE: trigger trig_ins_after_parent on parted_stmt_trig1 AFTER INSERT for ROW NOTICE: trigger trig_ins_after on parted_stmt_trig AFTER INSERT for STATEMENT drop table parted_stmt_trig, parted2_stmt_trig; -- Verify that triggers fire in alphabetical order create table parted_trig (a int) partition by range (a); create table parted_trig_1 partition of parted_trig for values from (0) to (1000) partition by range (a); create table parted_trig_1_1 partition of parted_trig_1 for values from (0) to (100); create table parted_trig_2 partition of parted_trig for values from (1000) to (2000); create trigger zzz after insert on parted_trig for each row execute procedure trigger_notice(); create trigger mmm after insert on parted_trig_1_1 for each row execute procedure trigger_notice(); create trigger aaa after insert on parted_trig_1 for each row execute procedure trigger_notice(); create trigger bbb after insert on parted_trig for each row execute procedure trigger_notice(); create trigger qqq after insert on parted_trig_1_1 for each row execute procedure trigger_notice(); insert into parted_trig values (50), (1500); NOTICE: trigger aaa on parted_trig_1_1 AFTER INSERT for ROW NOTICE: trigger bbb on parted_trig_1_1 AFTER INSERT for ROW NOTICE: trigger mmm on parted_trig_1_1 AFTER INSERT for ROW NOTICE: trigger qqq on parted_trig_1_1 AFTER INSERT for ROW NOTICE: trigger zzz on parted_trig_1_1 AFTER INSERT for ROW NOTICE: trigger bbb on parted_trig_2 AFTER INSERT for ROW NOTICE: trigger zzz on parted_trig_2 AFTER INSERT for ROW drop table parted_trig; -- Verify propagation of trigger arguments to partitions create table parted_trig (a int) partition by list (a); create table parted_trig1 partition of parted_trig for values in (1); create or replace function trigger_notice() returns trigger as $$ declare arg1 text = TG_ARGV[0]; arg2 integer = TG_ARGV[1]; begin raise notice 'trigger % on % % % for % args % %', TG_NAME, TG_TABLE_NAME, TG_WHEN, TG_OP, TG_LEVEL, arg1, arg2; return null; end; $$ language plpgsql; create trigger aaa after insert on parted_trig for each row execute procedure trigger_notice('quirky', 1); -- Verify propagation of trigger arguments to partitions attached after creating trigger create table parted_trig2 partition of parted_trig for values in (2); create table parted_trig3 (like parted_trig); alter table parted_trig attach partition parted_trig3 for values in (3); insert into parted_trig values (1), (2), (3); NOTICE: trigger aaa on parted_trig1 AFTER INSERT for ROW args quirky 1 NOTICE: trigger aaa on parted_trig2 AFTER INSERT for ROW args quirky 1 NOTICE: trigger aaa on parted_trig3 AFTER INSERT for ROW args quirky 1 drop table parted_trig; -- test irregular partitions (i.e., different column definitions), -- including that the WHEN clause works create function bark(text) returns bool language plpgsql immutable as $$ begin raise notice '% <- woof!', $1; return true; end; $$; create or replace function trigger_notice_ab() returns trigger as $$ begin raise notice 'trigger % on % % % for %: (a,b)=(%,%)', TG_NAME, TG_TABLE_NAME, TG_WHEN, TG_OP, TG_LEVEL, NEW.a, NEW.b; if TG_LEVEL = 'ROW' then return NEW; end if; return null; end; $$ language plpgsql; create table parted_irreg_ancestor (fd text, b text, fd2 int, fd3 int, a int) partition by range (b); alter table parted_irreg_ancestor drop column fd, drop column fd2, drop column fd3; create table parted_irreg (fd int, a int, fd2 int, b text) partition by range (b); alter table parted_irreg drop column fd, drop column fd2; alter table parted_irreg_ancestor attach partition parted_irreg for values from ('aaaa') to ('zzzz'); create table parted1_irreg (b text, fd int, a int); alter table parted1_irreg drop column fd; alter table parted_irreg attach partition parted1_irreg for values from ('aaaa') to ('bbbb'); create trigger parted_trig after insert on parted_irreg for each row execute procedure trigger_notice_ab(); create trigger parted_trig_odd after insert on parted_irreg for each row when (bark(new.b) AND new.a % 2 = 1) execute procedure trigger_notice_ab(); -- we should hear barking for every insert, but parted_trig_odd only emits -- noise for odd values of a. parted_trig does it for all inserts. insert into parted_irreg values (1, 'aardvark'), (2, 'aanimals'); NOTICE: aardvark <- woof! NOTICE: aanimals <- woof! NOTICE: trigger parted_trig on parted1_irreg AFTER INSERT for ROW: (a,b)=(1,aardvark) NOTICE: trigger parted_trig_odd on parted1_irreg AFTER INSERT for ROW: (a,b)=(1,aardvark) NOTICE: trigger parted_trig on parted1_irreg AFTER INSERT for ROW: (a,b)=(2,aanimals) insert into parted1_irreg values ('aardwolf', 2); NOTICE: aardwolf <- woof! NOTICE: trigger parted_trig on parted1_irreg AFTER INSERT for ROW: (a,b)=(2,aardwolf) insert into parted_irreg_ancestor values ('aasvogel', 3); NOTICE: aasvogel <- woof! NOTICE: trigger parted_trig on parted1_irreg AFTER INSERT for ROW: (a,b)=(3,aasvogel) NOTICE: trigger parted_trig_odd on parted1_irreg AFTER INSERT for ROW: (a,b)=(3,aasvogel) drop table parted_irreg_ancestor; -- Before triggers and partitions create table parted (a int, b int, c text) partition by list (a); create table parted_1 partition of parted for values in (1) partition by list (b); create table parted_1_1 partition of parted_1 for values in (1); create function parted_trigfunc() returns trigger language plpgsql as $$ begin new.a = new.a + 1; return new; end; $$; insert into parted values (1, 1, 'uno uno v1'); -- works create trigger t before insert or update or delete on parted for each row execute function parted_trigfunc(); insert into parted values (1, 1, 'uno uno v2'); -- fail ERROR: moving row to another partition during a BEFORE FOR EACH ROW trigger is not supported DETAIL: Before executing trigger "t", the row was to be in partition "public.parted_1_1". update parted set c = c || 'v3'; -- fail ERROR: no partition of relation "parted" found for row DETAIL: Partition key of the failing row contains (a) = (2). create or replace function parted_trigfunc() returns trigger language plpgsql as $$ begin new.b = new.b + 1; return new; end; $$; insert into parted values (1, 1, 'uno uno v4'); -- fail ERROR: moving row to another partition during a BEFORE FOR EACH ROW trigger is not supported DETAIL: Before executing trigger "t", the row was to be in partition "public.parted_1_1". update parted set c = c || 'v5'; -- fail ERROR: no partition of relation "parted_1" found for row DETAIL: Partition key of the failing row contains (b) = (2). create or replace function parted_trigfunc() returns trigger language plpgsql as $$ begin new.c = new.c || ' did '|| TG_OP; return new; end; $$; insert into parted values (1, 1, 'uno uno'); -- works update parted set c = c || ' v6'; -- works select tableoid::regclass, * from parted; tableoid | a | b | c ------------+---+---+---------------------------------- parted_1_1 | 1 | 1 | uno uno v1 v6 did UPDATE parted_1_1 | 1 | 1 | uno uno did INSERT v6 did UPDATE (2 rows) -- update itself moves tuple to new partition; trigger still works truncate table parted; create table parted_2 partition of parted for values in (2); insert into parted values (1, 1, 'uno uno v5'); update parted set a = 2; select tableoid::regclass, * from parted; tableoid | a | b | c ----------+---+---+--------------------------------------------- parted_2 | 2 | 1 | uno uno v5 did INSERT did UPDATE did INSERT (1 row) -- both trigger and update change the partition create or replace function parted_trigfunc2() returns trigger language plpgsql as $$ begin new.a = new.a + 1; return new; end; $$; create trigger t2 before update on parted for each row execute function parted_trigfunc2(); truncate table parted; insert into parted values (1, 1, 'uno uno v6'); create table parted_3 partition of parted for values in (3); update parted set a = a + 1; select tableoid::regclass, * from parted; tableoid | a | b | c ----------+---+---+--------------------------------------------- parted_3 | 3 | 1 | uno uno v6 did INSERT did UPDATE did INSERT (1 row) -- there's no partition for a=0, but this update works anyway because -- the trigger causes the tuple to be routed to another partition update parted set a = 0; select tableoid::regclass, * from parted; tableoid | a | b | c ------------+---+---+------------------------------------------------------------------- parted_1_1 | 1 | 1 | uno uno v6 did INSERT did UPDATE did INSERT did UPDATE did INSERT (1 row) drop table parted; create table parted (a int, b int, c text) partition by list ((a + b)); create or replace function parted_trigfunc() returns trigger language plpgsql as $$ begin new.a = new.a + new.b; return new; end; $$; create table parted_1 partition of parted for values in (1, 2); create table parted_2 partition of parted for values in (3, 4); create trigger t before insert or update on parted for each row execute function parted_trigfunc(); insert into parted values (0, 1, 'zero win'); insert into parted values (1, 1, 'one fail'); ERROR: moving row to another partition during a BEFORE FOR EACH ROW trigger is not supported DETAIL: Before executing trigger "t", the row was to be in partition "public.parted_1". insert into parted values (1, 2, 'two fail'); ERROR: moving row to another partition during a BEFORE FOR EACH ROW trigger is not supported DETAIL: Before executing trigger "t", the row was to be in partition "public.parted_2". select * from parted; a | b | c ---+---+---------- 1 | 1 | zero win (1 row) drop table parted; drop function parted_trigfunc(); -- -- Constraint triggers and partitioned tables create table parted_constr_ancestor (a int, b text) partition by range (b); create table parted_constr (a int, b text) partition by range (b); alter table parted_constr_ancestor attach partition parted_constr for values from ('aaaa') to ('zzzz'); create table parted1_constr (a int, b text); alter table parted_constr attach partition parted1_constr for values from ('aaaa') to ('bbbb'); create constraint trigger parted_trig after insert on parted_constr_ancestor deferrable for each row execute procedure trigger_notice_ab(); create constraint trigger parted_trig_two after insert on parted_constr deferrable initially deferred for each row when (bark(new.b) AND new.a % 2 = 1) execute procedure trigger_notice_ab(); -- The immediate constraint is fired immediately; the WHEN clause of the -- deferred constraint is also called immediately. The deferred constraint -- is fired at commit time. begin; insert into parted_constr values (1, 'aardvark'); NOTICE: aardvark <- woof! NOTICE: trigger parted_trig on parted1_constr AFTER INSERT for ROW: (a,b)=(1,aardvark) insert into parted1_constr values (2, 'aardwolf'); NOTICE: aardwolf <- woof! NOTICE: trigger parted_trig on parted1_constr AFTER INSERT for ROW: (a,b)=(2,aardwolf) insert into parted_constr_ancestor values (3, 'aasvogel'); NOTICE: aasvogel <- woof! NOTICE: trigger parted_trig on parted1_constr AFTER INSERT for ROW: (a,b)=(3,aasvogel) commit; NOTICE: trigger parted_trig_two on parted1_constr AFTER INSERT for ROW: (a,b)=(1,aardvark) NOTICE: trigger parted_trig_two on parted1_constr AFTER INSERT for ROW: (a,b)=(3,aasvogel) -- The WHEN clause is immediate, and both constraint triggers are fired at -- commit time. begin; set constraints parted_trig deferred; insert into parted_constr values (1, 'aardvark'); NOTICE: aardvark <- woof! insert into parted1_constr values (2, 'aardwolf'), (3, 'aasvogel'); NOTICE: aardwolf <- woof! NOTICE: aasvogel <- woof! commit; NOTICE: trigger parted_trig on parted1_constr AFTER INSERT for ROW: (a,b)=(1,aardvark) NOTICE: trigger parted_trig_two on parted1_constr AFTER INSERT for ROW: (a,b)=(1,aardvark) NOTICE: trigger parted_trig on parted1_constr AFTER INSERT for ROW: (a,b)=(2,aardwolf) NOTICE: trigger parted_trig on parted1_constr AFTER INSERT for ROW: (a,b)=(3,aasvogel) NOTICE: trigger parted_trig_two on parted1_constr AFTER INSERT for ROW: (a,b)=(3,aasvogel) drop table parted_constr_ancestor; drop function bark(text); -- Test that the WHEN clause is set properly to partitions create table parted_trigger (a int, b text) partition by range (a); create table parted_trigger_1 partition of parted_trigger for values from (0) to (1000); create table parted_trigger_2 (drp int, a int, b text); alter table parted_trigger_2 drop column drp; alter table parted_trigger attach partition parted_trigger_2 for values from (1000) to (2000); create trigger parted_trigger after update on parted_trigger for each row when (new.a % 2 = 1 and length(old.b) >= 2) execute procedure trigger_notice_ab(); create table parted_trigger_3 (b text, a int) partition by range (length(b)); create table parted_trigger_3_1 partition of parted_trigger_3 for values from (1) to (3); create table parted_trigger_3_2 partition of parted_trigger_3 for values from (3) to (5); alter table parted_trigger attach partition parted_trigger_3 for values from (2000) to (3000); insert into parted_trigger values (0, 'a'), (1, 'bbb'), (2, 'bcd'), (3, 'c'), (1000, 'c'), (1001, 'ddd'), (1002, 'efg'), (1003, 'f'), (2000, 'e'), (2001, 'fff'), (2002, 'ghi'), (2003, 'h'); update parted_trigger set a = a + 2; -- notice for odd 'a' values, long 'b' values NOTICE: trigger parted_trigger on parted_trigger_1 AFTER UPDATE for ROW: (a,b)=(3,bbb) NOTICE: trigger parted_trigger on parted_trigger_2 AFTER UPDATE for ROW: (a,b)=(1003,ddd) NOTICE: trigger parted_trigger on parted_trigger_3_2 AFTER UPDATE for ROW: (a,b)=(2003,fff) drop table parted_trigger; -- try a constraint trigger, also create table parted_referenced (a int); create table unparted_trigger (a int, b text); -- for comparison purposes create table parted_trigger (a int, b text) partition by range (a); create table parted_trigger_1 partition of parted_trigger for values from (0) to (1000); create table parted_trigger_2 (drp int, a int, b text); alter table parted_trigger_2 drop column drp; alter table parted_trigger attach partition parted_trigger_2 for values from (1000) to (2000); create constraint trigger parted_trigger after update on parted_trigger from parted_referenced for each row execute procedure trigger_notice_ab(); create constraint trigger parted_trigger after update on unparted_trigger from parted_referenced for each row execute procedure trigger_notice_ab(); create table parted_trigger_3 (b text, a int) partition by range (length(b)); create table parted_trigger_3_1 partition of parted_trigger_3 for values from (1) to (3); create table parted_trigger_3_2 partition of parted_trigger_3 for values from (3) to (5); alter table parted_trigger attach partition parted_trigger_3 for values from (2000) to (3000); select tgname, conname, t.tgrelid::regclass, t.tgconstrrelid::regclass, c.conrelid::regclass, c.confrelid::regclass from pg_trigger t join pg_constraint c on (t.tgconstraint = c.oid) where tgname = 'parted_trigger' order by t.tgrelid::regclass::text; tgname | conname | tgrelid | tgconstrrelid | conrelid | confrelid ----------------+----------------+--------------------+-------------------+--------------------+----------- parted_trigger | parted_trigger | parted_trigger | parted_referenced | parted_trigger | - parted_trigger | parted_trigger | parted_trigger_1 | parted_referenced | parted_trigger_1 | - parted_trigger | parted_trigger | parted_trigger_2 | parted_referenced | parted_trigger_2 | - parted_trigger | parted_trigger | parted_trigger_3 | parted_referenced | parted_trigger_3 | - parted_trigger | parted_trigger | parted_trigger_3_1 | parted_referenced | parted_trigger_3_1 | - parted_trigger | parted_trigger | parted_trigger_3_2 | parted_referenced | parted_trigger_3_2 | - parted_trigger | parted_trigger | unparted_trigger | parted_referenced | unparted_trigger | - (7 rows) drop table parted_referenced, parted_trigger, unparted_trigger; -- verify that the "AFTER UPDATE OF columns" event is propagated correctly create table parted_trigger (a int, b text) partition by range (a); create table parted_trigger_1 partition of parted_trigger for values from (0) to (1000); create table parted_trigger_2 (drp int, a int, b text); alter table parted_trigger_2 drop column drp; alter table parted_trigger attach partition parted_trigger_2 for values from (1000) to (2000); create trigger parted_trigger after update of b on parted_trigger for each row execute procedure trigger_notice_ab(); create table parted_trigger_3 (b text, a int) partition by range (length(b)); create table parted_trigger_3_1 partition of parted_trigger_3 for values from (1) to (4); create table parted_trigger_3_2 partition of parted_trigger_3 for values from (4) to (8); alter table parted_trigger attach partition parted_trigger_3 for values from (2000) to (3000); insert into parted_trigger values (0, 'a'), (1000, 'c'), (2000, 'e'), (2001, 'eeee'); update parted_trigger set a = a + 2; -- no notices here update parted_trigger set b = b || 'b'; -- all triggers should fire NOTICE: trigger parted_trigger on parted_trigger_1 AFTER UPDATE for ROW: (a,b)=(2,ab) NOTICE: trigger parted_trigger on parted_trigger_2 AFTER UPDATE for ROW: (a,b)=(1002,cb) NOTICE: trigger parted_trigger on parted_trigger_3_1 AFTER UPDATE for ROW: (a,b)=(2002,eb) NOTICE: trigger parted_trigger on parted_trigger_3_2 AFTER UPDATE for ROW: (a,b)=(2003,eeeeb) drop table parted_trigger; drop function trigger_notice_ab(); -- Make sure we don't end up with unnecessary copies of triggers, when -- cloning them. create table trg_clone (a int) partition by range (a); create table trg_clone1 partition of trg_clone for values from (0) to (1000); alter table trg_clone add constraint uniq unique (a) deferrable; create table trg_clone2 partition of trg_clone for values from (1000) to (2000); create table trg_clone3 partition of trg_clone for values from (2000) to (3000) partition by range (a); create table trg_clone_3_3 partition of trg_clone3 for values from (2000) to (2100); select tgrelid::regclass, count(*) from pg_trigger where tgrelid::regclass in ('trg_clone', 'trg_clone1', 'trg_clone2', 'trg_clone3', 'trg_clone_3_3') group by tgrelid::regclass order by tgrelid::regclass; tgrelid | count ---------------+------- trg_clone | 1 trg_clone1 | 1 trg_clone2 | 1 trg_clone3 | 1 trg_clone_3_3 | 1 (5 rows) drop table trg_clone; -- Test the interaction between ALTER TABLE .. DISABLE TRIGGER and -- both kinds of inheritance. Historically, legacy inheritance has -- not recursed to children, so that behavior is preserved. create table parent (a int); create table child1 () inherits (parent); create function trig_nothing() returns trigger language plpgsql as $$ begin return null; end $$; create trigger tg after insert on parent for each row execute function trig_nothing(); create trigger tg after insert on child1 for each row execute function trig_nothing(); alter table parent disable trigger tg; select tgrelid::regclass, tgname, tgenabled from pg_trigger where tgrelid in ('parent'::regclass, 'child1'::regclass) order by tgrelid::regclass::text; tgrelid | tgname | tgenabled ---------+--------+----------- child1 | tg | O parent | tg | D (2 rows) alter table only parent enable always trigger tg; select tgrelid::regclass, tgname, tgenabled from pg_trigger where tgrelid in ('parent'::regclass, 'child1'::regclass) order by tgrelid::regclass::text; tgrelid | tgname | tgenabled ---------+--------+----------- child1 | tg | O parent | tg | A (2 rows) drop table parent, child1; create table parent (a int) partition by list (a); create table child1 partition of parent for values in (1); create trigger tg after insert on parent for each row execute procedure trig_nothing(); select tgrelid::regclass, tgname, tgenabled from pg_trigger where tgrelid in ('parent'::regclass, 'child1'::regclass) order by tgrelid::regclass::text; tgrelid | tgname | tgenabled ---------+--------+----------- child1 | tg | O parent | tg | O (2 rows) alter table only parent enable always trigger tg; select tgrelid::regclass, tgname, tgenabled from pg_trigger where tgrelid in ('parent'::regclass, 'child1'::regclass) order by tgrelid::regclass::text; tgrelid | tgname | tgenabled ---------+--------+----------- child1 | tg | O parent | tg | A (2 rows) drop table parent, child1; -- Verify that firing state propagates correctly on creation, too CREATE TABLE trgfire (i int) PARTITION BY RANGE (i); CREATE TABLE trgfire1 PARTITION OF trgfire FOR VALUES FROM (1) TO (10); CREATE OR REPLACE FUNCTION tgf() RETURNS trigger LANGUAGE plpgsql AS $$ begin raise exception 'except'; end $$; CREATE TRIGGER tg AFTER INSERT ON trgfire FOR EACH ROW EXECUTE FUNCTION tgf(); INSERT INTO trgfire VALUES (1); ERROR: except CONTEXT: PL/pgSQL function tgf() line 1 at RAISE ALTER TABLE trgfire DISABLE TRIGGER tg; INSERT INTO trgfire VALUES (1); CREATE TABLE trgfire2 PARTITION OF trgfire FOR VALUES FROM (10) TO (20); INSERT INTO trgfire VALUES (11); CREATE TABLE trgfire3 (LIKE trgfire); ALTER TABLE trgfire ATTACH PARTITION trgfire3 FOR VALUES FROM (20) TO (30); INSERT INTO trgfire VALUES (21); CREATE TABLE trgfire4 PARTITION OF trgfire FOR VALUES FROM (30) TO (40) PARTITION BY LIST (i); CREATE TABLE trgfire4_30 PARTITION OF trgfire4 FOR VALUES IN (30); INSERT INTO trgfire VALUES (30); CREATE TABLE trgfire5 (LIKE trgfire) PARTITION BY LIST (i); CREATE TABLE trgfire5_40 PARTITION OF trgfire5 FOR VALUES IN (40); ALTER TABLE trgfire ATTACH PARTITION trgfire5 FOR VALUES FROM (40) TO (50); INSERT INTO trgfire VALUES (40); SELECT tgrelid::regclass, tgenabled FROM pg_trigger WHERE tgrelid::regclass IN (SELECT oid from pg_class where relname LIKE 'trgfire%') ORDER BY tgrelid::regclass::text; tgrelid | tgenabled -------------+----------- trgfire | D trgfire1 | D trgfire2 | D trgfire3 | D trgfire4 | D trgfire4_30 | D trgfire5 | D trgfire5_40 | D (8 rows) ALTER TABLE trgfire ENABLE TRIGGER tg; INSERT INTO trgfire VALUES (1); ERROR: except CONTEXT: PL/pgSQL function tgf() line 1 at RAISE INSERT INTO trgfire VALUES (11); ERROR: except CONTEXT: PL/pgSQL function tgf() line 1 at RAISE INSERT INTO trgfire VALUES (21); ERROR: except CONTEXT: PL/pgSQL function tgf() line 1 at RAISE INSERT INTO trgfire VALUES (30); ERROR: except CONTEXT: PL/pgSQL function tgf() line 1 at RAISE INSERT INTO trgfire VALUES (40); ERROR: except CONTEXT: PL/pgSQL function tgf() line 1 at RAISE DROP TABLE trgfire; DROP FUNCTION tgf(); -- -- Test the interaction between transition tables and both kinds of -- inheritance. We'll dump the contents of the transition tables in a -- format that shows the attribute order, so that we can distinguish -- tuple formats (though not dropped attributes). -- create or replace function dump_insert() returns trigger language plpgsql as $$ begin raise notice 'trigger = %, new table = %', TG_NAME, (select string_agg(new_table::text, ', ' order by a) from new_table); return null; end; $$; create or replace function dump_update() returns trigger language plpgsql as $$ begin raise notice 'trigger = %, old table = %, new table = %', TG_NAME, (select string_agg(old_table::text, ', ' order by a) from old_table), (select string_agg(new_table::text, ', ' order by a) from new_table); return null; end; $$; create or replace function dump_delete() returns trigger language plpgsql as $$ begin raise notice 'trigger = %, old table = %', TG_NAME, (select string_agg(old_table::text, ', ' order by a) from old_table); return null; end; $$; -- -- Verify behavior of statement triggers on partition hierarchy with -- transition tables. Tuples should appear to each trigger in the -- format of the relation the trigger is attached to. -- -- set up a partition hierarchy with some different TupleDescriptors create table parent (a text, b int) partition by list (a); -- a child matching parent create table child1 partition of parent for values in ('AAA'); -- a child with a dropped column create table child2 (x int, a text, b int); alter table child2 drop column x; alter table parent attach partition child2 for values in ('BBB'); -- a child with a different column order create table child3 (b int, a text); alter table parent attach partition child3 for values in ('CCC'); create trigger parent_insert_trig after insert on parent referencing new table as new_table for each statement execute procedure dump_insert(); create trigger parent_update_trig after update on parent referencing old table as old_table new table as new_table for each statement execute procedure dump_update(); create trigger parent_delete_trig after delete on parent referencing old table as old_table for each statement execute procedure dump_delete(); create trigger child1_insert_trig after insert on child1 referencing new table as new_table for each statement execute procedure dump_insert(); create trigger child1_update_trig after update on child1 referencing old table as old_table new table as new_table for each statement execute procedure dump_update(); create trigger child1_delete_trig after delete on child1 referencing old table as old_table for each statement execute procedure dump_delete(); create trigger child2_insert_trig after insert on child2 referencing new table as new_table for each statement execute procedure dump_insert(); create trigger child2_update_trig after update on child2 referencing old table as old_table new table as new_table for each statement execute procedure dump_update(); create trigger child2_delete_trig after delete on child2 referencing old table as old_table for each statement execute procedure dump_delete(); create trigger child3_insert_trig after insert on child3 referencing new table as new_table for each statement execute procedure dump_insert(); create trigger child3_update_trig after update on child3 referencing old table as old_table new table as new_table for each statement execute procedure dump_update(); create trigger child3_delete_trig after delete on child3 referencing old table as old_table for each statement execute procedure dump_delete(); SELECT trigger_name, event_manipulation, event_object_schema, event_object_table, action_order, action_condition, action_orientation, action_timing, action_reference_old_table, action_reference_new_table FROM information_schema.triggers WHERE event_object_table IN ('parent', 'child1', 'child2', 'child3') ORDER BY trigger_name COLLATE "C", 2; trigger_name | event_manipulation | event_object_schema | event_object_table | action_order | action_condition | action_orientation | action_timing | action_reference_old_table | action_reference_new_table --------------------+--------------------+---------------------+--------------------+--------------+------------------+--------------------+---------------+----------------------------+---------------------------- child1_delete_trig | DELETE | public | child1 | 1 | | STATEMENT | AFTER | old_table | child1_insert_trig | INSERT | public | child1 | 1 | | STATEMENT | AFTER | | new_table child1_update_trig | UPDATE | public | child1 | 1 | | STATEMENT | AFTER | old_table | new_table child2_delete_trig | DELETE | public | child2 | 1 | | STATEMENT | AFTER | old_table | child2_insert_trig | INSERT | public | child2 | 1 | | STATEMENT | AFTER | | new_table child2_update_trig | UPDATE | public | child2 | 1 | | STATEMENT | AFTER | old_table | new_table child3_delete_trig | DELETE | public | child3 | 1 | | STATEMENT | AFTER | old_table | child3_insert_trig | INSERT | public | child3 | 1 | | STATEMENT | AFTER | | new_table child3_update_trig | UPDATE | public | child3 | 1 | | STATEMENT | AFTER | old_table | new_table parent_delete_trig | DELETE | public | parent | 1 | | STATEMENT | AFTER | old_table | parent_insert_trig | INSERT | public | parent | 1 | | STATEMENT | AFTER | | new_table parent_update_trig | UPDATE | public | parent | 1 | | STATEMENT | AFTER | old_table | new_table (12 rows) -- insert directly into children sees respective child-format tuples insert into child1 values ('AAA', 42); NOTICE: trigger = child1_insert_trig, new table = (AAA,42) insert into child2 values ('BBB', 42); NOTICE: trigger = child2_insert_trig, new table = (BBB,42) insert into child3 values (42, 'CCC'); NOTICE: trigger = child3_insert_trig, new table = (42,CCC) -- update via parent sees parent-format tuples update parent set b = b + 1; NOTICE: trigger = parent_update_trig, old table = (AAA,42), (BBB,42), (CCC,42), new table = (AAA,43), (BBB,43), (CCC,43) -- delete via parent sees parent-format tuples delete from parent; NOTICE: trigger = parent_delete_trig, old table = (AAA,43), (BBB,43), (CCC,43) -- insert into parent sees parent-format tuples insert into parent values ('AAA', 42); NOTICE: trigger = parent_insert_trig, new table = (AAA,42) insert into parent values ('BBB', 42); NOTICE: trigger = parent_insert_trig, new table = (BBB,42) insert into parent values ('CCC', 42); NOTICE: trigger = parent_insert_trig, new table = (CCC,42) -- delete from children sees respective child-format tuples delete from child1; NOTICE: trigger = child1_delete_trig, old table = (AAA,42) delete from child2; NOTICE: trigger = child2_delete_trig, old table = (BBB,42) delete from child3; NOTICE: trigger = child3_delete_trig, old table = (42,CCC) -- copy into parent sees parent-format tuples copy parent (a, b) from stdin; NOTICE: trigger = parent_insert_trig, new table = (AAA,42), (BBB,42), (CCC,42) -- DML affecting parent sees tuples collected from children even if -- there is no transition table trigger on the children drop trigger child1_insert_trig on child1; drop trigger child1_update_trig on child1; drop trigger child1_delete_trig on child1; drop trigger child2_insert_trig on child2; drop trigger child2_update_trig on child2; drop trigger child2_delete_trig on child2; drop trigger child3_insert_trig on child3; drop trigger child3_update_trig on child3; drop trigger child3_delete_trig on child3; delete from parent; NOTICE: trigger = parent_delete_trig, old table = (AAA,42), (BBB,42), (CCC,42) -- copy into parent sees tuples collected from children even if there -- is no transition-table trigger on the children copy parent (a, b) from stdin; NOTICE: trigger = parent_insert_trig, new table = (AAA,42), (BBB,42), (CCC,42) -- insert into parent with a before trigger on a child tuple before -- insertion, and we capture the newly modified row in parent format create or replace function intercept_insert() returns trigger language plpgsql as $$ begin new.b = new.b + 1000; return new; end; $$; create trigger intercept_insert_child3 before insert on child3 for each row execute procedure intercept_insert(); -- insert, parent trigger sees post-modification parent-format tuple insert into parent values ('AAA', 42), ('BBB', 42), ('CCC', 66); NOTICE: trigger = parent_insert_trig, new table = (AAA,42), (BBB,42), (CCC,1066) -- copy, parent trigger sees post-modification parent-format tuple copy parent (a, b) from stdin; NOTICE: trigger = parent_insert_trig, new table = (AAA,42), (BBB,42), (CCC,1234) drop table child1, child2, child3, parent; drop function intercept_insert(); -- -- Verify prohibition of row triggers with transition triggers on -- partitions -- create table parent (a text, b int) partition by list (a); create table child partition of parent for values in ('AAA'); -- adding row trigger with transition table fails create trigger child_row_trig after insert on child referencing new table as new_table for each row execute procedure dump_insert(); ERROR: ROW triggers with transition tables are not supported on partitions -- detaching it first works alter table parent detach partition child; create trigger child_row_trig after insert on child referencing new table as new_table for each row execute procedure dump_insert(); -- but now we're not allowed to reattach it alter table parent attach partition child for values in ('AAA'); ERROR: trigger "child_row_trig" prevents table "child" from becoming a partition DETAIL: ROW triggers with transition tables are not supported on partitions -- drop the trigger, and now we're allowed to attach it again drop trigger child_row_trig on child; alter table parent attach partition child for values in ('AAA'); drop table child, parent; -- -- Verify behavior of statement triggers on (non-partition) -- inheritance hierarchy with transition tables; similar to the -- partition case, except there is no rerouting on insertion and child -- tables can have extra columns -- -- set up inheritance hierarchy with different TupleDescriptors create table parent (a text, b int); -- a child matching parent create table child1 () inherits (parent); -- a child with a different column order create table child2 (b int, a text); alter table child2 inherit parent; -- a child with an extra column create table child3 (c text) inherits (parent); create trigger parent_insert_trig after insert on parent referencing new table as new_table for each statement execute procedure dump_insert(); create trigger parent_update_trig after update on parent referencing old table as old_table new table as new_table for each statement execute procedure dump_update(); create trigger parent_delete_trig after delete on parent referencing old table as old_table for each statement execute procedure dump_delete(); create trigger child1_insert_trig after insert on child1 referencing new table as new_table for each statement execute procedure dump_insert(); create trigger child1_update_trig after update on child1 referencing old table as old_table new table as new_table for each statement execute procedure dump_update(); create trigger child1_delete_trig after delete on child1 referencing old table as old_table for each statement execute procedure dump_delete(); create trigger child2_insert_trig after insert on child2 referencing new table as new_table for each statement execute procedure dump_insert(); create trigger child2_update_trig after update on child2 referencing old table as old_table new table as new_table for each statement execute procedure dump_update(); create trigger child2_delete_trig after delete on child2 referencing old table as old_table for each statement execute procedure dump_delete(); create trigger child3_insert_trig after insert on child3 referencing new table as new_table for each statement execute procedure dump_insert(); create trigger child3_update_trig after update on child3 referencing old table as old_table new table as new_table for each statement execute procedure dump_update(); create trigger child3_delete_trig after delete on child3 referencing old table as old_table for each statement execute procedure dump_delete(); -- insert directly into children sees respective child-format tuples insert into child1 values ('AAA', 42); NOTICE: trigger = child1_insert_trig, new table = (AAA,42) insert into child2 values (42, 'BBB'); NOTICE: trigger = child2_insert_trig, new table = (42,BBB) insert into child3 values ('CCC', 42, 'foo'); NOTICE: trigger = child3_insert_trig, new table = (CCC,42,foo) -- update via parent sees parent-format tuples update parent set b = b + 1; NOTICE: trigger = parent_update_trig, old table = (AAA,42), (BBB,42), (CCC,42), new table = (AAA,43), (BBB,43), (CCC,43) -- delete via parent sees parent-format tuples delete from parent; NOTICE: trigger = parent_delete_trig, old table = (AAA,43), (BBB,43), (CCC,43) -- reinsert values into children for next test... insert into child1 values ('AAA', 42); NOTICE: trigger = child1_insert_trig, new table = (AAA,42) insert into child2 values (42, 'BBB'); NOTICE: trigger = child2_insert_trig, new table = (42,BBB) insert into child3 values ('CCC', 42, 'foo'); NOTICE: trigger = child3_insert_trig, new table = (CCC,42,foo) -- delete from children sees respective child-format tuples delete from child1; NOTICE: trigger = child1_delete_trig, old table = (AAA,42) delete from child2; NOTICE: trigger = child2_delete_trig, old table = (42,BBB) delete from child3; NOTICE: trigger = child3_delete_trig, old table = (CCC,42,foo) -- copy into parent sees parent-format tuples (no rerouting, so these -- are really inserted into the parent) copy parent (a, b) from stdin; NOTICE: trigger = parent_insert_trig, new table = (AAA,42), (BBB,42), (CCC,42) -- same behavior for copy if there is an index (interesting because rows are -- captured by a different code path in copy.c if there are indexes) create index on parent(b); copy parent (a, b) from stdin; NOTICE: trigger = parent_insert_trig, new table = (DDD,42) -- DML affecting parent sees tuples collected from children even if -- there is no transition table trigger on the children drop trigger child1_insert_trig on child1; drop trigger child1_update_trig on child1; drop trigger child1_delete_trig on child1; drop trigger child2_insert_trig on child2; drop trigger child2_update_trig on child2; drop trigger child2_delete_trig on child2; drop trigger child3_insert_trig on child3; drop trigger child3_update_trig on child3; drop trigger child3_delete_trig on child3; delete from parent; NOTICE: trigger = parent_delete_trig, old table = (AAA,42), (BBB,42), (CCC,42), (DDD,42) drop table child1, child2, child3, parent; -- -- Verify prohibition of row triggers with transition triggers on -- inheritance children -- create table parent (a text, b int); create table child () inherits (parent); -- adding row trigger with transition table fails create trigger child_row_trig after insert on child referencing new table as new_table for each row execute procedure dump_insert(); ERROR: ROW triggers with transition tables are not supported on inheritance children -- disinheriting it first works alter table child no inherit parent; create trigger child_row_trig after insert on child referencing new table as new_table for each row execute procedure dump_insert(); -- but now we're not allowed to make it inherit anymore alter table child inherit parent; ERROR: trigger "child_row_trig" prevents table "child" from becoming an inheritance child DETAIL: ROW triggers with transition tables are not supported in inheritance hierarchies. -- drop the trigger, and now we're allowed to make it inherit again drop trigger child_row_trig on child; alter table child inherit parent; drop table child, parent; -- -- Verify behavior of queries with wCTEs, where multiple transition -- tuplestores can be active at the same time because there are -- multiple DML statements that might fire triggers with transition -- tables -- create table table1 (a int); create table table2 (a text); create trigger table1_trig after insert on table1 referencing new table as new_table for each statement execute procedure dump_insert(); create trigger table2_trig after insert on table2 referencing new table as new_table for each statement execute procedure dump_insert(); with wcte as (insert into table1 values (42)) insert into table2 values ('hello world'); NOTICE: trigger = table2_trig, new table = ("hello world") NOTICE: trigger = table1_trig, new table = (42) with wcte as (insert into table1 values (43)) insert into table1 values (44); NOTICE: trigger = table1_trig, new table = (43), (44) select * from table1; a ---- 42 44 43 (3 rows) select * from table2; a ------------- hello world (1 row) drop table table1; drop table table2; -- -- Verify behavior of INSERT ... ON CONFLICT DO UPDATE ... with -- transition tables. -- create table my_table (a int primary key, b text); create trigger my_table_insert_trig after insert on my_table referencing new table as new_table for each statement execute procedure dump_insert(); create trigger my_table_update_trig after update on my_table referencing old table as old_table new table as new_table for each statement execute procedure dump_update(); -- inserts only insert into my_table values (1, 'AAA'), (2, 'BBB') on conflict (a) do update set b = my_table.b || ':' || excluded.b; NOTICE: trigger = my_table_update_trig, old table = , new table = NOTICE: trigger = my_table_insert_trig, new table = (1,AAA), (2,BBB) -- mixture of inserts and updates insert into my_table values (1, 'AAA'), (2, 'BBB'), (3, 'CCC'), (4, 'DDD') on conflict (a) do update set b = my_table.b || ':' || excluded.b; NOTICE: trigger = my_table_update_trig, old table = (1,AAA), (2,BBB), new table = (1,AAA:AAA), (2,BBB:BBB) NOTICE: trigger = my_table_insert_trig, new table = (3,CCC), (4,DDD) -- updates only insert into my_table values (3, 'CCC'), (4, 'DDD') on conflict (a) do update set b = my_table.b || ':' || excluded.b; NOTICE: trigger = my_table_update_trig, old table = (3,CCC), (4,DDD), new table = (3,CCC:CCC), (4,DDD:DDD) NOTICE: trigger = my_table_insert_trig, new table = -- -- now using a partitioned table -- create table iocdu_tt_parted (a int primary key, b text) partition by list (a); create table iocdu_tt_parted1 partition of iocdu_tt_parted for values in (1); create table iocdu_tt_parted2 partition of iocdu_tt_parted for values in (2); create table iocdu_tt_parted3 partition of iocdu_tt_parted for values in (3); create table iocdu_tt_parted4 partition of iocdu_tt_parted for values in (4); create trigger iocdu_tt_parted_insert_trig after insert on iocdu_tt_parted referencing new table as new_table for each statement execute procedure dump_insert(); create trigger iocdu_tt_parted_update_trig after update on iocdu_tt_parted referencing old table as old_table new table as new_table for each statement execute procedure dump_update(); -- inserts only insert into iocdu_tt_parted values (1, 'AAA'), (2, 'BBB') on conflict (a) do update set b = iocdu_tt_parted.b || ':' || excluded.b; NOTICE: trigger = iocdu_tt_parted_update_trig, old table = , new table = NOTICE: trigger = iocdu_tt_parted_insert_trig, new table = (1,AAA), (2,BBB) -- mixture of inserts and updates insert into iocdu_tt_parted values (1, 'AAA'), (2, 'BBB'), (3, 'CCC'), (4, 'DDD') on conflict (a) do update set b = iocdu_tt_parted.b || ':' || excluded.b; NOTICE: trigger = iocdu_tt_parted_update_trig, old table = (1,AAA), (2,BBB), new table = (1,AAA:AAA), (2,BBB:BBB) NOTICE: trigger = iocdu_tt_parted_insert_trig, new table = (3,CCC), (4,DDD) -- updates only insert into iocdu_tt_parted values (3, 'CCC'), (4, 'DDD') on conflict (a) do update set b = iocdu_tt_parted.b || ':' || excluded.b; NOTICE: trigger = iocdu_tt_parted_update_trig, old table = (3,CCC), (4,DDD), new table = (3,CCC:CCC), (4,DDD:DDD) NOTICE: trigger = iocdu_tt_parted_insert_trig, new table = drop table iocdu_tt_parted; -- -- Verify that you can't create a trigger with transition tables for -- more than one event. -- create trigger my_table_multievent_trig after insert or update on my_table referencing new table as new_table for each statement execute procedure dump_insert(); ERROR: transition tables cannot be specified for triggers with more than one event -- -- Verify that you can't create a trigger with transition tables with -- a column list. -- create trigger my_table_col_update_trig after update of b on my_table referencing new table as new_table for each statement execute procedure dump_insert(); ERROR: transition tables cannot be specified for triggers with column lists drop table my_table; -- -- Test firing of triggers with transition tables by foreign key cascades -- create table refd_table (a int primary key, b text); create table trig_table (a int, b text, foreign key (a) references refd_table on update cascade on delete cascade ); create trigger trig_table_before_trig before insert or update or delete on trig_table for each statement execute procedure trigger_func('trig_table'); create trigger trig_table_insert_trig after insert on trig_table referencing new table as new_table for each statement execute procedure dump_insert(); create trigger trig_table_update_trig after update on trig_table referencing old table as old_table new table as new_table for each statement execute procedure dump_update(); create trigger trig_table_delete_trig after delete on trig_table referencing old table as old_table for each statement execute procedure dump_delete(); insert into refd_table values (1, 'one'), (2, 'two'), (3, 'three'); insert into trig_table values (1, 'one a'), (1, 'one b'), (2, 'two a'), (2, 'two b'), (3, 'three a'), (3, 'three b'); NOTICE: trigger_func(trig_table) called: action = INSERT, when = BEFORE, level = STATEMENT NOTICE: trigger = trig_table_insert_trig, new table = (1,"one a"), (1,"one b"), (2,"two a"), (2,"two b"), (3,"three a"), (3,"three b") update refd_table set a = 11 where b = 'one'; NOTICE: trigger_func(trig_table) called: action = UPDATE, when = BEFORE, level = STATEMENT NOTICE: trigger = trig_table_update_trig, old table = (1,"one a"), (1,"one b"), new table = (11,"one a"), (11,"one b") select * from trig_table; a | b ----+--------- 2 | two a 2 | two b 3 | three a 3 | three b 11 | one a 11 | one b (6 rows) delete from refd_table where length(b) = 3; NOTICE: trigger_func(trig_table) called: action = DELETE, when = BEFORE, level = STATEMENT NOTICE: trigger = trig_table_delete_trig, old table = (2,"two a"), (2,"two b"), (11,"one a"), (11,"one b") select * from trig_table; a | b ---+--------- 3 | three a 3 | three b (2 rows) drop table refd_table, trig_table; -- -- self-referential FKs are even more fun -- create table self_ref (a int primary key, b int references self_ref(a) on delete cascade); create trigger self_ref_before_trig before delete on self_ref for each statement execute procedure trigger_func('self_ref'); create trigger self_ref_r_trig after delete on self_ref referencing old table as old_table for each row execute procedure dump_delete(); create trigger self_ref_s_trig after delete on self_ref referencing old table as old_table for each statement execute procedure dump_delete(); insert into self_ref values (1, null), (2, 1), (3, 2); delete from self_ref where a = 1; NOTICE: trigger_func(self_ref) called: action = DELETE, when = BEFORE, level = STATEMENT NOTICE: trigger = self_ref_r_trig, old table = (1,), (2,1) NOTICE: trigger_func(self_ref) called: action = DELETE, when = BEFORE, level = STATEMENT NOTICE: trigger = self_ref_r_trig, old table = (1,), (2,1) NOTICE: trigger = self_ref_s_trig, old table = (1,), (2,1) NOTICE: trigger = self_ref_r_trig, old table = (3,2) NOTICE: trigger = self_ref_s_trig, old table = (3,2) -- without AR trigger, cascaded deletes all end up in one transition table drop trigger self_ref_r_trig on self_ref; insert into self_ref values (1, null), (2, 1), (3, 2), (4, 3); delete from self_ref where a = 1; NOTICE: trigger_func(self_ref) called: action = DELETE, when = BEFORE, level = STATEMENT NOTICE: trigger = self_ref_s_trig, old table = (1,), (2,1), (3,2), (4,3) drop table self_ref; -- cleanup drop function dump_insert(); drop function dump_update(); drop function dump_delete(); -- Leave around some objects for other tests create table trigger_parted (a int primary key) partition by list (a); create function trigger_parted_trigfunc() returns trigger language plpgsql as $$ begin end; $$; create trigger aft_row after insert or update on trigger_parted for each row execute function trigger_parted_trigfunc(); create table trigger_parted_p1 partition of trigger_parted for values in (1) partition by list (a); create table trigger_parted_p1_1 partition of trigger_parted_p1 for values in (1); create table trigger_parted_p2 partition of trigger_parted for values in (2) partition by list (a); create table trigger_parted_p2_2 partition of trigger_parted_p2 for values in (2); alter table only trigger_parted_p2 disable trigger aft_row; alter table trigger_parted_p2_2 enable always trigger aft_row; -- verify transition table conversion slot's lifetime -- https://postgr.es/m/39a71864-b120-5a5c-8cc5-c632b6f16761@amazon.com create table convslot_test_parent (col1 text primary key); create table convslot_test_child (col1 text primary key, foreign key (col1) references convslot_test_parent(col1) on delete cascade on update cascade ); alter table convslot_test_child add column col2 text not null default 'tutu'; insert into convslot_test_parent(col1) values ('1'); insert into convslot_test_child(col1) values ('1'); insert into convslot_test_parent(col1) values ('3'); insert into convslot_test_child(col1) values ('3'); create or replace function trigger_function1() returns trigger language plpgsql AS $$ begin raise notice 'trigger = %, old_table = %', TG_NAME, (select string_agg(old_table::text, ', ' order by col1) from old_table); return null; end; $$; create or replace function trigger_function2() returns trigger language plpgsql AS $$ begin raise notice 'trigger = %, new table = %', TG_NAME, (select string_agg(new_table::text, ', ' order by col1) from new_table); return null; end; $$; create trigger but_trigger after update on convslot_test_child referencing new table as new_table for each statement execute function trigger_function2(); update convslot_test_parent set col1 = col1 || '1'; NOTICE: trigger = but_trigger, new table = (11,tutu), (31,tutu) create or replace function trigger_function3() returns trigger language plpgsql AS $$ begin raise notice 'trigger = %, old_table = %, new table = %', TG_NAME, (select string_agg(old_table::text, ', ' order by col1) from old_table), (select string_agg(new_table::text, ', ' order by col1) from new_table); return null; end; $$; create trigger but_trigger2 after update on convslot_test_child referencing old table as old_table new table as new_table for each statement execute function trigger_function3(); update convslot_test_parent set col1 = col1 || '1'; NOTICE: trigger = but_trigger, new table = (111,tutu), (311,tutu) NOTICE: trigger = but_trigger2, old_table = (11,tutu), (31,tutu), new table = (111,tutu), (311,tutu) create trigger bdt_trigger after delete on convslot_test_child referencing old table as old_table for each statement execute function trigger_function1(); delete from convslot_test_parent; NOTICE: trigger = bdt_trigger, old_table = (111,tutu), (311,tutu) drop table convslot_test_child, convslot_test_parent;