41.6. Trigger Functions in PL/Tcl#
41.6. Trigger Functions in PL/Tcl #
Trigger functions can be written in PL/Tcl.
Tantor SE requires that a function that is to be called
as a trigger must be declared as a function with no arguments
and a return type of trigger.
The information from the trigger manager is passed to the function body in the following variables:
$TG_nameThe name of the trigger from the
CREATE TRIGGERstatement.$TG_relidThe object ID of the table that caused the trigger function to be invoked.
$TG_table_nameThe name of the table that caused the trigger function to be invoked.
$TG_table_schemaThe schema of the table that caused the trigger function to be invoked.
$TG_relattsA Tcl list of the table column names, prefixed with an empty list element. So looking up a column name in the list with Tcl's
lsearchcommand returns the element's number starting with 1 for the first column, the same way the columns are customarily numbered in Tantor SE. (Empty list elements also appear in the positions of columns that have been dropped, so that the attribute numbering is correct for columns to their right.)$TG_whenThe string
BEFORE,AFTER, orINSTEAD OF, depending on the type of trigger event.$TG_levelThe string
ROWorSTATEMENTdepending on the type of trigger event.$TG_opThe string
INSERT,UPDATE,DELETE, orTRUNCATEdepending on the type of trigger event.$NEWAn associative array containing the values of the new table row for
INSERTorUPDATEactions, or empty forDELETE. 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.$OLDAn associative array containing the values of the old table row for
UPDATEorDELETEactions, or empty forINSERT. 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.$argsA Tcl list of the arguments to the function as given in the
CREATE TRIGGERstatement. These arguments are also accessible as$1...$in the function body.n
The return value from a trigger function can be one of the strings
OK or SKIP, or a list of column name/value pairs.
If the return value is OK,
the operation (INSERT/UPDATE/DELETE)
that fired the trigger will proceed
normally. SKIP tells the trigger manager to silently suppress
the operation for this row. If a list is returned, it tells PL/Tcl to
return a modified row to the trigger manager; the contents of the
modified row are specified by the column names and values in the list.
Any columns not mentioned in the list are set to null.
Returning a modified row is only meaningful
for row-level BEFORE INSERT or UPDATE
triggers, for which the modified row will be inserted instead of the one
given in $NEW; or for row-level INSTEAD OF
INSERT or UPDATE triggers where the returned row
is used as the source data for INSERT RETURNING or
UPDATE RETURNING clauses.
In row-level BEFORE DELETE or INSTEAD
OF DELETE triggers, returning a modified row has the same
effect as returning OK, that is the operation proceeds.
The trigger return value is ignored for all other types of triggers.
Tip
The result list can be made from an array representation of the
modified tuple with the array get Tcl command.
Here's a little example trigger function that forces an integer value in a table to keep track of the number of updates that are performed on the row. For new rows inserted, the value is initialized to 0 and then incremented on every update operation.
CREATE FUNCTION trigfunc_modcount() RETURNS trigger AS $$
switch $TG_op {
INSERT {
set NEW($1) 0
}
UPDATE {
set NEW($1) $OLD($1)
incr NEW($1)
}
default {
return OK
}
}
return [array get NEW]
$$ LANGUAGE pltcl;
CREATE TABLE mytab (num integer, description text, modcnt integer);
CREATE TRIGGER trig_mytab_modcount BEFORE INSERT OR UPDATE ON mytab
FOR EACH ROW EXECUTE FUNCTION trigfunc_modcount('modcnt');
Notice that the trigger function itself does not know the column name; that's supplied from the trigger arguments. This lets the trigger function be reused with different tables.