Functions can be defined to automatically start running when an action such as INSERT / UPDATE / DELETE occurs. This is performed through a trigger. The trigger invokes a named function when an INSERT / UPDATE / DELETE operation occurs on the specified table.
Triggers can be defined with the CREATE FUNCTION command. It is declared without any arguments and with the return type OPAQUE . For more information, refer to the section called Triggers in Chapter 5
This table lists some special variables that are used with triggers to manipulate the table data values:
|NEW||RECORD||holds the new database row for ROW level triggers on INSERT / UPDATE operations|
|OLD||RECORD||holds the old database row for ROW level triggers on UPDATE / DELETE operations|
|TG_NAME||name||contains the name of the fired trigger|
|TG_WHEN||text||a BEFORE or AFTER string, depending on the trigger definition|
|TG_LEVEL||text||a ROW or STATEMENT string, depending on the trigger definition|
|TG_OP||text||an INSERT , UPDATE , or DELETE string which specifies the operation that will invoke the trigger.|
|TG_RELID||oid||object identifier of the table invoking the trigger|
|TG_RELNAME||name||name of the table invoking the trigger|
|TG_NARGS||integer||number of arguments defined by the CREATE TRIGGER statement|
|TG_ARGV||array of text||the arguments specified by the CREATE TRIGGER statement.|
You should keep in mind that this array contains an index that starts at 0. Any index specified that is less than 0 or greater than the TG_NARGS value causes the index to have a NULL value.
To better understand how these variables can be useful, we will create a function called check_a_id . This function checks the author identification numbers and makes sure that the number entered for the inserted or updated author is not left blank (which is NULL) or less than 100:
Example 9-29. PL/pgSQL check_a_id() Function used by a Trigger
CREATE FUNCTION check_a_id() RETURNS OPAQUE AS ' BEGIN -- checks to verify the author id is -- not blank or less than 100 -- if the inserted/updated author id is blank IF NEW.a_id ISNULL THEN -- prints a message and aborts the transaction RAISE EXCEPTION ''The author id cannot be left blank!''; ELSE -- if the newly inserted/updated author id -- is less than 100 IF NEW.a_id < 100 THEN -- prints a message and aborts the transaction RAISE EXCEPTION ''Please insert a valid author id.''; ELSE -- this displays the new database row -- which was held on an UPDATE/INSERT operation RETURN NEW; END IF; END IF; END; ' LANGUAGE 'plpgsql';
Example 9-30. The if_author_added Trigger
CREATE TRIGGER if_author_added BEFORE INSERT OR UPDATE ON author FOR EACH ROW EXECUTE PROCEDURE check_a_id();
Once an insert/update operation occurs, this trigger is invoked and the function is called. The following insert operation test the check_a_id () function:
INSERT INTO author values (23, 'test', 'test');
This error message was displayed:
ERROR: Please insert a valid author id.
Next, update the author table and make the author id equal to NULL:
UPDATE author SET a_id = NULL WHERE a_id = 136;
It should return this message:
ERROR: The author id cannot be left blank!
To illustrate an advanced function used by a trigger, we will look at an example that originated in the triggers section of this book. Refer to the section called Triggers in Chapter 5 for more details on triggers. This example is of a function that tracks user modifications to the inventory table. We want to get a date and the user name who modified the table. When an UPDATE occurs, we want to copy the original data into another table called the inventory_audit table. Then INSERT INTO inventory the new data, date, and user name who modified the table. When a DELETE occurs, we want to move what was in the original table into the inventory_audit table. The resulting function uses many of the special variables provided to deal with triggers:
Example 9-31. PL/pgSQL inv_track Function used by a Trigger
CREATE FUNCTION inv_track() RETURNS OPAQUE AS ' BEGIN -- if a trigger insert or update operation occurs IF TG_OP = ''INSERT'' OR TG_OP = ''UPDATE'' THEN -- assigns the current user name -- into the user_aud column NEW.user_aud := current_user; -- assigns the current timestamp -- into the mod_time column NEW.mod_time := ''NOW''; -- inserts the row in the inventory -- table into the inventory_audit table INSERT INTO inventory_audit SELECT * FROM inventory WHERE prod_id=NEW.prod_id; -- displays the new row on an insert/update RETURN NEW; -- else if a trigger delete operation occurs ELSE if TG_OP = ''DELETE'' THEN -- inserts the row to be deleted -- and the user and time the deletion occurred -- into the inventory_audit table INSERT INTO inventory_audit SELECT *, current_user, ''NOW'' FROM inventory WHERE prod_id=OLD.prod_id; -- displays the old row on an update/delete RETURN OLD; END IF; END IF; END; ' LANGUAGE 'plpgsql';
Example 9-32. The audit_track Trigger
CREATE TRIGGER audit_track BEFORE INSERT OR UPDATE OR DELETE ON inventory FOR EACH ROW EXECUTE PROCEDURE inv_track();