From 1b7f3cc02d6129b678ab651716c19d2bf8f7f6ab Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Sat, 23 Nov 2002 03:59:09 +0000 Subject: 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 --- doc/src/sgml/plpgsql.sgml | 104 ++++++++------- doc/src/sgml/ref/alter_trigger.sgml | 4 +- doc/src/sgml/ref/create_trigger.sgml | 87 ++++++++---- doc/src/sgml/release.sgml | 4 +- doc/src/sgml/trigger.sgml | 248 +++++++++++++---------------------- 5 files changed, 210 insertions(+), 237 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index 1486ee8e316..43d00d68f0e 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -1,5 +1,5 @@ @@ -674,24 +674,25 @@ RENAME this_var TO that_var; Expressions - All expressions used in PL/pgSQL statements - are processed using the server's regular SQL executor. Expressions that - appear to contain - constants may in fact require run-time evaluation - (e.g. 'now' for the - timestamp type) so - it is impossible for the PL/pgSQL parser - to identify real constant values other than the NULL keyword. All - expressions are evaluated internally by executing a query + All expressions used in PL/pgSQL + statements are processed using the server's regular + SQL executor. Expressions that appear to + contain constants may in fact require run-time evaluation + (e.g. 'now' for the timestamp + type) so it is impossible for the + PL/pgSQL parser to identify real + constant values other than the NULL keyword. All expressions are + evaluated internally by executing a query SELECT expression - using the SPI manager. In the expression, occurrences - of PL/pgSQL variable + using the SPI manager. In the expression, + occurrences of PL/pgSQL variable identifiers are replaced by parameters and the actual values from the variables are passed to the executor in the parameter array. - This allows the query plan for the SELECT to be prepared just once - and then re-used for subsequent evaluations. + This allows the query plan for the SELECT to + be prepared just once and then re-used for subsequent + evaluations. @@ -1100,41 +1101,43 @@ GET DIAGNOSTICS variable = item - A SELECT INTO statement sets FOUND - true if it returns a row, false if no row is returned. + A SELECT INTO statement sets + FOUND true if it returns a row, false if no + row is returned. - A PERFORM statement sets FOUND + A PERFORM statement sets FOUND true if it produces (discards) a row, false if no row is produced. - UPDATE, INSERT, and DELETE statements set - FOUND true if at least one row is - affected, false if no row is affected. + UPDATE, INSERT, and DELETE + statements set FOUND true if at least one + row is affected, false if no row is affected. - A FETCH statement sets FOUND + A FETCH statement sets FOUND true if it returns a row, false if no row is returned. - A FOR statement sets FOUND - true if it iterates one or more times, else false. - This applies to all three variants of the FOR statement - (integer FOR loops, record-set FOR loops, and dynamic - record-set FOR loops). FOUND is only set - when the FOR loop exits: inside the execution of the loop, - FOUND is not modified by the FOR statement, - although it may be changed by the execution of other - statements within the loop body. + A FOR statement sets FOUND true + if it iterates one or more times, else false. This applies to + all three variants of the FOR statement (integer + FOR loops, record-set FOR loops, and + dynamic record-set FOR + loops). FOUND is only set when the + FOR loop exits: inside the execution of the loop, + FOUND is not modified by the + FOR statement, although it may be changed by the + execution of other statements within the loop body. @@ -1975,7 +1978,7 @@ RAISE EXCEPTION ''Inexistent ID --> %'',user_id; PL/pgSQL can be used to define trigger procedures. A trigger procedure is created with the CREATE FUNCTION command as a function with no - arguments and a return type of TRIGGER. Note that + arguments and a return type of trigger. Note that the function must be declared with no arguments even if it expects to receive arguments specified in CREATE TRIGGER --- trigger arguments are passed via TG_ARGV, as described @@ -1992,8 +1995,9 @@ RAISE EXCEPTION ''Inexistent ID --> %'',user_id; NEW - Data type RECORD; variable holding the new database row for INSERT/UPDATE - operations in ROW level triggers. + Data type RECORD; variable holding the new + database row for INSERT/UPDATE operations in ROW level + triggers. This variable is NULL in STATEMENT level triggers. @@ -2002,8 +2006,9 @@ RAISE EXCEPTION ''Inexistent ID --> %'',user_id; OLD - Data type RECORD; variable holding the old database row for UPDATE/DELETE - operations in ROW level triggers. + Data type RECORD; variable holding the old + database row for UPDATE/DELETE operations in ROW level + triggers. This variable is NULL in STATEMENT level triggers. @@ -2098,22 +2103,23 @@ RAISE EXCEPTION ''Inexistent ID --> %'',user_id; A trigger function must return either NULL or a record/row value - having exactly the structure of the table the trigger was fired for. - Triggers fired BEFORE may return NULL to signal the trigger manager - to skip the rest of the operation for this row (ie, subsequent triggers - are not fired, and the INSERT/UPDATE/DELETE does not occur for this - row). If a non-NULL value is returned then the operation proceeds with - that row value. Note that returning a row value different from the - original value of NEW alters the row that will be inserted or updated. - It is possible to replace single values directly - in NEW and return that, or to build a complete new record/row to - return. + having exactly the structure of the table the trigger was fired + for. The return value of a BEFORE or AFTER STATEMENT level + trigger, or an AFTER ROW level trigger is ignored; it may as well + return NULL. However, any of these types of triggers can still + abort the entire trigger operation by raising an error. - The return value of a trigger fired AFTER is ignored; it may as well - always return a NULL value. But an AFTER trigger can still abort the - operation by raising an error. + ROW level triggers fired BEFORE may return NULL to signal the + trigger manager to skip the rest of the operation for this row + (ie, subsequent triggers are not fired, and the + INSERT/UPDATE/DELETE does not occur for this row). If a non-NULL + value is returned then the operation proceeds with that row value. + Note that returning a row value different from the original value + of NEW alters the row that will be inserted or updated. It is + possible to replace single values directly in NEW and return that, + or to build a complete new record/row to return. @@ -2143,7 +2149,7 @@ CREATE FUNCTION emp_stamp () RETURNS TRIGGER AS ' RAISE EXCEPTION ''% cannot have NULL salary'', NEW.empname; END IF; - -- Who works for us when she must pay for? + -- Who works for us when she must pay for it? IF NEW.salary < 0 THEN RAISE EXCEPTION ''% cannot have a negative salary'', NEW.empname; END IF; diff --git a/doc/src/sgml/ref/alter_trigger.sgml b/doc/src/sgml/ref/alter_trigger.sgml index cdfbb792c74..4dfe945d2b6 100644 --- a/doc/src/sgml/ref/alter_trigger.sgml +++ b/doc/src/sgml/ref/alter_trigger.sgml @@ -153,8 +153,8 @@ ALTER TRIGGER emp_stamp ON emp RENAME TO emp_track_chgs; SQL92 - The clause to rename triggers is a - PostgreSQL extension from SQL92. + ALTER TRIGGER is a PostgreSQL + extension of SQL92. diff --git a/doc/src/sgml/ref/create_trigger.sgml b/doc/src/sgml/ref/create_trigger.sgml index 67481c19a31..ac8309af2e1 100644 --- a/doc/src/sgml/ref/create_trigger.sgml +++ b/doc/src/sgml/ref/create_trigger.sgml @@ -1,5 +1,5 @@ @@ -21,8 +21,9 @@ PostgreSQL documentation 2000-03-25 -CREATE TRIGGER name { BEFORE | AFTER } { event [OR ...] } - ON table FOR EACH { ROW | STATEMENT } +CREATE TRIGGER name { + BEFORE | AFTER } { event [ OR ... ] } + ON table [ FOR EACH { ROW | STATEMENT } ] EXECUTE PROCEDURE func ( arguments ) @@ -45,11 +46,26 @@ CREATE TRIGGER name { BEFORE | AFTE + + + BEFORE + AFTER + + + Determines whether the function is called before or after the + event. + + + + event - One of INSERT, DELETE or UPDATE. + One of INSERT, DELETE or + UPDATE; this specifies the event that will + fire the trigger. Multiple events can be specified using + OR. @@ -57,10 +73,26 @@ CREATE TRIGGER name { BEFORE | AFTE table - The name (optionally schema-qualified) of the table the trigger is for. + The name (optionally schema-qualified) of the table the + trigger is for. + + + FOR EACH ROW + FOR EACH STATEMENT + + + + This specifies whether the trigger procedure should be fired + once for every row affected by the trigger event, or just once + per SQL statement. If neither is specified, FOR EACH + STATEMENT is the default. + + + + func @@ -74,11 +106,15 @@ CREATE TRIGGER name { BEFORE | AFTE arguments - An optional comma-separated list of arguments to be provided to the - function when the trigger is executed, along with the standard trigger - data such as old and new tuple contents. The arguments are literal - string constants. Simple names and numeric constants may be written - here too, but they will all be converted to strings. + An optional comma-separated list of arguments to be provided to + the function when the trigger is executed, along with the standard + trigger data such as old and new tuple contents. The arguments + are literal string constants. Simple names and numeric constants + may be written here too, but they will all be converted to + strings. Note that these arguments are not provided as normal + function parameters (since a trigger procedure must be declared to + take zero parameters), but are instead accessed through the + TG_ARGV array. @@ -121,7 +157,7 @@ CREATE TRIGGER CREATE TRIGGER will enter a new trigger into the current - data base. The trigger will be associated with the relation + database. The trigger will be associated with the relation table and will execute the specified function func. @@ -141,15 +177,27 @@ CREATE TRIGGER or deletion, are visible to the trigger. + + A trigger that executes FOR EACH ROW of the + specified operation is called once for every row that the operation + modifies. For example, a DELETE that affects 10 + rows will cause any ON DELETE triggers on the + target relation to be called 10 separate times, once for each + deleted tuple. In contrast, a trigger that executes FOR + EACH STATEMENT of the specified operation only executes + once for any given operation, regardless of how many rows it + modifies. + + If multiple triggers of the same kind are defined for the same event, they will be fired in alphabetical order by name. - SELECT does not modify any rows so you can not - create SELECT triggers. Rules and views are more - appropriate in such cases. + SELECT does not modify any rows so you can not + create SELECT triggers. Rules and views are more + appropriate in such cases. @@ -176,10 +224,6 @@ CREATE TRIGGER change the function's declared return type to trigger. - - As of the current release, STATEMENT triggers are not implemented. - - Refer to the command for information on how to remove triggers. @@ -268,13 +312,6 @@ CREATE TABLE distributors ( - - - PostgreSQL only has row-level - triggers, no statement-level triggers. - - - PostgreSQL only allows the diff --git a/doc/src/sgml/release.sgml b/doc/src/sgml/release.sgml index de439f3713d..0c5c03beb3c 100644 --- a/doc/src/sgml/release.sgml +++ b/doc/src/sgml/release.sgml @@ -1,5 +1,5 @@ @@ -4619,7 +4619,7 @@ Enhancements * pg_dump now output the schema and/or the data, with many fixes to enhance completeness. * psql used in place of monitor in administration shell scripts. - monitor to be depreciated in next release. + monitor to be deprecated in next release. * date/time functions enhanced * NULL insert/update/comparison fixed/enhanced * TCL/TK lib and shell fixed to work with both tck7.4/tk4.0 and tcl7.5/tk4.1 diff --git a/doc/src/sgml/trigger.sgml b/doc/src/sgml/trigger.sgml index fa3e149accc..b24663aa7a0 100644 --- a/doc/src/sgml/trigger.sgml +++ b/doc/src/sgml/trigger.sgml @@ -1,5 +1,5 @@ @@ -7,21 +7,24 @@ $Header: /cvsroot/pgsql/doc/src/sgml/trigger.sgml,v 1.25 2002/09/21 18:32:54 pet PostgreSQL has various server-side - function interfaces. Server-side functions can be written in SQL, - C, or any defined procedural language. Trigger functions can be - written in C and most procedural languages, but not in SQL. Note that - statement-level trigger events are not supported in the current - version. You can currently specify BEFORE or AFTER on INSERT, - DELETE or UPDATE of a tuple as a trigger event. + function interfaces. Server-side functions can be written in + SQL, C, or any defined procedural + language. Trigger functions can be written in C and most procedural + languages, but not in SQL. Both per-row and + per-statement triggers are supported. A trigger procedure can + execute BEFORE or AFTER a INSERT, + DELETE or UPDATE, either once + per modified row, or once per SQL statement. Trigger Definition - If a trigger event occurs, the trigger manager (called by the Executor) - sets up a TriggerData information structure (described below) and calls - the trigger function to handle the event. + If a trigger event occurs, the trigger manager (called by the + Executor) sets up a TriggerData information + structure (described below) and calls the trigger function to + handle the event. @@ -35,116 +38,13 @@ $Header: /cvsroot/pgsql/doc/src/sgml/trigger.sgml,v 1.25 2002/09/21 18:32:54 pet - The syntax for creating triggers is: - - -CREATE TRIGGER trigger [ BEFORE | AFTER ] [ INSERT | DELETE | UPDATE [ OR ... ] ] - ON relation FOR EACH [ ROW | STATEMENT ] - EXECUTE PROCEDURE procedure - (args); - - - where the arguments are: - - - - - trigger - - - - The trigger must have a name distinct from all other triggers on - the same table. The name is needed - if you ever have to delete the trigger. - - - - - - BEFORE - AFTER - - - Determines whether the function is called before or after - the event. - - - - - - INSERT - DELETE - UPDATE - - - The next element of the command determines what event(s) will trigger - the function. Multiple events can be specified separated by OR. - - - - - - relation - - - The relation name indicates which table the event applies to. - - - - - - ROW - STATEMENT - - - The FOR EACH clause determines whether the trigger is fired for each - affected row or before (or after) the entire statement has completed. - Currently only the ROW case is supported. - - - - - - procedure - - - The procedure name is the function to be called. - - - - - - args - - - The arguments passed to the function in the TriggerData structure. - This is either empty or a list of one or more simple literal - constants (which will be passed to the function as strings). - - - - The purpose of including arguments in the trigger definition - is to allow different - triggers with similar requirements to call the same function. - As an example, there could be a generalized trigger - function that takes as its arguments two field names and puts the - current user in one and the current time stamp in the other. - Properly written, this trigger function would be independent of - the specific table it is triggering on. So the same function - could be used for INSERT events on any table with suitable fields, - to automatically track creation of records in a transaction table for - example. It could also be used to track last-update events if - defined as an UPDATE trigger. - - - - + The syntax for creating triggers is described in &cite-reference;. - Trigger functions return a HeapTuple to the calling executor. The return - value is ignored for triggers fired AFTER an operation, - but it allows BEFORE triggers to: + Trigger functions return a HeapTuple to the calling + executor. The return value is ignored for triggers fired AFTER an + operation, but it allows BEFORE triggers to: @@ -157,9 +57,10 @@ CREATE TRIGGER trigger [ BEFORE | AFTER ] [ INSERT | - For INSERT and UPDATE triggers only, the returned tuple becomes the - tuple which will be inserted or will replace the tuple being updated. - This allows the trigger function to modify the row being inserted or + For INSERT and UPDATE + triggers only, the returned tuple becomes the tuple which will + be inserted or will replace the tuple being updated. This + allows the trigger function to modify the row being inserted or updated. @@ -170,8 +71,9 @@ CREATE TRIGGER trigger [ BEFORE | AFTER ] [ INSERT | - Note that there is no initialization performed by the CREATE TRIGGER - handler. This may be changed in the future. + Note that there is no initialization performed by the + CREATE TRIGGER handler. This may be changed in + the future. @@ -184,15 +86,34 @@ CREATE TRIGGER trigger [ BEFORE | AFTER ] [ INSERT | - If a trigger function executes SQL-queries (using SPI) then these queries - may fire triggers again. This is known as cascading triggers. There is no - direct limitation on the number of cascade levels. It is possible for - cascades to cause recursive invocation of the same trigger --- for - example, an INSERT trigger might execute a query that inserts an - additional tuple into the same table, causing the INSERT trigger to be - fired again. It is the trigger programmer's - responsibility to avoid infinite recursion in such scenarios. + If a trigger function executes SQL-queries (using SPI) then these + queries may fire triggers again. This is known as cascading + triggers. There is no direct limitation on the number of cascade + levels. It is possible for cascades to cause recursive invocation + of the same trigger --- for example, an INSERT + trigger might execute a query that inserts an additional tuple + into the same table, causing the INSERT trigger + to be fired again. It is the trigger programmer's responsibility + to avoid infinite recursion in such scenarios. + + + When a trigger is defined, a number of arguments can be + specified. The purpose of including arguments in the trigger + definition is to allow different triggers with similar + requirements to call the same function. As an example, there + could be a generalized trigger function that takes as its + arguments two field names and puts the current user in one and the + current time stamp in the other. Properly written, this trigger + function would be independent of the specific table it is + triggering on. So the same function could be used for + INSERT events on any table with suitable + fields, to automatically track creation of records in a + transaction table for example. It could also be used to track + last-update events if defined as an UPDATE + trigger. + + @@ -215,18 +136,20 @@ CREATE TRIGGER trigger [ BEFORE | AFTER ] [ INSERT | - When a function is called by the trigger manager, it is not passed any - normal parameters, but it is passed a context pointer pointing to a - TriggerData structure. C functions can check whether they were called - from the trigger manager or not by executing the macro + When a function is called by the trigger manager, it is not passed + any normal parameters, but it is passed a context + pointer pointing to a TriggerData structure. C + functions can check whether they were called from the trigger + manager or not by executing the macro CALLED_AS_TRIGGER(fcinfo), which expands to ((fcinfo)->context != NULL && IsA((fcinfo)->context, TriggerData)) - If this returns true, then it is safe to cast fcinfo->context to type - TriggerData * and make use of the pointed-to - TriggerData structure. - The function must not alter the TriggerData + If this returns true, then it is safe to cast + fcinfo->context to type TriggerData + * and make use of the pointed-to + TriggerData structure. The function must + not alter the TriggerData structure or any of the data it points to. @@ -288,8 +211,7 @@ typedef struct TriggerData TRIGGER_FIRED_FOR_ROW(event) - Returns TRUE if trigger fired for - a ROW-level event. + Returns TRUE if trigger fired for a ROW-level event. @@ -298,8 +220,7 @@ typedef struct TriggerData TRIGGER_FIRED_FOR_STATEMENT(event) - Returns TRUE if trigger fired for - STATEMENT-level event. + Returns TRUE if trigger fired for STATEMENT-level event. @@ -308,7 +229,7 @@ typedef struct TriggerData TRIGGER_FIRED_BY_INSERT(event) - Returns TRUE if trigger fired by INSERT. + Returns TRUE if trigger fired by INSERT. @@ -317,7 +238,7 @@ typedef struct TriggerData TRIGGER_FIRED_BY_DELETE(event) - Returns TRUE if trigger fired by DELETE. + Returns TRUE if trigger fired by DELETE. @@ -326,7 +247,7 @@ typedef struct TriggerData TRIGGER_FIRED_BY_UPDATE(event) - Returns TRUE if trigger fired by UPDATE. + Returns TRUE if trigger fired by UPDATE. @@ -356,11 +277,15 @@ typedef struct TriggerData tg_trigtuple - is a pointer to the tuple for which the trigger is fired. This is the tuple - being inserted (if INSERT), deleted (if DELETE) or updated (if UPDATE). - If INSERT/DELETE then this is what you are to return to Executor if - you don't want to replace tuple with another one (INSERT) or skip the - operation. + is a pointer to the tuple for which the trigger is fired. This is + the tuple being inserted (if INSERT), deleted + (if DELETE) or updated (if + UPDATE). If this trigger was fired for an + INSERT or DELETE then this + is what you should return to the Executor if you don't want to + replace the tuple with a different one (in the case of + INSERT) or skip the operation (in the case of + DELETE). @@ -369,9 +294,11 @@ typedef struct TriggerData tg_newtuple - is a pointer to the new version of tuple if UPDATE and NULL if this is - for an INSERT or a DELETE. This is what you are to return to Executor if - UPDATE and you don't want to replace this tuple with another one or skip + is a pointer to the new version of tuple if + UPDATE and NULL if this is for an + INSERT or a DELETE. This is + what you are to return to Executor if UPDATE + and you don't want to replace this tuple with another one or skip the operation. @@ -404,8 +331,9 @@ typedef struct Trigger where tgname is the trigger's name, tgnargs is number of arguments in tgargs, tgargs is an array of - pointers to the arguments specified in the CREATE TRIGGER - statement. Other members are for internal use only. + pointers to the arguments specified in the CREATE + TRIGGER statement. Other members are for internal use + only. @@ -460,10 +388,12 @@ execution of Q) or after Q is done. - Here is a very simple example of trigger usage. Function trigf reports - the number of tuples in the triggered relation ttest and skips the - operation if the query attempts to insert a null value into x (i.e - it acts as a - not-null constraint but doesn't abort the transaction). + Here is a very simple example of trigger usage. Function + trigf reports the number of tuples in the triggered + relation ttest and skips the operation if the query + attempts to insert a null value into x (i.e - it acts as a + NOT NULL constraint but doesn't abort the + transaction). #include "executor/spi.h" /* this is what you need to work with SPI */ -- cgit v1.2.3