Triggers

So far, all the functions that defined in this chapter have been called explicitly, either by using a SELECT function() command or by using the function within an expression. You can also call certain PL/pgSQL functions automatically. A trigger is a function that is called whenever a specific event occurs in a given table. An INSERT command, an UPDATE command, or a DELETE command can cause a trigger to execute.

Let's look at a simple example. You currently have a customers table defined like this:

CREATE TABLE customers 
(
 customer_id integer primary key,
 customer_name character varying(50) not null,
 phone character(8),
 birth_date date,
 balance decimal(7,2)
);

You want to create a new table that you can use to archive any rows that are deleted from the customers table. You also want to archive any updates to the customers table. Name this table customer_archive:

CREATE TABLE customer_archive
(
 customer_id integer,
 customer_name character varying(50) not null,
 phone character(8),
 birth_date date,
 balance decimal(7,2),
 user_changed varchar,
 date_changed date,
 operation varchar
);

Each row in the customer_archive table contains a complete customers record plus a few pieces of information about the modification that took place.

Now, let's create a trigger function that executes whenever a change is made to a row in the customers table. A trigger function is a function that takes no arguments and returns a special data typeTRIGGER. (I'll talk more about the information returned by a trigger in a moment.)

CREATE FUNCTION archive_customer() RETURNS TRIGGER AS '
 BEGIN
 INSERT INTO customer_archive 
 VALUES
 (
 OLD.customer_id,
 OLD.customer_name,
 OLD.phone,
 OLD.birth_date,
 OLD.balance,
 CURRENT_USER,
 now(),
 TG_OP
 );
 RETURN NULL;
 END;
' LANGUAGE 'plpgsql';

Notice that I am using a variable in this function that I have not declared: OLD. trigger functions have access to several predefined variables that make it easier to find information about the context in which the trigger event occurred. The OLD variable contains a copy of the original row when a trigger is executed because of an UPDATE or DELETE command. The NEW variable contains a copy of the new row when a trigger is executed for an UPDATE or INSERT command.

When this trigger executes, it creates a new row in the customer_archive() table. The new row will contain a copy of the original customers row, the name of the user making the modification, the date that the modification was made, and the type of operation: TG_OP will be set to 'UPDATE', 'INSERT', or 'DELETE'.

Table 7.3 contains a complete list of the predefined variables that you can use inside of a trigger function:

Table 7.3. Predefined Trigger Variables

Name	Type	Description
NEW	%ROWTYPE	New values (for UPDATE and INSERT)
OLD	%ROWTYPE	Old values (for UPDATE and DELETE)
TG_NAME	name	Name of trigger
TG_WHEN	text	BEFORE or AFTER
TG_LEVEL	text	ROW or STATEMENT[7]
TG_OP	text	INSERT, UPDATE, or DELETE
TG_RELID	Oid	Object ID of trigger table
TG_RELNAME	name	Name of trigger table
TG_NARGS	integer	Count of the optional arguments given to the CREATE TRIGGER command
TG_ARGV[]	text[]	Optional arguments given to the CREATE TRIGGER command

[7] Statement triggers are not supported in PostgreSQL, so TG_LEVEL will always be set to ROW.

Now that you have created a function, you have to define it as a trigger function. The CREATE TRIGGER command associates a function with an event (or events) in a given table. Here is the command that you use for the archive_customer() function:

1 CREATE TRIGGER archive_customer 
2 AFTER DELETE OR UPDATE 
3 ON customers
4 FOR EACH ROW 
5 EXECUTE PROCEDURE archive_customer();

This is a rather unwieldy command, so let's look at it one line at a time.

The first line tells PostgreSQL that you want to create a new triggereach trigger has a namein this case, archive_customer. trigger names must be unique within each table (in other words, I can have two triggers named foo as long as the triggers are defined for two different tables). Inside the trigger function, the TG_NAME variable holds the name of the trigger.

Line 2 specifies the event (or events) that cause this trigger to fire. In this case, I want the trigger to occur AFTER a DELETE command or an UPDATE command. Altogether, PostgreSQL can fire a trigger BEFORE or AFTER an UPDATE command, an INSERT command, or a DELETE command. In the trigger function, TG_WHEN is set to either BEFORE or AFTER, and TG_OP is set to INSERT, UPDATE, or DELETE.

Line 3 associates this trigger with a specific table. This is not an optional clause; each trigger must be associated with a specific table. You can't, for example, define a trigger that will execute on every INSERT statement regardless of the table involved. You can use the TG_RELNAME variable in the trigger function to find the name of the associated table. TG_RELOID holds the object-ID (OID) of the table.

A single DELETE or UPDATE statement can affect multiple rows. The FOR EACH clause determines whether a trigger will execute once for each row or once for the entire statement. PostgreSQL does not support statement-level triggers at the moment, so the only choice is FOR EACH ROW. Inside of the trigger function, TG_LEVEL can contain either ROW or STATEMENT; but the only value currently implemented is ROW.

Line 5 finally gets around to telling PostgreSQL which function you actually want to execute when the specified events occur.

The full syntax for the CREATE TRIGGER command is

CREATE TRIGGER trigger-name 
 [BEFORE | AFTER] [ INSERT | DELETE | UPDATE [OR ...]]
 ON table-name FOR EACH ROW
 EXECUTE PROCEDURE function-name [(args)];

 

TRIGGER Return Values

A trigger function can return a value just like any other function, but the value that you return can have far-reaching consequences. If you return NULL from a row-level BEFORE trigger, PostgreSQL cancels the rest of the operation for that rowthat means that PostgreSQL won't fire any subsequent triggers and the INSERT, UPDATE, or DELETE won't occur for that row. If you return a non-NULL value from a row-level BEFORE TRigger, the value that you return must match the structure of the table that you're modifying. If PostgreSQL is executing an UPDATE or INSERT command, the row value that you return from the trigger function is used in place of the original value.

PostgreSQL ignores the return value of an AFTER TRigger. PostgreSQL also ignores the return value of a statement-level BEFORE trigger.

trIGGER Function Arguments

Notice that the CREATE TRIGGER command allows you to specify optional arguments (indicated by args in the preceding syntax diagram). You can include a list of string literals when you create a trigger (any arguments that are not of string type are converted into strings). The arguments that you specify are made available to the trigger function through the TG_NARGS and TG_ARGV variables. TG_NARGS contains an integer count of the number of arguments. TG_ARGV contains an array of strings corresponding to the values that you specified when you created the trigger: TG_ARGV[0] contains the first argument, TG_ARGV[1] contains the second argument, and so on. You can use the optional trigger arguments to pass extra information that might help the trigger function know more about the context in which the trigger has executed. You might find this useful when using the same function as a trigger for multiple tables; although in most situations, the TG_NAME, TG_RELNAME, and TG_OP variables provide enough context information.