This patch implements FOR EACH STATEMENT triggers, per my email to

-hackers a couple days ago.

Notes/caveats:

        - added regression tests for the new functionality, all
          regression tests pass on my machine

        - added pg_dump support

        - updated PL/PgSQL to support per-statement triggers; didn't
          look at the other procedural languages.

        - there's (even) more code duplication in trigger.c than there
          was previously. Any suggestions on how to refactor the
          ExecXXXTriggers() functions to reuse more code would be
          welcome -- I took a brief look at it, but couldn't see an
          easy way to do it (there are several subtly-different
          versions of the code in question)

        - updated the documentation. I also took the liberty of
          removing a big chunk of duplicated syntax documentation in
          the Programmer's Guide on triggers, and moving that
          information to the CREATE TRIGGER reference page.

        - I also included some spelling fixes and similar small
          cleanups I noticed while making the changes. If you'd like
          me to split those into a separate patch, let me know.

Neil Conway

2 files changed, 106 insertions, 2 deletions

diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out
index b5a62dace1..b8e49452b6 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 6358249116..214ffff446 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