From 7fc0f06221d22632daa3ff8b70919b43e8a242ca Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Fri, 20 Nov 2009 20:38:12 +0000 Subject: Add a WHEN clause to CREATE TRIGGER, allowing a boolean expression to be checked to determine whether the trigger should be fired. For BEFORE triggers this is mostly a matter of spec compliance; but for AFTER triggers it can provide a noticeable performance improvement, since queuing of a deferred trigger event and re-fetching of the row(s) at end of statement can be short-circuited if the trigger does not need to be fired. Takahiro Itagaki, reviewed by KaiGai Kohei. --- doc/src/sgml/catalogs.sgml | 11 +++- doc/src/sgml/ref/create_constraint.sgml | 19 +++++- doc/src/sgml/ref/create_trigger.sgml | 112 ++++++++++++++++++++++++++++++-- doc/src/sgml/trigger.sgml | 22 ++++++- 4 files changed, 157 insertions(+), 7 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 174dd0ee819..f2959af5260 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -1,4 +1,4 @@ - + @@ -4756,6 +4756,15 @@ Argument strings to pass to trigger, each NULL-terminated + + + tgqual + text + + Expression tree (in nodeToString() + representation) for the trigger's WHEN condition, or NULL + if none + diff --git a/doc/src/sgml/ref/create_constraint.sgml b/doc/src/sgml/ref/create_constraint.sgml index dbf4d679f2c..7da88318568 100644 --- a/doc/src/sgml/ref/create_constraint.sgml +++ b/doc/src/sgml/ref/create_constraint.sgml @@ -1,5 +1,5 @@ @@ -27,6 +27,7 @@ CREATE CONSTRAINT TRIGGER name [ FROM referenced_table_name ] { NOT DEFERRABLE | [ DEFERRABLE ] { INITIALLY IMMEDIATE | INITIALLY DEFERRED } } FOR EACH ROW + [ WHEN ( condition ) ] EXECUTE PROCEDURE function_name ( arguments ) @@ -109,6 +110,22 @@ CREATE CONSTRAINT TRIGGER name + + condition + + + A Boolean expression that determines whether the trigger function + will actually be executed. This acts the same as in . + Note in particular that evaluation of the WHEN + condition is not deferred, but occurs immediately after the row + update operation is performed. If the condition does not evaluate + to true then the trigger is not queued for deferred + execution. + + + + function_name diff --git a/doc/src/sgml/ref/create_trigger.sgml b/doc/src/sgml/ref/create_trigger.sgml index 04f681f0308..78f5977999b 100644 --- a/doc/src/sgml/ref/create_trigger.sgml +++ b/doc/src/sgml/ref/create_trigger.sgml @@ -1,5 +1,5 @@ @@ -23,6 +23,7 @@ PostgreSQL documentation CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] } ON table [ FOR [ EACH ] { ROW | STATEMENT } ] + [ WHEN ( condition ) ] EXECUTE PROCEDURE function_name ( arguments ) @@ -72,6 +73,16 @@ CREATE TRIGGER name { BEFORE | AFTE FOR EACH STATEMENT. + + Also, a trigger definition can specify a boolean WHEN + condition, which will be tested to see whether the trigger should + be fired. In row-level triggers the WHEN condition can + examine the old and/or new values of columns of the row. Statement-level + triggers can also have WHEN conditions, although the feature + is not so useful for them since the condition cannot refer to any values + in the table. + + If multiple triggers of the same kind are defined for the same event, they will be fired in alphabetical order by name. @@ -159,6 +170,31 @@ UPDATE OF column_name1 [, column_name2 + + condition + + + A Boolean expression that determines whether the trigger function + will actually be executed. If WHEN is specified, the + function will only be called if the condition returns true. + In FOR EACH ROW triggers, the WHEN + condition can refer to columns of the old and/or new row values + by writing OLD.column_name or + NEW.column_name respectively. + Of course, INSERT triggers cannot refer to OLD + and DELETE triggers cannot refer to NEW. + + + + Currently, WHEN expressions cannot contain + subqueries. + + + + function_name @@ -213,6 +249,29 @@ UPDATE OF column_name1 [, column_name2 + + In a BEFORE trigger, the WHEN condition is + evaluated just before the function is or would be executed, so using + WHEN is not materially different from testing the same + condition at the beginning of the trigger function. Note in particular + that the NEW row seen by the condition is the current value, + as possibly modified by earlier triggers. Also, a BEFORE + trigger's WHEN condition is not allowed to examine the + system columns of the NEW row (such as oid), + because those won't have been set yet. + + + + In an AFTER trigger, the WHEN condition is + evaluated just after the row update occurs, and it determines whether an + event is queued to fire the trigger at the end of statement. So when an + AFTER trigger's WHEN condition does not return + true, it is not necessary to queue an event nor to re-fetch the row at end + of statement. This can result in significant speedups in statements that + modify many rows, if the trigger only needs to be fired for a few of the + rows. + + In PostgreSQL versions before 7.3, it was necessary to declare trigger functions as returning the placeholder @@ -223,11 +282,56 @@ UPDATE OF column_name1 [, column_name2 - + Examples - contains a complete example. + Execute the function check_account_update whenever + a row of the table accounts is about to be updated: + + +CREATE TRIGGER check_update + BEFORE UPDATE ON accounts + FOR EACH ROW + EXECUTE PROCEDURE check_account_update(); + + + The same, but only execute the function if column balance + is specified as a target in the UPDATE command: + + +CREATE TRIGGER check_update + BEFORE UPDATE OF balance ON accounts + FOR EACH ROW + EXECUTE PROCEDURE check_account_update(); + + + This form only executes the function if column balance + has in fact changed value: + + +CREATE TRIGGER check_update + BEFORE UPDATE ON accounts + FOR EACH ROW + WHEN (OLD.balance IS DISTINCT FROM NEW.balance) + EXECUTE PROCEDURE check_account_update(); + + + Call a function to log updates of accounts, but only if + something changed: + + +CREATE TRIGGER log_update + AFTER UPDATE ON accounts + FOR EACH ROW + WHEN (OLD.* IS DISTINCT FROM NEW.*) + EXECUTE PROCEDURE log_account_update(); + + + + + contains a complete example of a trigger + function written in C. @@ -258,7 +362,7 @@ UPDATE OF column_name1 [, column_name2PostgreSQL only allows the execution of a user-defined function for the triggered action. The standard allows the execution of a number of other SQL commands, such as - CREATE TABLE as the triggered action. This + CREATE TABLE, as the triggered action. This limitation is not hard to work around by creating a user-defined function that executes the desired commands. diff --git a/doc/src/sgml/trigger.sgml b/doc/src/sgml/trigger.sgml index 60977cbd564..2c2ec89b028 100644 --- a/doc/src/sgml/trigger.sgml +++ b/doc/src/sgml/trigger.sgml @@ -1,4 +1,4 @@ - + Triggers @@ -140,6 +140,25 @@ triggers are not fired. + + A trigger definition can also specify a boolean WHEN + condition, which will be tested to see whether the trigger should + be fired. In row-level triggers the WHEN condition can + examine the old and/or new values of columns of the row. (Statement-level + triggers can also have WHEN conditions, although the feature + is not so useful for them.) In a before trigger, the WHEN + condition is evaluated just before the function is or would be executed, + so using WHEN is not materially different from testing the + same condition at the beginning of the trigger function. However, in + an after trigger, the WHEN condition is evaluated just after + the row update occurs, and it determines whether an event is queued to + fire the trigger at the end of statement. So when an after trigger's + WHEN condition does not return true, it is not necessary + to queue an event nor to re-fetch the row at end of statement. This + can result in significant speedups in statements that modify many + rows, if the trigger only needs to be fired for a few of the rows. + + Typically, row before triggers are used for checking or modifying the data that will be inserted or updated. For example, @@ -497,6 +516,7 @@ typedef struct Trigger int16 tgnattr; int16 *tgattr; char **tgargs; + char *tgqual; } Trigger; -- cgit v1.2.3