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, UPDATE command, or 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 type?OPAQUE. (I'll talk more about the information returned by a trigger in a moment.)

CREATE FUNCTION archive_customer() RETURNS OPAQUE 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.2 contains a complete list of the predefined variables that you can use inside of a trigger function:

^[9] 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 trigger?each trigger has a name?in 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)];

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.