diff options
Diffstat (limited to 'doc/src/sgml/trigger.sgml')
| -rw-r--r-- | doc/src/sgml/trigger.sgml | 396 |
1 files changed, 396 insertions, 0 deletions
diff --git a/doc/src/sgml/trigger.sgml b/doc/src/sgml/trigger.sgml new file mode 100644 index 00000000000..b8b7fc12ab3 --- /dev/null +++ b/doc/src/sgml/trigger.sgml @@ -0,0 +1,396 @@ +<Chapter> +<Title>Triggers</Title> + +<Para> + While the current version of <ProductName>Postgres</ProductName> has various client interfaces +such as Perl, Tcl, Python and C, it lacks an actual <FirstTerm>Procedural Language</FirstTerm> +(PL). We hope to have a proper PL one day. In the meantime it is possible +to call C functions as trigger actions. 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. +</Para> + +<Sect1> +<Title>Trigger Creation</Title> + +<Para> + If a trigger event occurs, the trigger manager (called by the Executor) +initializes the global structure TriggerData *CurrentTriggerData (described +below) and calls the trigger function to handle the event. +</Para> + +<Para> + The trigger function must be created before the trigger is created as a +function taking no arguments and returns opaque. +</Para> + +<Para> + The syntax for creating triggers is as follows: + +<ProgramListing> + CREATE TRIGGER <trigger name> <BEFORE|AFTER> <INSERT|DELETE|UPDATE> + ON <relation name> FOR EACH <ROW|STATEMENT> + EXECUTE PROCEDURE <procedure name> (<function args>); +</ProgramListing> +</Para> + +<Para> + The name of the trigger is used if you ever have to delete the trigger. +It is used as an argument to the DROP TRIGGER command. +</Para> + +<Para> + The next word determines whether the function is called before or after +the event. +</Para> + +<Para> + The next element of the command determines on what event(s) will trigger +the function. Multiple events can be specified separated by OR. +</Para> + +<Para> + The relation name determines which table the event applies to. +</Para> + +<Para> + The FOR EACH statement determines whether the trigger is fired for each +affected row or before (or after) the entire statement has completed. +</Para> + +<Para> + The procedure name is the C function called. +</Para> + +<Para> + The args are passed to the function in the CurrentTriggerData structure. +The purpose of passing arguments to the function is to allow different +triggers with similar requirements to call the same function. +</Para> + +<Para> + Also, function may be used for triggering different relations (these +functions are named as "general trigger functions"). +</Para> + +<Para> + As example of using both features above, there could be a general +function that takes as its arguments two field names and puts the current +user in one and the current timestamp in the other. This allows triggers to +be written on INSERT events to automatically track creation of records in a +transaction table for example. It could also be used as a "last updated" +function if used in an UPDATE event. +</Para> + +<Para> + Trigger functions return HeapTuple to the calling Executor. This +is ignored for triggers fired after an INSERT, DELETE or UPDATE operation +but it allows BEFORE triggers to: + + - return NULL to skip the operation for the current tuple (and so the + tuple will not be inserted/updated/deleted); + - return a pointer to another tuple (INSERT and UPDATE only) which will + be inserted (as the new version of the updated tuple if UPDATE) instead + of original tuple. +</Para> + +<Para> + Note, that there is no initialization performed by the CREATE TRIGGER +handler. This will be changed in the future. Also, if more than one trigger +is defined for the same event on the same relation, the order of trigger +firing is unpredictable. This may be changed in the future. +</Para> + +<Para> + 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 +explicit limitation on the number of cascade levels. +</Para> + +<Para> + If a trigger is fired by INSERT and inserts a new tuple in the same +relation then this trigger will be fired again. Currently, there is nothing +provided for synchronization (etc) of these cases but this may change. At +the moment, there is function funny_dup17() in the regress tests which uses +some techniques to stop recursion (cascading) on itself... +</Para> + +</Sect1> + +<Sect1> +<Title>Interaction with the Trigger Manager</Title> + +<Para> + As mentioned above, when function is called by the trigger manager, +structure TriggerData *CurrentTriggerData is NOT NULL and initialized. So +it is better to check CurrentTriggerData against being NULL at the start +and set it to NULL just after fetching the information to prevent calls to +a trigger function not from the trigger manager. +</Para> + +<Para> + struct TriggerData is defined in src/include/commands/trigger.h: + +<ProgramListing> +typedef struct TriggerData +{ + TriggerEvent tg_event; + Relation tg_relation; + HeapTuple tg_trigtuple; + HeapTuple tg_newtuple; + Trigger *tg_trigger; +} TriggerData; +</ProgramListing> + +<ProgramListing> +tg_event + describes event for which the function is called. You may use the + following macros to examine tg_event: + + TRIGGER_FIRED_BEFORE(event) returns TRUE if trigger fired BEFORE; + TRIGGER_FIRED_AFTER(event) returns TRUE if trigger fired AFTER; + TRIGGER_FIRED_FOR_ROW(event) returns TRUE if trigger fired for + ROW-level event; + TRIGGER_FIRED_FOR_STATEMENT(event) returns TRUE if trigger fired for + STATEMENT-level event; + TRIGGER_FIRED_BY_INSERT(event) returns TRUE if trigger fired by INSERT; + TRIGGER_FIRED_BY_DELETE(event) returns TRUE if trigger fired by DELETE; + TRIGGER_FIRED_BY_UPDATE(event) returns TRUE if trigger fired by UPDATE. + +tg_relation + is pointer to structure describing the triggered relation. Look at + src/include/utils/rel.h for details about this structure. The most + interest things are tg_relation->rd_att (descriptor of the relation + tuples) and tg_relation->rd_rel->relname (relation's name. This is not + char*, but NameData. Use SPI_getrelname(tg_relation) to get char* if + you need a copy of name). + +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. + +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 + the operation. + +tg_trigger + is pointer to structure Trigger defined in src/include/utils/rel.h: + +typedef struct Trigger +{ + char *tgname; + Oid tgfoid; + func_ptr tgfunc; + int16 tgtype; + int16 tgnargs; + int16 tgattr[8]; + char **tgargs; +} Trigger; + + 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. +</ProgramListing> +</Para> +</Sect1> + +<Sect1> +<Title>Visibility of Data Changes</Title> + +<Para> + <ProductName>Postgres</ProductName> data changes visibility rule: during a query execution, data +changes made by the query itself (via SQL-function, SPI-function, triggers) +are invisible to the query scan. For example, in query + +<ProgramListing> + INSERT INTO a SELECT * FROM a +</ProgramListing> + + tuples inserted are invisible for SELECT' scan. In effect, this +duplicates the database table within itself (subject to unique index +rules, of course) without recursing. +</Para> + +<Para> + But keep in mind this notice about visibility in the SPI documentation: + +<ProgramListing> + Changes made by query Q are visible by queries which are started after + query Q, no matter whether they are started inside Q (during the + execution of Q) or after Q is done. +</ProgramListing> +</Para> + +<Para> + This is true for triggers as well so, though a tuple being inserted +(tg_trigtuple) is not visible to queries in a BEFORE trigger, this tuple +(just inserted) is visible to queries in an AFTER trigger, and to queries +in BEFORE/AFTER triggers fired after this! +</Para> +</Sect1> + +<Sect1> +<Title>Examples</Title> + +<Para> + There are more complex examples in in src/test/regress/regress.c and +in contrib/spi. +</Para> + +<Para> + 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 NULL into x (i.e - it acts as a +NOT NULL constraint but doesn't abort the transaction). + +<ProgramListing> +#include "executor/spi.h" /* this is what you need to work with SPI */ +#include "commands/trigger.h" /* -"- and triggers */ + +HeapTuple trigf(void); + +HeapTuple +trigf() +{ + TupleDesc tupdesc; + HeapTuple rettuple; + char *when; + bool checknull = false; + bool isnull; + int ret, i; + + if (!CurrentTriggerData) + elog(WARN, "trigf: triggers are not initialized"); + + /* tuple to return to Executor */ + if (TRIGGER_FIRED_BY_UPDATE(CurrentTriggerData->tg_event)) + rettuple = CurrentTriggerData->tg_newtuple; + else + rettuple = CurrentTriggerData->tg_trigtuple; + + /* check for NULLs ? */ + if (!TRIGGER_FIRED_BY_DELETE(CurrentTriggerData->tg_event) && + TRIGGER_FIRED_BEFORE(CurrentTriggerData->tg_event)) + checknull = true; + + if (TRIGGER_FIRED_BEFORE(CurrentTriggerData->tg_event)) + when = "before"; + else + when = "after "; + + tupdesc = CurrentTriggerData->tg_relation->rd_att; + CurrentTriggerData = NULL; + + /* Connect to SPI manager */ + if ((ret = SPI_connect()) < 0) + elog(WARN, "trigf (fired %s): SPI_connect returned %d", when, ret); + + /* Get number of tuples in relation */ + ret = SPI_exec("select count(*) from ttest", 0); + + if (ret < 0) + elog(WARN, "trigf (fired %s): SPI_exec returned %d", when, ret); + + i = SPI_getbinval(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1, &isnull); + + elog (NOTICE, "trigf (fired %s): there are %d tuples in ttest", when, i); + + SPI_finish(); + + if (checknull) + { + i = SPI_getbinval(rettuple, tupdesc, 1, &isnull); + if (isnull) + rettuple = NULL; + } + + return (rettuple); +} +</ProgramListing> +</Para> + +<Para> + Now, compile and +create table ttest (x int4); +create function trigf () returns opaque as +'...path_to_so' language 'c'; + +<ProgramListing> +vac=> create trigger tbefore before insert or update or delete on ttest +for each row execute procedure trigf(); +CREATE +vac=> create trigger tafter after insert or update or delete on ttest +for each row execute procedure trigf(); +CREATE +vac=> insert into ttest values (null); +NOTICE:trigf (fired before): there are 0 tuples in ttest +INSERT 0 0 + +-- Insertion skipped and AFTER trigger is not fired + +vac=> select * from ttest; +x +- +(0 rows) + +vac=> insert into ttest values (1); +NOTICE:trigf (fired before): there are 0 tuples in ttest +NOTICE:trigf (fired after ): there are 1 tuples in ttest + ^^^^^^^^ + remember what we said about visibility. +INSERT 167793 1 +vac=> select * from ttest; +x +- +1 +(1 row) + +vac=> insert into ttest select x * 2 from ttest; +NOTICE:trigf (fired before): there are 1 tuples in ttest +NOTICE:trigf (fired after ): there are 2 tuples in ttest + ^^^^^^^^ + remember what we said about visibility. +INSERT 167794 1 +vac=> select * from ttest; +x +- +1 +2 +(2 rows) + +vac=> update ttest set x = null where x = 2; +NOTICE:trigf (fired before): there are 2 tuples in ttest +UPDATE 0 +vac=> update ttest set x = 4 where x = 2; +NOTICE:trigf (fired before): there are 2 tuples in ttest +NOTICE:trigf (fired after ): there are 2 tuples in ttest +UPDATE 1 +vac=> select * from ttest; +x +- +1 +4 +(2 rows) + +vac=> delete from ttest; +NOTICE:trigf (fired before): there are 2 tuples in ttest +NOTICE:trigf (fired after ): there are 1 tuples in ttest +NOTICE:trigf (fired before): there are 1 tuples in ttest +NOTICE:trigf (fired after ): there are 0 tuples in ttest + ^^^^^^^^ + remember what we said about visibility. +DELETE 2 +vac=> select * from ttest; +x +- +(0 rows) +</ProgramListing> + +</Para> +</Sect1> +</Chapter> |