From 7692d8d5b72f510bd84f708d0a8e53c548f71adc Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Fri, 28 Mar 2008 00:21:56 +0000 Subject: Support statement-level ON TRUNCATE triggers. Simon Riggs --- doc/src/sgml/plperl.sgml | 21 +++++++++++--------- doc/src/sgml/plpgsql.sgml | 8 ++++---- doc/src/sgml/plpython.sgml | 25 +++++++++++++----------- doc/src/sgml/pltcl.sgml | 14 ++++++++----- doc/src/sgml/ref/create_trigger.sgml | 38 ++++++++++++++++++++++++------------ doc/src/sgml/ref/truncate.sgml | 16 +++++++++++---- doc/src/sgml/trigger.sgml | 27 +++++++++++++++++-------- 7 files changed, 95 insertions(+), 54 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/plperl.sgml b/doc/src/sgml/plperl.sgml index 11040c5700c..ce217dfa33b 100644 --- a/doc/src/sgml/plperl.sgml +++ b/doc/src/sgml/plperl.sgml @@ -1,4 +1,4 @@ - + PL/Perl - Perl Procedural Language @@ -17,12 +17,14 @@ Perl programming language. - The usual advantage to using PL/Perl is that this allows use, + + The main advantage to using PL/Perl is that this allows use, within stored functions, of the manyfold string - munging operators and functions available for Perl. Parsing + munging operators and functions available for Perl. Parsing complex strings might be easier using Perl than it is with the - string functions and control structures provided in PL/pgSQL. - + string functions and control structures provided in PL/pgSQL. + + To install PL/Perl in a particular database, use createlang plperl dbname. @@ -739,7 +741,8 @@ $$ LANGUAGE plperl; $_TD->{event} - Trigger event: INSERT, UPDATE, DELETE, or UNKNOWN + Trigger event: INSERT, UPDATE, + DELETE, TRUNCATE, or UNKNOWN @@ -822,14 +825,14 @@ $$ LANGUAGE plperl; - Triggers can return one of the following: + Row-level triggers can return one of the following: return; - Execute the statement + Execute the operation @@ -838,7 +841,7 @@ $$ LANGUAGE plperl; "SKIP" - Don't execute the statement + Don't execute the operation diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index 73873614f64..f7b94798d87 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -1,4 +1,4 @@ - + <application>PL/pgSQL</application> - <acronym>SQL</acronym> Procedural Language @@ -2785,9 +2785,9 @@ RAISE EXCEPTION 'Nonexistent ID --> %', user_id; Data type text; a string of - INSERT, UPDATE, or - DELETE telling for which operation the - trigger was fired. + INSERT, UPDATE, + DELETE, or TRUNCATE + telling for which operation the trigger was fired. diff --git a/doc/src/sgml/plpython.sgml b/doc/src/sgml/plpython.sgml index 718bb7e4fd4..d4770176080 100644 --- a/doc/src/sgml/plpython.sgml +++ b/doc/src/sgml/plpython.sgml @@ -1,4 +1,4 @@ - + PL/Python - Python Procedural Language @@ -381,31 +381,34 @@ $$ LANGUAGE plpythonu; When a function is used as a trigger, the dictionary - TD contains trigger-related values. The trigger - rows are in TD["new"] and/or TD["old"] - depending on the trigger event. TD["event"] contains + TD contains trigger-related values. + TD["event"] contains the event as a string (INSERT, UPDATE, - DELETE, or UNKNOWN). + DELETE, TRUNCATE, or UNKNOWN). TD["when"] contains one of BEFORE, - AFTER, and UNKNOWN. + AFTER, or UNKNOWN. TD["level"] contains one of ROW, - STATEMENT, and UNKNOWN. + STATEMENT, or UNKNOWN. + For a row-level trigger, the trigger + rows are in TD["new"] and/or TD["old"] + depending on the trigger event. TD["name"] contains the trigger name, TD["table_name"] contains the name of the table on which the trigger occurred, TD["table_schema"] contains the schema of the table on which the trigger occurred, - TD["name"] contains the trigger name, and - TD["relid"] contains the OID of the table on + and TD["relid"] contains the OID of the table on which the trigger occurred. If the CREATE TRIGGER command included arguments, they are available in TD["args"][0] to - TD["args"][(n-1)]. + TD["args"][n-1]. - If TD["when"] is BEFORE, you can + If TD["when"] is BEFORE and + TD["level"] is ROW, you can return None or "OK" from the Python function to indicate the row is unmodified, "SKIP" to abort the event, or "MODIFY" to indicate you've modified the row. + Otherwise the return value is ignored. diff --git a/doc/src/sgml/pltcl.sgml b/doc/src/sgml/pltcl.sgml index 38d12128568..899891bee51 100644 --- a/doc/src/sgml/pltcl.sgml +++ b/doc/src/sgml/pltcl.sgml @@ -1,4 +1,4 @@ - + PL/Tcl - Tcl Procedural Language @@ -569,7 +569,7 @@ SELECT 'doesn''t' AS ret The string BEFORE or AFTER depending on the - type of trigger call. + type of trigger event. @@ -579,7 +579,7 @@ SELECT 'doesn''t' AS ret The string ROW or STATEMENT depending on the - type of trigger call. + type of trigger event. @@ -588,8 +588,9 @@ SELECT 'doesn''t' AS ret $TG_op - The string INSERT, UPDATE, or - DELETE depending on the type of trigger call. + The string INSERT, UPDATE, + DELETE, or TRUNCATE depending on the type of + trigger event. @@ -602,6 +603,7 @@ SELECT 'doesn''t' AS ret row for INSERT or UPDATE actions, or empty for DELETE. The array is indexed by column name. Columns that are null will not appear in the array. + This is not set for statement-level triggers. @@ -614,6 +616,7 @@ SELECT 'doesn''t' AS ret row for UPDATE or DELETE actions, or empty for INSERT. The array is indexed by column name. Columns that are null will not appear in the array. + This is not set for statement-level triggers. @@ -644,6 +647,7 @@ SELECT 'doesn''t' AS ret only.) Needless to say that all this is only meaningful when the trigger is BEFORE and FOR EACH ROW; otherwise the return value is ignored. + Here's a little example trigger procedure that forces an integer value in a table to keep track of the number of updates that are performed on the diff --git a/doc/src/sgml/ref/create_trigger.sgml b/doc/src/sgml/ref/create_trigger.sgml index 9cbdcf91651..13079815667 100644 --- a/doc/src/sgml/ref/create_trigger.sgml +++ b/doc/src/sgml/ref/create_trigger.sgml @@ -1,5 +1,5 @@ @@ -25,7 +25,7 @@ CREATE TRIGGER name { BEFORE | AFTE EXECUTE PROCEDURE funcname ( arguments ) - + Description @@ -65,6 +65,12 @@ CREATE TRIGGER name { BEFORE | AFTE EACH STATEMENT triggers). + + In addition, triggers may be defined to fire for a + TRUNCATE, though only + FOR EACH STATEMENT. + + If multiple triggers of the same kind are defined for the same event, they will be fired in alphabetical order by name. @@ -80,7 +86,7 @@ CREATE TRIGGER name { BEFORE | AFTE Refer to for more information about triggers. - + Parameters @@ -110,10 +116,10 @@ CREATE TRIGGER name { BEFORE | AFTE event - One of INSERT, UPDATE, or - DELETE; this specifies the event that will - fire the trigger. Multiple events can be specified using - OR. + One of INSERT, UPDATE, + DELETE, or TRUNCATE; + this specifies the event that will fire the trigger. Multiple + events can be specified using OR. @@ -179,6 +185,11 @@ CREATE TRIGGER name { BEFORE | AFTE TRIGGER privilege on the table. + + Use to remove a trigger. + + In PostgreSQL versions before 7.3, it was necessary to declare trigger functions as returning the placeholder @@ -187,11 +198,6 @@ CREATE TRIGGER name { BEFORE | AFTE declared as returning opaque, but it will issue a notice and change the function's declared return type to trigger. - - - Use to remove a trigger. - @@ -204,7 +210,7 @@ CREATE TRIGGER name { BEFORE | AFTE Compatibility - + The CREATE TRIGGER statement in PostgreSQL implements a subset of the @@ -267,6 +273,12 @@ CREATE TRIGGER name { BEFORE | AFTE OR is a PostgreSQL extension of the SQL standard. + + + The ability to fire triggers for TRUNCATE is a + PostgreSQL extension of the SQL standard. + + diff --git a/doc/src/sgml/ref/truncate.sgml b/doc/src/sgml/ref/truncate.sgml index 3dca068b457..486a2d3e992 100644 --- a/doc/src/sgml/ref/truncate.sgml +++ b/doc/src/sgml/ref/truncate.sgml @@ -1,5 +1,5 @@ @@ -36,7 +36,7 @@ TRUNCATE [ TABLE ] name [, ...] [ C operation. This is most useful on large tables. - + Parameters @@ -91,8 +91,16 @@ TRUNCATE [ TABLE ] name [, ...] [ C - TRUNCATE will not run any ON DELETE - triggers that might exist for the tables. + TRUNCATE will not fire any ON DELETE + triggers that might exist for the tables. But it will fire + ON TRUNCATE triggers. + If ON TRUNCATE triggers are defined for any of + the tables, then all BEFORE TRUNCATE triggers are + fired before any truncation happens, and all AFTER + TRUNCATE triggers are fired after the last truncation is + performed. The triggers will fire in the order that the tables are + to be processed (first those listed in the command, and then any + that were added due to cascading). diff --git a/doc/src/sgml/trigger.sgml b/doc/src/sgml/trigger.sgml index 942aeb4b7e4..a13925b0662 100644 --- a/doc/src/sgml/trigger.sgml +++ b/doc/src/sgml/trigger.sgml @@ -1,4 +1,4 @@ - + Triggers @@ -36,14 +36,15 @@ performed. Triggers can be defined to execute either before or after any INSERT, UPDATE, or DELETE operation, either once per modified row, - or once per SQL statement. - If a trigger event occurs, the trigger's function is called - at the appropriate time to handle the event. + or once per SQL statement. Triggers can also fire + for TRUNCATE statements. If a trigger event occurs, + the trigger's function is called at the appropriate time to handle the + event. The trigger function must be defined before the trigger itself can be - created. The trigger function must be declared as a + created. The trigger function must be declared as a function taking no arguments and returning type trigger. (The trigger function receives its input through a specially-passed TriggerData structure, not in the form of ordinary function @@ -69,7 +70,8 @@ in the execution of any applicable per-statement triggers. These two types of triggers are sometimes called row-level triggers and statement-level triggers, - respectively. + respectively. Triggers on TRUNCATE may only be + defined at statement-level. @@ -398,6 +400,15 @@ typedef struct TriggerData + + + TRIGGER_FIRED_BY_TRUNCATE(tg_event) + + + Returns true if the trigger was fired by a TRUNCATE command. + + + @@ -630,10 +641,10 @@ CREATE FUNCTION trigf() RETURNS trigger AS 'filename' LANGUAGE C; -CREATE TRIGGER tbefore BEFORE INSERT OR UPDATE OR DELETE ON ttest +CREATE TRIGGER tbefore BEFORE INSERT OR UPDATE OR DELETE ON ttest FOR EACH ROW EXECUTE PROCEDURE trigf(); -CREATE TRIGGER tafter AFTER INSERT OR UPDATE OR DELETE ON ttest +CREATE TRIGGER tafter AFTER INSERT OR UPDATE OR DELETE ON ttest FOR EACH ROW EXECUTE PROCEDURE trigf(); -- cgit v1.2.3