4 create table pkeys (pkey1 int4 not null, pkey2 text not null);
5 create table fkeys (fkey1 int4, fkey2 text, fkey3 int);
6 create table fkeys2 (fkey21 int4, fkey22 text, pkey23 int not null);
7 create index fkeys_i on fkeys (fkey1, fkey2);
8 create index fkeys2_i on fkeys2 (fkey21, fkey22);
9 create index fkeys2p_i on fkeys2 (pkey23);
10 insert into pkeys values (10, '1');
11 insert into pkeys values (20, '2');
12 insert into pkeys values (30, '3');
13 insert into pkeys values (40, '4');
14 insert into pkeys values (50, '5');
15 insert into pkeys values (60, '6');
16 create unique index pkeys_i on pkeys (pkey1, pkey2);
19 -- (fkey1, fkey2) --> pkeys (pkey1, pkey2)
20 -- (fkey3) --> fkeys2 (pkey23)
22 create trigger check_fkeys_pkey_exist
23 before insert or update on fkeys
26 check_primary_key ('fkey1', 'fkey2', 'pkeys', 'pkey1', 'pkey2');
27 create trigger check_fkeys_pkey2_exist
28 before insert or update on fkeys
30 execute procedure check_primary_key ('fkey3', 'fkeys2', 'pkey23');
33 -- (fkey21, fkey22) --> pkeys (pkey1, pkey2)
35 create trigger check_fkeys2_pkey_exist
36 before insert or update on fkeys2
39 check_primary_key ('fkey21', 'fkey22', 'pkeys', 'pkey1', 'pkey2');
41 COMMENT ON TRIGGER check_fkeys2_pkey_bad ON fkeys2 IS 'wrong';
42 ERROR: trigger "check_fkeys2_pkey_bad" for table "fkeys2" does not exist
43 COMMENT ON TRIGGER check_fkeys2_pkey_exist ON fkeys2 IS 'right';
44 COMMENT ON TRIGGER check_fkeys2_pkey_exist ON fkeys2 IS NULL;
47 -- ON DELETE/UPDATE (pkey1, pkey2) CASCADE:
48 -- fkeys (fkey1, fkey2) and fkeys2 (fkey21, fkey22)
50 create trigger check_pkeys_fkey_cascade
51 before delete or update on pkeys
54 check_foreign_key (2, 'cascade', 'pkey1', 'pkey2',
55 'fkeys', 'fkey1', 'fkey2', 'fkeys2', 'fkey21', 'fkey22');
58 -- ON DELETE/UPDATE (pkey23) RESTRICT:
61 create trigger check_fkeys2_fkey_restrict
62 before delete or update on fkeys2
64 execute procedure check_foreign_key (1, 'restrict', 'pkey23', 'fkeys', 'fkey3');
65 insert into fkeys2 values (10, '1', 1);
66 insert into fkeys2 values (30, '3', 2);
67 insert into fkeys2 values (40, '4', 5);
68 insert into fkeys2 values (50, '5', 3);
70 insert into fkeys2 values (70, '5', 3);
71 ERROR: tuple references non-existent key
72 DETAIL: Trigger "check_fkeys2_pkey_exist" found tuple referencing non-existent key in "pkeys".
73 insert into fkeys values (10, '1', 2);
74 insert into fkeys values (30, '3', 3);
75 insert into fkeys values (40, '4', 2);
76 insert into fkeys values (50, '5', 2);
78 insert into fkeys values (70, '5', 1);
79 ERROR: tuple references non-existent key
80 DETAIL: Trigger "check_fkeys_pkey_exist" found tuple referencing non-existent key in "pkeys".
82 insert into fkeys values (60, '6', 4);
83 ERROR: tuple references non-existent key
84 DETAIL: Trigger "check_fkeys_pkey2_exist" found tuple referencing non-existent key in "fkeys2".
85 delete from pkeys where pkey1 = 30 and pkey2 = '3';
86 NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted
87 ERROR: "check_fkeys2_fkey_restrict": tuple is referenced in "fkeys"
88 CONTEXT: SQL statement "delete from fkeys2 where fkey21 = $1 and fkey22 = $2 "
89 delete from pkeys where pkey1 = 40 and pkey2 = '4';
90 NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted
91 NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys2 are deleted
92 update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 50 and pkey2 = '5';
93 NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted
94 ERROR: "check_fkeys2_fkey_restrict": tuple is referenced in "fkeys"
95 CONTEXT: SQL statement "delete from fkeys2 where fkey21 = $1 and fkey22 = $2 "
96 update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 10 and pkey2 = '1';
97 NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted
98 NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys2 are deleted
102 -- -- I've disabled the funny_dup17 test because the new semantics
103 -- -- of AFTER ROW triggers, which get now fired at the end of a
104 -- -- query always, cause funny_dup17 to enter an endless loop.
108 -- create table dup17 (x int4);
110 -- create trigger dup17_before
111 -- before insert on dup17
117 -- insert into dup17 values (17);
118 -- select count(*) from dup17;
119 -- insert into dup17 values (17);
120 -- select count(*) from dup17;
122 -- drop trigger dup17_before on dup17;
124 -- create trigger dup17_after
125 -- after insert on dup17
130 -- insert into dup17 values (13);
131 -- select count(*) from dup17 where x = 13;
132 -- insert into dup17 values (13);
133 -- select count(*) from dup17 where x = 13;
136 create sequence ttdummy_seq increment 10 start 0 minvalue 0;
137 create table tttest (
141 price_off int4 default 999999
143 create trigger ttdummy
144 before delete or update on tttest
147 ttdummy (price_on, price_off);
148 create trigger ttserial
149 before insert or update on tttest
152 autoinc (price_on, ttdummy_seq);
153 insert into tttest values (1, 1, null);
154 insert into tttest values (2, 2, null);
155 insert into tttest values (3, 3, 0);
156 select * from tttest;
157 price_id | price_val | price_on | price_off
158 ----------+-----------+----------+-----------
164 delete from tttest where price_id = 2;
165 select * from tttest;
166 price_id | price_val | price_on | price_off
167 ----------+-----------+----------+-----------
174 -- get current prices
175 select * from tttest where price_off = 999999;
176 price_id | price_val | price_on | price_off
177 ----------+-----------+----------+-----------
182 -- change price for price_id == 3
183 update tttest set price_val = 30 where price_id = 3;
184 select * from tttest;
185 price_id | price_val | price_on | price_off
186 ----------+-----------+----------+-----------
193 -- now we want to change pric_id in ALL tuples
194 -- this gets us not what we need
195 update tttest set price_id = 5 where price_id = 3;
196 select * from tttest;
197 price_id | price_val | price_on | price_off
198 ----------+-----------+----------+-----------
206 -- restore data as before last update:
207 select set_ttdummy(0);
213 delete from tttest where price_id = 5;
214 update tttest set price_off = 999999 where price_val = 30;
215 select * from tttest;
216 price_id | price_val | price_on | price_off
217 ----------+-----------+----------+-----------
224 -- and try change price_id now!
225 update tttest set price_id = 5 where price_id = 3;
226 select * from tttest;
227 price_id | price_val | price_on | price_off
228 ----------+-----------+----------+-----------
235 -- isn't it what we need ?
236 select set_ttdummy(1);
242 -- we want to correct some "date"
243 update tttest set price_on = -1 where price_id = 1;
244 ERROR: ttdummy (tttest): you cannot change price_on and/or price_off columns (use set_ttdummy)
245 -- but this doesn't work
247 select set_ttdummy(0);
253 update tttest set price_on = -1 where price_id = 1;
254 select * from tttest;
255 price_id | price_val | price_on | price_off
256 ----------+-----------+----------+-----------
263 -- isn't it what we need ?
264 -- get price for price_id == 5 as it was @ "date" 35
265 select * from tttest where price_on <= 35 and price_off > 35 and price_id = 5;
266 price_id | price_val | price_on | price_off
267 ----------+-----------+----------+-----------
272 drop sequence ttdummy_seq;
274 -- tests for per-statement triggers
276 CREATE TABLE log_table (tstamp timestamp default timeofday()::timestamp);
277 CREATE TABLE main_table (a int, b int);
278 COPY main_table (a,b) FROM stdin;
279 CREATE FUNCTION trigger_func() RETURNS trigger LANGUAGE plpgsql AS '
281 RAISE NOTICE ''trigger_func(%) called: action = %, when = %, level = %'', TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL;
284 CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON main_table
285 FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('before_ins_stmt');
286 CREATE TRIGGER after_ins_stmt_trig AFTER INSERT ON main_table
287 FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('after_ins_stmt');
289 -- if neither 'FOR EACH ROW' nor 'FOR EACH STATEMENT' was specified,
290 -- CREATE TRIGGER should default to 'FOR EACH STATEMENT'
292 CREATE TRIGGER after_upd_stmt_trig AFTER UPDATE ON main_table
293 EXECUTE PROCEDURE trigger_func('after_upd_stmt');
294 CREATE TRIGGER after_upd_row_trig AFTER UPDATE ON main_table
295 FOR EACH ROW EXECUTE PROCEDURE trigger_func('after_upd_row');
296 INSERT INTO main_table DEFAULT VALUES;
297 NOTICE: trigger_func(before_ins_stmt) called: action = INSERT, when = BEFORE, level = STATEMENT
298 NOTICE: trigger_func(after_ins_stmt) called: action = INSERT, when = AFTER, level = STATEMENT
299 UPDATE main_table SET a = a + 1 WHERE b < 30;
300 NOTICE: trigger_func(after_upd_row) called: action = UPDATE, when = AFTER, level = ROW
301 NOTICE: trigger_func(after_upd_row) called: action = UPDATE, when = AFTER, level = ROW
302 NOTICE: trigger_func(after_upd_row) called: action = UPDATE, when = AFTER, level = ROW
303 NOTICE: trigger_func(after_upd_row) called: action = UPDATE, when = AFTER, level = ROW
304 NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
305 -- UPDATE that effects zero rows should still call per-statement trigger
306 UPDATE main_table SET a = a + 2 WHERE b > 100;
307 NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
308 -- COPY should fire per-row and per-statement INSERT triggers
309 COPY main_table (a, b) FROM stdin;
310 NOTICE: trigger_func(before_ins_stmt) called: action = INSERT, when = BEFORE, level = STATEMENT
311 NOTICE: trigger_func(after_ins_stmt) called: action = INSERT, when = AFTER, level = STATEMENT
312 SELECT * FROM main_table ORDER BY a, b;
326 -- test triggers with WHEN clause
328 CREATE TRIGGER modified_a BEFORE UPDATE OF a ON main_table
329 FOR EACH ROW WHEN (OLD.a <> NEW.a) EXECUTE PROCEDURE trigger_func('modified_a');
330 CREATE TRIGGER modified_any BEFORE UPDATE OF a ON main_table
331 FOR EACH ROW WHEN (OLD.* IS DISTINCT FROM NEW.*) EXECUTE PROCEDURE trigger_func('modified_any');
332 CREATE TRIGGER insert_a AFTER INSERT ON main_table
333 FOR EACH ROW WHEN (NEW.a = 123) EXECUTE PROCEDURE trigger_func('insert_a');
334 CREATE TRIGGER delete_a AFTER DELETE ON main_table
335 FOR EACH ROW WHEN (OLD.a = 123) EXECUTE PROCEDURE trigger_func('delete_a');
336 CREATE TRIGGER insert_when BEFORE INSERT ON main_table
337 FOR EACH STATEMENT WHEN (true) EXECUTE PROCEDURE trigger_func('insert_when');
338 CREATE TRIGGER delete_when AFTER DELETE ON main_table
339 FOR EACH STATEMENT WHEN (true) EXECUTE PROCEDURE trigger_func('delete_when');
340 INSERT INTO main_table (a) VALUES (123), (456);
341 NOTICE: trigger_func(before_ins_stmt) called: action = INSERT, when = BEFORE, level = STATEMENT
342 NOTICE: trigger_func(insert_when) called: action = INSERT, when = BEFORE, level = STATEMENT
343 NOTICE: trigger_func(insert_a) called: action = INSERT, when = AFTER, level = ROW
344 NOTICE: trigger_func(after_ins_stmt) called: action = INSERT, when = AFTER, level = STATEMENT
345 COPY main_table FROM stdin;
346 NOTICE: trigger_func(before_ins_stmt) called: action = INSERT, when = BEFORE, level = STATEMENT
347 NOTICE: trigger_func(insert_when) called: action = INSERT, when = BEFORE, level = STATEMENT
348 NOTICE: trigger_func(insert_a) called: action = INSERT, when = AFTER, level = ROW
349 NOTICE: trigger_func(after_ins_stmt) called: action = INSERT, when = AFTER, level = STATEMENT
350 DELETE FROM main_table WHERE a IN (123, 456);
351 NOTICE: trigger_func(delete_a) called: action = DELETE, when = AFTER, level = ROW
352 NOTICE: trigger_func(delete_a) called: action = DELETE, when = AFTER, level = ROW
353 NOTICE: trigger_func(delete_when) called: action = DELETE, when = AFTER, level = STATEMENT
354 UPDATE main_table SET a = 50, b = 60;
355 NOTICE: trigger_func(modified_any) called: action = UPDATE, when = BEFORE, level = ROW
356 NOTICE: trigger_func(modified_any) called: action = UPDATE, when = BEFORE, level = ROW
357 NOTICE: trigger_func(modified_a) called: action = UPDATE, when = BEFORE, level = ROW
358 NOTICE: trigger_func(modified_a) called: action = UPDATE, when = BEFORE, level = ROW
359 NOTICE: trigger_func(modified_a) called: action = UPDATE, when = BEFORE, level = ROW
360 NOTICE: trigger_func(modified_a) called: action = UPDATE, when = BEFORE, level = ROW
361 NOTICE: trigger_func(modified_a) called: action = UPDATE, when = BEFORE, level = ROW
362 NOTICE: trigger_func(after_upd_row) called: action = UPDATE, when = AFTER, level = ROW
363 NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
364 SELECT * FROM main_table ORDER BY a, b;
377 SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_a';
379 --------------------------------------------------------------------------------------------------------------------------------------------
380 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')
383 SELECT pg_get_triggerdef(oid, false) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_a';
385 ----------------------------------------------------------------------------------------------------------------------------------------------
386 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')
389 SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_any';
391 --------------------------------------------------------------------------------------------------------------------------------------------------------------
392 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')
395 DROP TRIGGER modified_a ON main_table;
396 DROP TRIGGER modified_any ON main_table;
397 DROP TRIGGER insert_a ON main_table;
398 DROP TRIGGER delete_a ON main_table;
399 DROP TRIGGER insert_when ON main_table;
400 DROP TRIGGER delete_when ON main_table;
401 -- Test column-level triggers
402 DROP TRIGGER after_upd_row_trig ON main_table;
403 CREATE TRIGGER before_upd_a_row_trig BEFORE UPDATE OF a ON main_table
404 FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_upd_a_row');
405 CREATE TRIGGER after_upd_b_row_trig AFTER UPDATE OF b ON main_table
406 FOR EACH ROW EXECUTE PROCEDURE trigger_func('after_upd_b_row');
407 CREATE TRIGGER after_upd_a_b_row_trig AFTER UPDATE OF a, b ON main_table
408 FOR EACH ROW EXECUTE PROCEDURE trigger_func('after_upd_a_b_row');
409 CREATE TRIGGER before_upd_a_stmt_trig BEFORE UPDATE OF a ON main_table
410 FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('before_upd_a_stmt');
411 CREATE TRIGGER after_upd_b_stmt_trig AFTER UPDATE OF b ON main_table
412 FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('after_upd_b_stmt');
413 SELECT pg_get_triggerdef(oid) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'after_upd_a_b_row_trig';
415 -------------------------------------------------------------------------------------------------------------------------------------------
416 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')
419 UPDATE main_table SET a = 50;
420 NOTICE: trigger_func(before_upd_a_stmt) called: action = UPDATE, when = BEFORE, level = STATEMENT
421 NOTICE: trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW
422 NOTICE: trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW
423 NOTICE: trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW
424 NOTICE: trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW
425 NOTICE: trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW
426 NOTICE: trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW
427 NOTICE: trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW
428 NOTICE: trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW
429 NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
430 UPDATE main_table SET b = 10;
431 NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW
432 NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW
433 NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW
434 NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW
435 NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW
436 NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW
437 NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW
438 NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW
439 NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW
440 NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW
441 NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW
442 NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW
443 NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW
444 NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW
445 NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW
446 NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW
447 NOTICE: trigger_func(after_upd_b_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
448 NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
450 CREATE TRIGGER error_upd_and_col BEFORE UPDATE OR UPDATE OF a ON main_table
451 FOR EACH ROW EXECUTE PROCEDURE trigger_func('error_upd_and_col');
452 ERROR: duplicate trigger events specified at or near "ON"
453 LINE 1: ...ER error_upd_and_col BEFORE UPDATE OR UPDATE OF a ON main_ta...
455 CREATE TRIGGER error_upd_a_a BEFORE UPDATE OF a, a ON main_table
456 FOR EACH ROW EXECUTE PROCEDURE trigger_func('error_upd_a_a');
457 ERROR: column "a" specified more than once
458 CREATE TRIGGER error_ins_a BEFORE INSERT OF a ON main_table
459 FOR EACH ROW EXECUTE PROCEDURE trigger_func('error_ins_a');
460 ERROR: syntax error at or near "OF"
461 LINE 1: CREATE TRIGGER error_ins_a BEFORE INSERT OF a ON main_table
463 CREATE TRIGGER error_ins_when BEFORE INSERT OR UPDATE ON main_table
464 FOR EACH ROW WHEN (OLD.a <> NEW.a)
465 EXECUTE PROCEDURE trigger_func('error_ins_old');
466 ERROR: INSERT trigger's WHEN condition cannot reference OLD values
467 LINE 2: FOR EACH ROW WHEN (OLD.a <> NEW.a)
469 CREATE TRIGGER error_del_when BEFORE DELETE OR UPDATE ON main_table
470 FOR EACH ROW WHEN (OLD.a <> NEW.a)
471 EXECUTE PROCEDURE trigger_func('error_del_new');
472 ERROR: DELETE trigger's WHEN condition cannot reference NEW values
473 LINE 2: FOR EACH ROW WHEN (OLD.a <> NEW.a)
475 CREATE TRIGGER error_del_when BEFORE INSERT OR UPDATE ON main_table
476 FOR EACH ROW WHEN (NEW.tableoid <> 0)
477 EXECUTE PROCEDURE trigger_func('error_when_sys_column');
478 ERROR: BEFORE trigger's WHEN condition cannot reference NEW system columns
479 LINE 2: FOR EACH ROW WHEN (NEW.tableoid <> 0)
481 CREATE TRIGGER error_stmt_when BEFORE UPDATE OF a ON main_table
482 FOR EACH STATEMENT WHEN (OLD.* IS DISTINCT FROM NEW.*)
483 EXECUTE PROCEDURE trigger_func('error_stmt_when');
484 ERROR: statement trigger's WHEN condition cannot reference column values
485 LINE 2: FOR EACH STATEMENT WHEN (OLD.* IS DISTINCT FROM NEW.*)
487 -- check dependency restrictions
488 ALTER TABLE main_table DROP COLUMN b;
489 ERROR: cannot drop table main_table column b because other objects depend on it
490 DETAIL: trigger after_upd_b_row_trig on table main_table depends on table main_table column b
491 trigger after_upd_a_b_row_trig on table main_table depends on table main_table column b
492 trigger after_upd_b_stmt_trig on table main_table depends on table main_table column b
493 HINT: Use DROP ... CASCADE to drop the dependent objects too.
494 -- this should succeed, but we'll roll it back to keep the triggers around
496 DROP TRIGGER after_upd_a_b_row_trig ON main_table;
497 DROP TRIGGER after_upd_b_row_trig ON main_table;
498 DROP TRIGGER after_upd_b_stmt_trig ON main_table;
499 ALTER TABLE main_table DROP COLUMN b;
501 -- Test enable/disable triggers
502 create table trigtest (i serial primary key);
503 NOTICE: CREATE TABLE will create implicit sequence "trigtest_i_seq" for serial column "trigtest.i"
504 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "trigtest_pkey" for table "trigtest"
505 -- test that disabling RI triggers works
506 create table trigtest2 (i int references trigtest(i) on delete cascade);
507 create function trigtest() returns trigger as $$
509 raise notice '% % % %', TG_RELNAME, TG_OP, TG_WHEN, TG_LEVEL;
511 end;$$ language plpgsql;
512 create trigger trigtest_b_row_tg before insert or update or delete on trigtest
513 for each row execute procedure trigtest();
514 create trigger trigtest_a_row_tg after insert or update or delete on trigtest
515 for each row execute procedure trigtest();
516 create trigger trigtest_b_stmt_tg before insert or update or delete on trigtest
517 for each statement execute procedure trigtest();
518 create trigger trigtest_a_stmt_tg after insert or update or delete on trigtest
519 for each statement execute procedure trigtest();
520 insert into trigtest default values;
521 NOTICE: trigtest INSERT BEFORE STATEMENT
522 NOTICE: trigtest INSERT BEFORE ROW
523 NOTICE: trigtest INSERT AFTER ROW
524 NOTICE: trigtest INSERT AFTER STATEMENT
525 alter table trigtest disable trigger trigtest_b_row_tg;
526 insert into trigtest default values;
527 NOTICE: trigtest INSERT BEFORE STATEMENT
528 NOTICE: trigtest INSERT AFTER ROW
529 NOTICE: trigtest INSERT AFTER STATEMENT
530 alter table trigtest disable trigger user;
531 insert into trigtest default values;
532 alter table trigtest enable trigger trigtest_a_stmt_tg;
533 insert into trigtest default values;
534 NOTICE: trigtest INSERT AFTER STATEMENT
535 insert into trigtest2 values(1);
536 insert into trigtest2 values(2);
537 delete from trigtest where i=2;
538 NOTICE: trigtest DELETE AFTER STATEMENT
539 select * from trigtest2;
545 alter table trigtest disable trigger all;
546 delete from trigtest where i=1;
547 select * from trigtest2;
553 -- ensure we still insert, even when all triggers are disabled
554 insert into trigtest default values;
555 select * from trigtest;
563 drop table trigtest2;
566 CREATE TABLE trigger_test (
570 CREATE OR REPLACE FUNCTION trigger_data() RETURNS trigger
571 LANGUAGE plpgsql AS $$
580 relid := TG_relid::regclass;
582 -- plpgsql can't discover its trigger data in a hash like perl and python
583 -- can, or by a sort of reflection like tcl can,
584 -- so we have to hard code the names.
585 raise NOTICE 'TG_NAME: %', TG_name;
586 raise NOTICE 'TG_WHEN: %', TG_when;
587 raise NOTICE 'TG_LEVEL: %', TG_level;
588 raise NOTICE 'TG_OP: %', TG_op;
589 raise NOTICE 'TG_RELID::regclass: %', relid;
590 raise NOTICE 'TG_RELNAME: %', TG_relname;
591 raise NOTICE 'TG_TABLE_NAME: %', TG_table_name;
592 raise NOTICE 'TG_TABLE_SCHEMA: %', TG_table_schema;
593 raise NOTICE 'TG_NARGS: %', TG_nargs;
596 for i in 0 .. TG_nargs - 1 loop
598 argstr := argstr || ', ';
600 argstr := argstr || TG_argv[i];
602 argstr := argstr || ']';
603 raise NOTICE 'TG_ARGV: %', argstr;
605 if TG_OP != 'INSERT' then
606 raise NOTICE 'OLD: %', OLD;
609 if TG_OP != 'DELETE' then
610 raise NOTICE 'NEW: %', NEW;
613 if TG_OP = 'DELETE' then
621 CREATE TRIGGER show_trigger_data_trig
622 BEFORE INSERT OR UPDATE OR DELETE ON trigger_test
623 FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
624 insert into trigger_test values(1,'insert');
625 NOTICE: TG_NAME: show_trigger_data_trig
626 NOTICE: TG_WHEN: BEFORE
627 NOTICE: TG_LEVEL: ROW
628 NOTICE: TG_OP: INSERT
629 NOTICE: TG_RELID::regclass: trigger_test
630 NOTICE: TG_RELNAME: trigger_test
631 NOTICE: TG_TABLE_NAME: trigger_test
632 NOTICE: TG_TABLE_SCHEMA: public
634 NOTICE: TG_ARGV: [23, skidoo]
635 NOTICE: NEW: (1,insert)
636 update trigger_test set v = 'update' where i = 1;
637 NOTICE: TG_NAME: show_trigger_data_trig
638 NOTICE: TG_WHEN: BEFORE
639 NOTICE: TG_LEVEL: ROW
640 NOTICE: TG_OP: UPDATE
641 NOTICE: TG_RELID::regclass: trigger_test
642 NOTICE: TG_RELNAME: trigger_test
643 NOTICE: TG_TABLE_NAME: trigger_test
644 NOTICE: TG_TABLE_SCHEMA: public
646 NOTICE: TG_ARGV: [23, skidoo]
647 NOTICE: OLD: (1,insert)
648 NOTICE: NEW: (1,update)
649 delete from trigger_test;
650 NOTICE: TG_NAME: show_trigger_data_trig
651 NOTICE: TG_WHEN: BEFORE
652 NOTICE: TG_LEVEL: ROW
653 NOTICE: TG_OP: DELETE
654 NOTICE: TG_RELID::regclass: trigger_test
655 NOTICE: TG_RELNAME: trigger_test
656 NOTICE: TG_TABLE_NAME: trigger_test
657 NOTICE: TG_TABLE_SCHEMA: public
659 NOTICE: TG_ARGV: [23, skidoo]
660 NOTICE: OLD: (1,update)
661 DROP TRIGGER show_trigger_data_trig on trigger_test;
662 DROP FUNCTION trigger_data();
663 DROP TABLE trigger_test;
665 -- Test use of row comparisons on OLD/NEW
667 CREATE TABLE trigger_test (f1 int, f2 text, f3 text);
668 -- this is the obvious (and wrong...) way to compare rows
669 CREATE FUNCTION mytrigger() RETURNS trigger LANGUAGE plpgsql as $$
671 if row(old.*) = row(new.*) then
672 raise notice 'row % not changed', new.f1;
674 raise notice 'row % changed', new.f1;
679 BEFORE UPDATE ON trigger_test
680 FOR EACH ROW EXECUTE PROCEDURE mytrigger();
681 INSERT INTO trigger_test VALUES(1, 'foo', 'bar');
682 INSERT INTO trigger_test VALUES(2, 'baz', 'quux');
683 UPDATE trigger_test SET f3 = 'bar';
684 NOTICE: row 1 not changed
685 NOTICE: row 2 changed
686 UPDATE trigger_test SET f3 = NULL;
687 NOTICE: row 1 changed
688 NOTICE: row 2 changed
689 -- this demonstrates that the above isn't really working as desired:
690 UPDATE trigger_test SET f3 = NULL;
691 NOTICE: row 1 changed
692 NOTICE: row 2 changed
693 -- the right way when considering nulls is
694 CREATE OR REPLACE FUNCTION mytrigger() RETURNS trigger LANGUAGE plpgsql as $$
696 if row(old.*) is distinct from row(new.*) then
697 raise notice 'row % changed', new.f1;
699 raise notice 'row % not changed', new.f1;
703 UPDATE trigger_test SET f3 = 'bar';
704 NOTICE: row 1 changed
705 NOTICE: row 2 changed
706 UPDATE trigger_test SET f3 = NULL;
707 NOTICE: row 1 changed
708 NOTICE: row 2 changed
709 UPDATE trigger_test SET f3 = NULL;
710 NOTICE: row 1 not changed
711 NOTICE: row 2 not changed
712 DROP TABLE trigger_test;
713 DROP FUNCTION mytrigger();
714 -- Test snapshot management in serializable transactions involving triggers
715 -- per bug report in 6bc73d4c0910042358k3d1adff3qa36f8df75198ecea@mail.gmail.com
716 CREATE FUNCTION serializable_update_trig() RETURNS trigger LANGUAGE plpgsql AS
721 new.description = 'updated in trigger';
725 CREATE TABLE serializable_update_tab (
730 CREATE TRIGGER serializable_update_trig BEFORE UPDATE ON serializable_update_tab
731 FOR EACH ROW EXECUTE PROCEDURE serializable_update_trig();
732 INSERT INTO serializable_update_tab SELECT a, repeat('xyzxz', 100), 'new'
733 FROM generate_series(1, 50) a;
735 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
736 UPDATE serializable_update_tab SET description = 'no no', id = 1 WHERE id = 1;
738 SELECT description FROM serializable_update_tab WHERE id = 1;
744 DROP TABLE serializable_update_tab;
745 -- minimal update trigger
746 CREATE TABLE min_updates_test (
750 CREATE TABLE min_updates_test_oids (
754 INSERT INTO min_updates_test VALUES ('a',1,2),('b','2',null);
755 INSERT INTO min_updates_test_oids VALUES ('a',1,2),('b','2',null);
756 CREATE TRIGGER z_min_update
757 BEFORE UPDATE ON min_updates_test
758 FOR EACH ROW EXECUTE PROCEDURE suppress_redundant_updates_trigger();
759 CREATE TRIGGER z_min_update
760 BEFORE UPDATE ON min_updates_test_oids
761 FOR EACH ROW EXECUTE PROCEDURE suppress_redundant_updates_trigger();
763 UPDATE min_updates_test SET f1 = f1;
765 UPDATE min_updates_test SET f2 = f2 + 1;
767 UPDATE min_updates_test SET f3 = 2 WHERE f3 is null;
769 UPDATE min_updates_test_oids SET f1 = f1;
771 UPDATE min_updates_test_oids SET f2 = f2 + 1;
773 UPDATE min_updates_test_oids SET f3 = 2 WHERE f3 is null;
776 SELECT * FROM min_updates_test;
783 SELECT * FROM min_updates_test_oids;
790 DROP TABLE min_updates_test;
791 DROP TABLE min_updates_test_oids;
793 -- Test triggers on views
795 CREATE VIEW main_view AS SELECT a, b FROM main_table;
796 -- Updates should fail without rules or triggers
797 INSERT INTO main_view VALUES (1,2);
798 ERROR: cannot insert into view "main_view"
799 HINT: You need an unconditional ON INSERT DO INSTEAD rule or an INSTEAD OF INSERT trigger.
800 UPDATE main_view SET b = 20 WHERE a = 50;
801 ERROR: cannot update view "main_view"
802 HINT: You need an unconditional ON UPDATE DO INSTEAD rule or an INSTEAD OF UPDATE trigger.
803 DELETE FROM main_view WHERE a = 50;
804 ERROR: cannot delete from view "main_view"
805 HINT: You need an unconditional ON DELETE DO INSTEAD rule or an INSTEAD OF DELETE trigger.
806 -- Should fail even when there are no matching rows
807 DELETE FROM main_view WHERE a = 51;
808 ERROR: cannot delete from view "main_view"
809 HINT: You need an unconditional ON DELETE DO INSTEAD rule or an INSTEAD OF DELETE trigger.
810 -- VIEW trigger function
811 CREATE OR REPLACE FUNCTION view_trigger() RETURNS trigger
812 LANGUAGE plpgsql AS $$
816 for i in 0 .. TG_nargs - 1 loop
818 argstr := argstr || ', ';
820 argstr := argstr || TG_argv[i];
823 raise notice '% % % % (%)', TG_RELNAME, TG_WHEN, TG_OP, TG_LEVEL, argstr;
825 if TG_LEVEL = 'ROW' then
826 if TG_OP = 'INSERT' then
827 raise NOTICE 'NEW: %', NEW;
828 INSERT INTO main_table VALUES (NEW.a, NEW.b);
832 if TG_OP = 'UPDATE' then
833 raise NOTICE 'OLD: %, NEW: %', OLD, NEW;
834 UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b;
835 if NOT FOUND then RETURN NULL; end if;
839 if TG_OP = 'DELETE' then
840 raise NOTICE 'OLD: %', OLD;
841 DELETE FROM main_table WHERE a = OLD.a AND b = OLD.b;
842 if NOT FOUND then RETURN NULL; end if;
850 -- Before row triggers aren't allowed on views
851 CREATE TRIGGER invalid_trig BEFORE INSERT ON main_view
852 FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_ins_row');
853 ERROR: "main_view" is a view
854 DETAIL: Views cannot have row-level BEFORE or AFTER triggers.
855 CREATE TRIGGER invalid_trig BEFORE UPDATE ON main_view
856 FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_upd_row');
857 ERROR: "main_view" is a view
858 DETAIL: Views cannot have row-level BEFORE or AFTER triggers.
859 CREATE TRIGGER invalid_trig BEFORE DELETE ON main_view
860 FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_del_row');
861 ERROR: "main_view" is a view
862 DETAIL: Views cannot have row-level BEFORE or AFTER triggers.
863 -- After row triggers aren't allowed on views
864 CREATE TRIGGER invalid_trig AFTER INSERT ON main_view
865 FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_ins_row');
866 ERROR: "main_view" is a view
867 DETAIL: Views cannot have row-level BEFORE or AFTER triggers.
868 CREATE TRIGGER invalid_trig AFTER UPDATE ON main_view
869 FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_upd_row');
870 ERROR: "main_view" is a view
871 DETAIL: Views cannot have row-level BEFORE or AFTER triggers.
872 CREATE TRIGGER invalid_trig AFTER DELETE ON main_view
873 FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_del_row');
874 ERROR: "main_view" is a view
875 DETAIL: Views cannot have row-level BEFORE or AFTER triggers.
876 -- Truncate triggers aren't allowed on views
877 CREATE TRIGGER invalid_trig BEFORE TRUNCATE ON main_view
878 EXECUTE PROCEDURE trigger_func('before_tru_row');
879 ERROR: "main_view" is a view
880 DETAIL: Views cannot have TRUNCATE triggers.
881 CREATE TRIGGER invalid_trig AFTER TRUNCATE ON main_view
882 EXECUTE PROCEDURE trigger_func('before_tru_row');
883 ERROR: "main_view" is a view
884 DETAIL: Views cannot have TRUNCATE triggers.
885 -- INSTEAD OF triggers aren't allowed on tables
886 CREATE TRIGGER invalid_trig INSTEAD OF INSERT ON main_table
887 FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_ins');
888 ERROR: "main_table" is a table
889 DETAIL: Tables cannot have INSTEAD OF triggers.
890 CREATE TRIGGER invalid_trig INSTEAD OF UPDATE ON main_table
891 FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd');
892 ERROR: "main_table" is a table
893 DETAIL: Tables cannot have INSTEAD OF triggers.
894 CREATE TRIGGER invalid_trig INSTEAD OF DELETE ON main_table
895 FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_del');
896 ERROR: "main_table" is a table
897 DETAIL: Tables cannot have INSTEAD OF triggers.
898 -- Don't support WHEN clauses with INSTEAD OF triggers
899 CREATE TRIGGER invalid_trig INSTEAD OF UPDATE ON main_view
900 FOR EACH ROW WHEN (OLD.a <> NEW.a) EXECUTE PROCEDURE view_trigger('instead_of_upd');
901 ERROR: INSTEAD OF triggers cannot have WHEN conditions
902 -- Don't support column-level INSTEAD OF triggers
903 CREATE TRIGGER invalid_trig INSTEAD OF UPDATE OF a ON main_view
904 FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd');
905 ERROR: INSTEAD OF triggers cannot have column lists
906 -- Don't support statement-level INSTEAD OF triggers
907 CREATE TRIGGER invalid_trig INSTEAD OF UPDATE ON main_view
908 EXECUTE PROCEDURE view_trigger('instead_of_upd');
909 ERROR: INSTEAD OF triggers must be FOR EACH ROW
910 -- Valid INSTEAD OF triggers
911 CREATE TRIGGER instead_of_insert_trig INSTEAD OF INSERT ON main_view
912 FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_ins');
913 CREATE TRIGGER instead_of_update_trig INSTEAD OF UPDATE ON main_view
914 FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd');
915 CREATE TRIGGER instead_of_delete_trig INSTEAD OF DELETE ON main_view
916 FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_del');
917 -- Valid BEFORE statement VIEW triggers
918 CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON main_view
919 FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_ins_stmt');
920 CREATE TRIGGER before_upd_stmt_trig BEFORE UPDATE ON main_view
921 FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_upd_stmt');
922 CREATE TRIGGER before_del_stmt_trig BEFORE DELETE ON main_view
923 FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_del_stmt');
924 -- Valid AFTER statement VIEW triggers
925 CREATE TRIGGER after_ins_stmt_trig AFTER INSERT ON main_view
926 FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_ins_stmt');
927 CREATE TRIGGER after_upd_stmt_trig AFTER UPDATE ON main_view
928 FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_upd_stmt');
929 CREATE TRIGGER after_del_stmt_trig AFTER DELETE ON main_view
930 FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_del_stmt');
932 -- Insert into view using trigger
933 INSERT INTO main_view VALUES (20, 30);
934 NOTICE: main_view BEFORE INSERT STATEMENT (before_view_ins_stmt)
935 NOTICE: main_view INSTEAD OF INSERT ROW (instead_of_ins)
937 NOTICE: trigger_func(before_ins_stmt) called: action = INSERT, when = BEFORE, level = STATEMENT
938 CONTEXT: SQL statement "INSERT INTO main_table VALUES (NEW.a, NEW.b)"
939 PL/pgSQL function "view_trigger" line 17 at SQL statement
940 NOTICE: trigger_func(after_ins_stmt) called: action = INSERT, when = AFTER, level = STATEMENT
941 CONTEXT: SQL statement "INSERT INTO main_table VALUES (NEW.a, NEW.b)"
942 PL/pgSQL function "view_trigger" line 17 at SQL statement
943 NOTICE: main_view AFTER INSERT STATEMENT (after_view_ins_stmt)
945 INSERT INTO main_view VALUES (21, 31) RETURNING a, b;
946 NOTICE: main_view BEFORE INSERT STATEMENT (before_view_ins_stmt)
947 NOTICE: main_view INSTEAD OF INSERT ROW (instead_of_ins)
949 NOTICE: trigger_func(before_ins_stmt) called: action = INSERT, when = BEFORE, level = STATEMENT
950 CONTEXT: SQL statement "INSERT INTO main_table VALUES (NEW.a, NEW.b)"
951 PL/pgSQL function "view_trigger" line 17 at SQL statement
952 NOTICE: trigger_func(after_ins_stmt) called: action = INSERT, when = AFTER, level = STATEMENT
953 CONTEXT: SQL statement "INSERT INTO main_table VALUES (NEW.a, NEW.b)"
954 PL/pgSQL function "view_trigger" line 17 at SQL statement
955 NOTICE: main_view AFTER INSERT STATEMENT (after_view_ins_stmt)
962 -- Table trigger will prevent updates
963 UPDATE main_view SET b = 31 WHERE a = 20;
964 NOTICE: main_view BEFORE UPDATE STATEMENT (before_view_upd_stmt)
965 NOTICE: main_view INSTEAD OF UPDATE ROW (instead_of_upd)
966 NOTICE: OLD: (20,30), NEW: (20,31)
967 NOTICE: trigger_func(before_upd_a_stmt) called: action = UPDATE, when = BEFORE, level = STATEMENT
968 CONTEXT: SQL statement "UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b"
969 PL/pgSQL function "view_trigger" line 23 at SQL statement
970 NOTICE: trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW
971 CONTEXT: SQL statement "UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b"
972 PL/pgSQL function "view_trigger" line 23 at SQL statement
973 NOTICE: trigger_func(after_upd_b_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
974 CONTEXT: SQL statement "UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b"
975 PL/pgSQL function "view_trigger" line 23 at SQL statement
976 NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
977 CONTEXT: SQL statement "UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b"
978 PL/pgSQL function "view_trigger" line 23 at SQL statement
979 NOTICE: main_view AFTER UPDATE STATEMENT (after_view_upd_stmt)
981 UPDATE main_view SET b = 32 WHERE a = 21 AND b = 31 RETURNING a, b;
982 NOTICE: main_view BEFORE UPDATE STATEMENT (before_view_upd_stmt)
983 NOTICE: main_view INSTEAD OF UPDATE ROW (instead_of_upd)
984 NOTICE: OLD: (21,31), NEW: (21,32)
985 NOTICE: trigger_func(before_upd_a_stmt) called: action = UPDATE, when = BEFORE, level = STATEMENT
986 CONTEXT: SQL statement "UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b"
987 PL/pgSQL function "view_trigger" line 23 at SQL statement
988 NOTICE: trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW
989 CONTEXT: SQL statement "UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b"
990 PL/pgSQL function "view_trigger" line 23 at SQL statement
991 NOTICE: trigger_func(after_upd_b_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
992 CONTEXT: SQL statement "UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b"
993 PL/pgSQL function "view_trigger" line 23 at SQL statement
994 NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
995 CONTEXT: SQL statement "UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b"
996 PL/pgSQL function "view_trigger" line 23 at SQL statement
997 NOTICE: main_view AFTER UPDATE STATEMENT (after_view_upd_stmt)
1003 -- Remove table trigger to allow updates
1004 DROP TRIGGER before_upd_a_row_trig ON main_table;
1006 UPDATE main_view SET b = 31 WHERE a = 20;
1007 NOTICE: main_view BEFORE UPDATE STATEMENT (before_view_upd_stmt)
1008 NOTICE: main_view INSTEAD OF UPDATE ROW (instead_of_upd)
1009 NOTICE: OLD: (20,30), NEW: (20,31)
1010 NOTICE: trigger_func(before_upd_a_stmt) called: action = UPDATE, when = BEFORE, level = STATEMENT
1011 CONTEXT: SQL statement "UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b"
1012 PL/pgSQL function "view_trigger" line 23 at SQL statement
1013 NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW
1014 CONTEXT: SQL statement "UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b"
1015 PL/pgSQL function "view_trigger" line 23 at SQL statement
1016 NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW
1017 CONTEXT: SQL statement "UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b"
1018 PL/pgSQL function "view_trigger" line 23 at SQL statement
1019 NOTICE: trigger_func(after_upd_b_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
1020 CONTEXT: SQL statement "UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b"
1021 PL/pgSQL function "view_trigger" line 23 at SQL statement
1022 NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
1023 CONTEXT: SQL statement "UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b"
1024 PL/pgSQL function "view_trigger" line 23 at SQL statement
1025 NOTICE: main_view AFTER UPDATE STATEMENT (after_view_upd_stmt)
1027 UPDATE main_view SET b = 32 WHERE a = 21 AND b = 31 RETURNING a, b;
1028 NOTICE: main_view BEFORE UPDATE STATEMENT (before_view_upd_stmt)
1029 NOTICE: main_view INSTEAD OF UPDATE ROW (instead_of_upd)
1030 NOTICE: OLD: (21,31), NEW: (21,32)
1031 NOTICE: trigger_func(before_upd_a_stmt) called: action = UPDATE, when = BEFORE, level = STATEMENT
1032 CONTEXT: SQL statement "UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b"
1033 PL/pgSQL function "view_trigger" line 23 at SQL statement
1034 NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW
1035 CONTEXT: SQL statement "UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b"
1036 PL/pgSQL function "view_trigger" line 23 at SQL statement
1037 NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW
1038 CONTEXT: SQL statement "UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b"
1039 PL/pgSQL function "view_trigger" line 23 at SQL statement
1040 NOTICE: trigger_func(after_upd_b_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
1041 CONTEXT: SQL statement "UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b"
1042 PL/pgSQL function "view_trigger" line 23 at SQL statement
1043 NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
1044 CONTEXT: SQL statement "UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b"
1045 PL/pgSQL function "view_trigger" line 23 at SQL statement
1046 NOTICE: main_view AFTER UPDATE STATEMENT (after_view_upd_stmt)
1053 -- Before and after stmt triggers should fire even when no rows are affected
1054 UPDATE main_view SET b = 0 WHERE false;
1055 NOTICE: main_view BEFORE UPDATE STATEMENT (before_view_upd_stmt)
1056 NOTICE: main_view AFTER UPDATE STATEMENT (after_view_upd_stmt)
1058 -- Delete from view using trigger
1059 DELETE FROM main_view WHERE a IN (20,21);
1060 NOTICE: main_view BEFORE DELETE STATEMENT (before_view_del_stmt)
1061 NOTICE: main_view INSTEAD OF DELETE ROW (instead_of_del)
1062 NOTICE: OLD: (21,10)
1063 NOTICE: main_view INSTEAD OF DELETE ROW (instead_of_del)
1064 NOTICE: OLD: (20,31)
1065 NOTICE: main_view INSTEAD OF DELETE ROW (instead_of_del)
1066 NOTICE: OLD: (21,32)
1067 NOTICE: main_view AFTER DELETE STATEMENT (after_view_del_stmt)
1069 DELETE FROM main_view WHERE a = 31 RETURNING a, b;
1070 NOTICE: main_view BEFORE DELETE STATEMENT (before_view_del_stmt)
1071 NOTICE: main_view INSTEAD OF DELETE ROW (instead_of_del)
1072 NOTICE: OLD: (31,10)
1073 NOTICE: main_view AFTER DELETE STATEMENT (after_view_del_stmt)
1081 -- Describe view should list triggers
1083 View "public.main_view"
1084 Column | Type | Modifiers
1085 --------+---------+-----------
1089 after_del_stmt_trig AFTER DELETE ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_del_stmt')
1090 after_ins_stmt_trig AFTER INSERT ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_ins_stmt')
1091 after_upd_stmt_trig AFTER UPDATE ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_upd_stmt')
1092 before_del_stmt_trig BEFORE DELETE ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_del_stmt')
1093 before_ins_stmt_trig BEFORE INSERT ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_ins_stmt')
1094 before_upd_stmt_trig BEFORE UPDATE ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_upd_stmt')
1095 instead_of_delete_trig INSTEAD OF DELETE ON main_view FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_del')
1096 instead_of_insert_trig INSTEAD OF INSERT ON main_view FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_ins')
1097 instead_of_update_trig INSTEAD OF UPDATE ON main_view FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd')
1099 -- Test dropping view triggers
1100 DROP TRIGGER instead_of_insert_trig ON main_view;
1101 DROP TRIGGER instead_of_delete_trig ON main_view;
1103 View "public.main_view"
1104 Column | Type | Modifiers | Storage | Description
1105 --------+---------+-----------+---------+-------------
1106 a | integer | | plain |
1107 b | integer | | plain |
1109 SELECT main_table.a, main_table.b
1112 after_del_stmt_trig AFTER DELETE ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_del_stmt')
1113 after_ins_stmt_trig AFTER INSERT ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_ins_stmt')
1114 after_upd_stmt_trig AFTER UPDATE ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_upd_stmt')
1115 before_del_stmt_trig BEFORE DELETE ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_del_stmt')
1116 before_ins_stmt_trig BEFORE INSERT ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_ins_stmt')
1117 before_upd_stmt_trig BEFORE UPDATE ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_upd_stmt')
1118 instead_of_update_trig INSTEAD OF UPDATE ON main_view FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd')
1120 DROP VIEW main_view;
1122 -- Test triggers on a join view
1124 CREATE TABLE country_table (
1125 country_id serial primary key,
1126 country_name text unique not null,
1127 continent text not null
1129 NOTICE: CREATE TABLE will create implicit sequence "country_table_country_id_seq" for serial column "country_table.country_id"
1130 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "country_table_pkey" for table "country_table"
1131 NOTICE: CREATE TABLE / UNIQUE will create implicit index "country_table_country_name_key" for table "country_table"
1132 INSERT INTO country_table (country_name, continent)
1133 VALUES ('Japan', 'Asia'),
1135 ('USA', 'North America')
1137 country_id | country_name | continent
1138 ------------+--------------+---------------
1141 3 | USA | North America
1144 CREATE TABLE city_table (
1145 city_id serial primary key,
1146 city_name text not null,
1148 country_id int references country_table
1150 NOTICE: CREATE TABLE will create implicit sequence "city_table_city_id_seq" for serial column "city_table.city_id"
1151 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "city_table_pkey" for table "city_table"
1152 CREATE VIEW city_view AS
1153 SELECT city_id, city_name, population, country_name, continent
1155 LEFT JOIN country_table co ON co.country_id = ci.country_id;
1156 CREATE FUNCTION city_insert() RETURNS trigger LANGUAGE plpgsql AS $$
1160 if NEW.country_name IS NOT NULL then
1161 SELECT country_id, continent INTO ctry_id, NEW.continent
1162 FROM country_table WHERE country_name = NEW.country_name;
1164 raise exception 'No such country: "%"', NEW.country_name;
1167 NEW.continent := NULL;
1170 if NEW.city_id IS NOT NULL then
1171 INSERT INTO city_table
1172 VALUES(NEW.city_id, NEW.city_name, NEW.population, ctry_id);
1174 INSERT INTO city_table(city_name, population, country_id)
1175 VALUES(NEW.city_name, NEW.population, ctry_id)
1176 RETURNING city_id INTO NEW.city_id;
1182 CREATE TRIGGER city_insert_trig INSTEAD OF INSERT ON city_view
1183 FOR EACH ROW EXECUTE PROCEDURE city_insert();
1184 CREATE FUNCTION city_delete() RETURNS trigger LANGUAGE plpgsql AS $$
1186 DELETE FROM city_table WHERE city_id = OLD.city_id;
1187 if NOT FOUND then RETURN NULL; end if;
1191 CREATE TRIGGER city_delete_trig INSTEAD OF DELETE ON city_view
1192 FOR EACH ROW EXECUTE PROCEDURE city_delete();
1193 CREATE FUNCTION city_update() RETURNS trigger LANGUAGE plpgsql AS $$
1197 if NEW.country_name IS DISTINCT FROM OLD.country_name then
1198 SELECT country_id, continent INTO ctry_id, NEW.continent
1199 FROM country_table WHERE country_name = NEW.country_name;
1201 raise exception 'No such country: "%"', NEW.country_name;
1204 UPDATE city_table SET city_name = NEW.city_name,
1205 population = NEW.population,
1206 country_id = ctry_id
1207 WHERE city_id = OLD.city_id;
1209 UPDATE city_table SET city_name = NEW.city_name,
1210 population = NEW.population
1211 WHERE city_id = OLD.city_id;
1212 NEW.continent := OLD.continent;
1215 if NOT FOUND then RETURN NULL; end if;
1219 CREATE TRIGGER city_update_trig INSTEAD OF UPDATE ON city_view
1220 FOR EACH ROW EXECUTE PROCEDURE city_update();
1222 -- INSERT .. RETURNING
1223 INSERT INTO city_view(city_name) VALUES('Tokyo') RETURNING *;
1224 city_id | city_name | population | country_name | continent
1225 ---------+-----------+------------+--------------+-----------
1230 INSERT INTO city_view(city_name, population) VALUES('London', 7556900) RETURNING *;
1231 city_id | city_name | population | country_name | continent
1232 ---------+-----------+------------+--------------+-----------
1233 2 | London | 7556900 | |
1237 INSERT INTO city_view(city_name, country_name) VALUES('Washington DC', 'USA') RETURNING *;
1238 city_id | city_name | population | country_name | continent
1239 ---------+---------------+------------+--------------+---------------
1240 3 | Washington DC | | USA | North America
1244 INSERT INTO city_view(city_id, city_name) VALUES(123456, 'New York') RETURNING *;
1245 city_id | city_name | population | country_name | continent
1246 ---------+-----------+------------+--------------+-----------
1247 123456 | New York | | |
1251 INSERT INTO city_view VALUES(234567, 'Birmingham', 1016800, 'UK', 'EU') RETURNING *;
1252 city_id | city_name | population | country_name | continent
1253 ---------+------------+------------+--------------+-----------
1254 234567 | Birmingham | 1016800 | UK | Europe
1258 -- UPDATE .. RETURNING
1259 UPDATE city_view SET country_name = 'Japon' WHERE city_name = 'Tokyo'; -- error
1260 ERROR: No such country: "Japon"
1261 UPDATE city_view SET country_name = 'Japan' WHERE city_name = 'Takyo'; -- no match
1263 UPDATE city_view SET country_name = 'Japan' WHERE city_name = 'Tokyo' RETURNING *; -- OK
1264 city_id | city_name | population | country_name | continent
1265 ---------+-----------+------------+--------------+-----------
1266 1 | Tokyo | | Japan | Asia
1270 UPDATE city_view SET population = 13010279 WHERE city_name = 'Tokyo' RETURNING *;
1271 city_id | city_name | population | country_name | continent
1272 ---------+-----------+------------+--------------+-----------
1273 1 | Tokyo | 13010279 | Japan | Asia
1277 UPDATE city_view SET country_name = 'UK' WHERE city_name = 'New York' RETURNING *;
1278 city_id | city_name | population | country_name | continent
1279 ---------+-----------+------------+--------------+-----------
1280 123456 | New York | | UK | Europe
1284 UPDATE city_view SET country_name = 'USA', population = 8391881 WHERE city_name = 'New York' RETURNING *;
1285 city_id | city_name | population | country_name | continent
1286 ---------+-----------+------------+--------------+---------------
1287 123456 | New York | 8391881 | USA | North America
1291 UPDATE city_view SET continent = 'EU' WHERE continent = 'Europe' RETURNING *;
1292 city_id | city_name | population | country_name | continent
1293 ---------+------------+------------+--------------+-----------
1294 234567 | Birmingham | 1016800 | UK | Europe
1298 UPDATE city_view v1 SET country_name = v2.country_name FROM city_view v2
1299 WHERE v2.city_name = 'Birmingham' AND v1.city_name = 'London' RETURNING *;
1300 city_id | city_name | population | country_name | continent | city_id | city_name | population | country_name | continent
1301 ---------+-----------+------------+--------------+-----------+---------+------------+------------+--------------+-----------
1302 2 | London | 7556900 | UK | Europe | 234567 | Birmingham | 1016800 | UK | Europe
1306 -- DELETE .. RETURNING
1307 DELETE FROM city_view WHERE city_name = 'Birmingham' RETURNING *;
1308 city_id | city_name | population | country_name | continent
1309 ---------+------------+------------+--------------+-----------
1310 234567 | Birmingham | 1016800 | UK | Europe
1315 -- read-only view with WHERE clause
1316 CREATE VIEW european_city_view AS
1317 SELECT * FROM city_view WHERE continent = 'Europe';
1318 SELECT count(*) FROM european_city_view;
1324 CREATE FUNCTION no_op_trig_fn() RETURNS trigger LANGUAGE plpgsql
1325 AS 'begin RETURN NULL; end';
1326 CREATE TRIGGER no_op_trig INSTEAD OF INSERT OR UPDATE OR DELETE
1327 ON european_city_view FOR EACH ROW EXECUTE PROCEDURE no_op_trig_fn();
1329 INSERT INTO european_city_view VALUES (0, 'x', 10000, 'y', 'z');
1331 UPDATE european_city_view SET population = 10000;
1333 DELETE FROM european_city_view;
1336 -- rules bypassing no-op triggers
1337 CREATE RULE european_city_insert_rule AS ON INSERT TO european_city_view
1338 DO INSTEAD INSERT INTO city_view
1339 VALUES (NEW.city_id, NEW.city_name, NEW.population, NEW.country_name, NEW.continent)
1341 CREATE RULE european_city_update_rule AS ON UPDATE TO european_city_view
1342 DO INSTEAD UPDATE city_view SET
1343 city_name = NEW.city_name,
1344 population = NEW.population,
1345 country_name = NEW.country_name
1346 WHERE city_id = OLD.city_id
1348 CREATE RULE european_city_delete_rule AS ON DELETE TO european_city_view
1349 DO INSTEAD DELETE FROM city_view WHERE city_id = OLD.city_id RETURNING *;
1351 -- INSERT not limited by view's WHERE clause, but UPDATE AND DELETE are
1352 INSERT INTO european_city_view(city_name, country_name)
1353 VALUES ('Cambridge', 'USA') RETURNING *;
1354 city_id | city_name | population | country_name | continent
1355 ---------+-----------+------------+--------------+---------------
1356 4 | Cambridge | | USA | North America
1360 UPDATE european_city_view SET country_name = 'UK'
1361 WHERE city_name = 'Cambridge';
1363 DELETE FROM european_city_view WHERE city_name = 'Cambridge';
1365 -- UPDATE and DELETE via rule and trigger
1366 UPDATE city_view SET country_name = 'UK'
1367 WHERE city_name = 'Cambridge' RETURNING *;
1368 city_id | city_name | population | country_name | continent
1369 ---------+-----------+------------+--------------+-----------
1370 4 | Cambridge | | UK | Europe
1374 UPDATE european_city_view SET population = 122800
1375 WHERE city_name = 'Cambridge' RETURNING *;
1376 city_id | city_name | population | country_name | continent
1377 ---------+-----------+------------+--------------+-----------
1378 4 | Cambridge | 122800 | UK | Europe
1382 DELETE FROM european_city_view WHERE city_name = 'Cambridge' RETURNING *;
1383 city_id | city_name | population | country_name | continent
1384 ---------+-----------+------------+--------------+-----------
1385 4 | Cambridge | 122800 | UK | Europe
1390 UPDATE city_view v SET population = 599657
1391 FROM city_table ci, country_table co
1392 WHERE ci.city_name = 'Washington DC' and co.country_name = 'USA'
1393 AND v.city_id = ci.city_id AND v.country_name = co.country_name
1394 RETURNING co.country_id, v.country_name,
1395 v.city_id, v.city_name, v.population;
1396 country_id | country_name | city_id | city_name | population
1397 ------------+--------------+---------+---------------+------------
1398 3 | USA | 3 | Washington DC | 599657
1403 SELECT * FROM city_view;
1404 city_id | city_name | population | country_name | continent
1405 ---------+---------------+------------+--------------+---------------
1406 1 | Tokyo | 13010279 | Japan | Asia
1407 123456 | New York | 8391881 | USA | North America
1408 2 | London | 7556900 | UK | Europe
1409 3 | Washington DC | 599657 | USA | North America
1412 DROP TABLE city_table CASCADE;
1413 NOTICE: drop cascades to 2 other objects
1414 DETAIL: drop cascades to view city_view
1415 drop cascades to view european_city_view
1416 DROP TABLE country_table;