Triggers

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:

Table 9-1.

Name Data Type Description
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.

Note TG_ARGV[] array
 

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';
  

The function previously created will need a corresponding trigger to invoke that function. This trigger will call the check_a_id () function when an insert or update occurs on the author table:

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';
   

Then a trigger called audit_track is simply defined to call the inv_track() function:

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();