Trigger functions can be created with PL/pgSQL and referenced within a PostgreSQL trigger
definition. The term "trigger function" is a simply a way of referring to a function that is intended to be invoked by a
trigger. Triggers define operations that are performed when a specific event occurs within the database. A PL/pgSQL trigger
function can be referenced by a trigger as the operation to be performed when the trigger's event occurs.
The definition of a trigger and the definition of its associated trigger function are two different things. A trigger
is defined with the SQL CREATE TRIGGER command, whereas trigger functions are defined using
the SQL CREATE FUNCTION command. Trigger definitions are explained in detail in Chapter 7.
A trigger function should be defined as accepting no arguments, and returns a value of the special
opaque data type. The CREATE FUNCTION syntax for defining
a PL/pgSQL trigger function is shown in Example 11-52.
Example 11-52. Creating trigger functions
CREATE FUNCTION function_identifier () RETURNS opaque AS '
DECLARE
declarations;
[...]
BEGIN
statements;
[...]
END;
' LANGUAGE 'plpgsql';
Every trigger function created has access to a number of special variables that exist to provide information about the
calling trigger, and to allow the trigger function to manipulate table data. All special trigger function variables are
listed in Table 11-2.
Table 11-2. Trigger function variables
Name | Data type | Description |
---|
NEW | RECORD | Contains the new database row created after INSERT and UPDATE operations run by ROW level triggers. Use this variable to make modifications to the new row. |
OLD | RECORD | Contains the old database row left after UPDATE AND DELETE operations performed by ROW level triggers. |
TG_NAME | name | Contains the name of the fired trigger. |
TG_WHEN | text | Contains either a BEFORE or AFTER string, depending on whether the trigger was defined as running after or before its specified event. |
TG_LEVEL | text | Contains either a ROW or STATEMENT string, depending on the defined level of the trigger. |
TG_OP | text | Contains an INSERT, UPDATE, or DELETE string that indicates the operation the trigger is invoked on. |
TG_RELID | oid | Contains the object ID of the table that invoked the trigger. |
TG_RELNAME | name | Contains the name of the table for which the trigger was invoked. |
TG_NARGS | integer | Contains the number of arguments the trigger's definition specifies the trigger function as having. |
TG_ARGV[ ] | array of text | Contains the arguments specified by the CREATE TRIGGER statement. The array index begins at zero. |
Example 11-53 illustrates the definition of a PL/pgSQL trigger function and demonstrates the
usage of the previously listed special variables. The check_shipment_addition trigger
function is called after an INSERT or UPDATE operation is
performed upon the shipments table.
The check_shipment_addition() function checks to make sure each added shipment
contains a valid customer ID number and a valid ISBN for the book specified. It then subtracts one from the total amount of
stock in the stock table for the specified book if the calling SQL operation is an
INSERT statement (but not an UPDATE
statement).
Example 11-53. The check_shipment_addition() PL/pgSQL trigger function
CREATE FUNCTION check_shipment_addition () RETURNS opaque AS '
DECLARE
-- Declare a variable to hold the customer ID.
id_number INTEGER;
-- Declare a variable to hold the ISBN.
book_isbn TEXT;
BEGIN
-- If there is an ID number that matches the customer ID in
-- the new table, retrieve it from the customers table.
SELECT INTO id_number id FROM customers WHERE id = NEW.customer_id;
-- If there was no matching ID number, raise an exception.
IF NOT FOUND THEN
RAISE EXCEPTION ''Invalid customer ID number.'';
END IF;
-- If there is an ISBN that matches the ISBN specified in the
-- new table, retrieve it from the editions table.
SELECT INTO book_isbn isbn FROM editions WHERE isbn = NEW.isbn;
-- If there is no matching ISBN, raise an exception.
IF NOT FOUND THEN
RAISE EXCEPTION ''Invalid ISBN.'';
END IF;
-- If the previous checks succeeded, update the stock amount
-- for INSERT commands.
IF TG_OP = ''INSERT'' THEN
UPDATE stock SET stock = stock -1 WHERE isbn = NEW.isbn;
END IF;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
Once the check_shipment_addition() function has been created, a trigger may be set on
the shipments table to call it. Example 11-54 shows the syntax to
create the check_shipment trigger in the booktown database
from within psql.
Example 11-54. The check_shipment trigger
booktown=# CREATE TRIGGER check_shipment
booktown-# BEFORE INSERT OR UPDATE
booktown-# ON shipments FOR EACH ROW
booktown-# EXECUTE PROCEDURE check_shipment_addition();
CREATE
Note that the check_shipment_addition trigger function must be
defined within the booktown database before its associated trigger is defined. Always
define trigger functions before defining the triggers that reference them.
See Chapter 7 for more in-depth information on triggers.