Creating Custom Triggers

There is a certain degree of overlap between stored procedures (that is, functions) and triggers. Both operate as predefined code created with the CREATE FUNCTION statement. However, triggers are most often used as an automated response to some table-related event, not as an action directly called by a client application.

Triggers bind these functions to DELETE, UPDATE, or INSERT table events using the CREATE TRIGGER command. The client application has no direct knowledge of a trigger's existence; it simply performs the requested action, which results in the server firing the appropriate trigger event.

Triggers are used for performing actions that pertain to the same table that is being accessed. Often they are used as a mechanism to ensure data or business-rule integrity. For instance, consider the following function and trigger pair:

CREATE FUNCTION trig_insert_update_check_emp() RETURNS opaque AS 
      'BEGIN 
            /*Check employee age, state, and name 
            and enforce certain checks */ 
            IF new.age > 20 THEN 
                  new.adult = 'yes'; 
            ELSE 
                  new.adult = 'no'; 
            END IF; 
            IF new.state ~ '^[A-Za-z][A-Za-z]$' THEN 
                  new.state = upper(new.state); 
            ELSE 
                  RAISE EXCEPTION 'Alphabetical State Desc Only'; 
            END IF; 
            IF new.name ~ '^[a-zA-Z]*' THEN 
                  new.name = initcap(new.name); 
            ELSE 
                     RAISE EXCEPTION 'Alphabetical Name Only'; 
            END IF; 
      END; 
' LANGUAGE 'plpgsql'; 
CREATE FUNCTION trig_delete_check_emp() RETURNS opaque AS 
      'BEGIN 
            /*Make sure a manager isn't deleted*/ 
            IF old.manager='yes' THEN 
                 RAISE EXCEPTION 'Cannot Delete Managers!' 
            END IF; 
      END; 
' LANGUAGE 'plpgsql'; 

The preceding two functions make use of the new and old keywords. These keywords refer to data that has just been INSERTED or DELETED, respectively, when called as part of a trigger event. Next, a trigger event is created and bound to each function.

CREATE TRIGGER employee_insert_update 
      BEFORE INSERT OR UPDATE 
      ON employee 
      FOR EACH ROW EXECUTE PROCEDURE trig_insert_update_check_emp(); 

CREATE TRIGGER employee_update 
      BEFORE DELETE 
      ON employee 
      FOR EACH ROW EXECUTE PROCEDURE trig_delete_check_emp(); 

Now that the triggers have been created, they can be tested as follows:

>INSERT INTO employee (name, age, state, manager) 
      VALUES ('sean', 29, 'T8', 'yes'); 
>ERROR: Alphabetical State Desc Only 

>INSERT INTO employee (name, age, state, manager) 
      VALUES ('sean', 29, 'tx', 'yes'); 
>INSERT 323003 1 

>SELECT * FROM employee WHERE name='Sean'; 
name   age   state   manager   adult 
------------------------------------
Sean   29    TX      yes       yes 

>DELETE FROM employee WHERE name='Sean'; 
>ERROR: Cannot Delete Managers! 

>UPDATE employee SET manager='no' WHERE name='Sean'; 
>UPDATE 1 

>DELETE FROM employee WHERE name='Sean'; 
>DELETE 1 

In the preceding examples, notice the similarity between how these triggers behave and how column constraints typically behave. Column constraints generally check a specific field's validity before an INSERT or UPDATE is allowed.

However, triggers and column constraints are not mutually exclusive in their behavior. If the BEFORE keyword is used when creating a trigger, it will fire before the field (or table) constraints are checked. Moreover, the BEFORE keyword means that the trigger will be fired before the actual insert is completed. Therefore, if a trigger depends on an OID or relies on a unique index, it will not function correctly.

Likewise, when the AFTER keyword is specified, the trigger event will be activated after the specified table action (INSERT, UPDATE, or DELETE) has already completed. Moreover, the AFTER keyword will cause the trigger not to fire until all the table or field constraints have already been evaluated.