diff options
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/regress/expected/triggers.out | 54 | ||||
| -rw-r--r-- | src/test/regress/sql/triggers.sql | 54 |
2 files changed, 106 insertions, 2 deletions
diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out index b5a62dace1b..b8e49452b6c 100644 --- a/src/test/regress/expected/triggers.out +++ b/src/test/regress/expected/triggers.out @@ -91,7 +91,7 @@ DROP TABLE fkeys; DROP TABLE fkeys2; -- -- I've disabled the funny_dup17 test because the new semantics -- -- of AFTER ROW triggers, which get now fired at the end of a --- -- query allways, cause funny_dup17 to enter an endless loop. +-- -- query always, cause funny_dup17 to enter an endless loop. -- -- -- -- Jan -- @@ -260,3 +260,55 @@ select * from tttest where price_on <= 35 and price_off > 35 and price_id = 5; 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, 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_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(); +CREATE TRIGGER after_ins_stmt_trig AFTER INSERT ON main_table +FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func(); +-- +-- if neither 'FOR EACH ROW' nor 'FOR EACH STATEMENT' was specified, +-- CREATE TRIGGER should default to 'FOR EACH STATEMENT' +-- +CREATE TRIGGER before_upd_stmt_trig AFTER UPDATE ON main_table +EXECUTE PROCEDURE trigger_func(); +CREATE TRIGGER before_upd_row_trig AFTER UPDATE ON main_table +FOR EACH ROW EXECUTE PROCEDURE trigger_func(); +INSERT INTO main_table DEFAULT VALUES; +NOTICE: trigger_func() called: action = INSERT, when = BEFORE, level = STATEMENT +NOTICE: trigger_func() called: action = INSERT, when = AFTER, level = STATEMENT +UPDATE main_table SET a = a + 1 WHERE b < 30; +NOTICE: trigger_func() called: action = UPDATE, when = AFTER, level = ROW +NOTICE: trigger_func() called: action = UPDATE, when = AFTER, level = ROW +NOTICE: trigger_func() called: action = UPDATE, when = AFTER, level = ROW +NOTICE: trigger_func() called: action = UPDATE, when = AFTER, level = ROW +NOTICE: trigger_func() 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() called: action = UPDATE, when = AFTER, level = STATEMENT +-- COPY should fire per-row and per-statement INSERT triggers +COPY main_table (a, b) FROM stdin; +NOTICE: trigger_func() called: action = INSERT, when = BEFORE, level = STATEMENT +NOTICE: trigger_func() called: action = INSERT, when = AFTER, level = STATEMENT +SELECT * FROM main_table ORDER BY a; + a | b +----+---- + 6 | 10 + 21 | 20 + 30 | 40 + 31 | 10 + 50 | 35 + 50 | 60 + 81 | 15 + | +(8 rows) + diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql index 6358249116e..214ffff4469 100644 --- a/src/test/regress/sql/triggers.sql +++ b/src/test/regress/sql/triggers.sql @@ -93,7 +93,7 @@ DROP TABLE fkeys2; -- -- I've disabled the funny_dup17 test because the new semantics -- -- of AFTER ROW triggers, which get now fired at the end of a --- -- query allways, cause funny_dup17 to enter an endless loop. +-- -- query always, cause funny_dup17 to enter an endless loop. -- -- -- -- Jan -- @@ -196,3 +196,55 @@ select * from tttest where price_on <= 35 and price_off > 35 and price_id = 5; 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, b int); + +COPY main_table (a,b) FROM stdin; +5 10 +20 20 +30 10 +50 35 +80 15 +\. + +CREATE FUNCTION trigger_func() RETURNS trigger LANGUAGE 'plpgsql' AS ' +BEGIN + RAISE NOTICE ''trigger_func() called: action = %, when = %, level = %'', 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(); + +CREATE TRIGGER after_ins_stmt_trig AFTER INSERT ON main_table +FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func(); + +-- +-- if neither 'FOR EACH ROW' nor 'FOR EACH STATEMENT' was specified, +-- CREATE TRIGGER should default to 'FOR EACH STATEMENT' +-- +CREATE TRIGGER before_upd_stmt_trig AFTER UPDATE ON main_table +EXECUTE PROCEDURE trigger_func(); + +CREATE TRIGGER before_upd_row_trig AFTER UPDATE ON main_table +FOR EACH ROW EXECUTE PROCEDURE trigger_func(); + +INSERT INTO main_table DEFAULT VALUES; + +UPDATE main_table SET a = a + 1 WHERE b < 30; +-- UPDATE that effects zero rows should still call per-statement trigger +UPDATE main_table SET a = a + 2 WHERE b > 100; + +-- COPY should fire per-row and per-statement INSERT triggers +COPY main_table (a, b) FROM stdin; +30 40 +50 60 +\. + +SELECT * FROM main_table ORDER BY a;
\ No newline at end of file |