Creating Custom Triggers

I l @ ve RuBoard

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.

I l @ ve RuBoard


PostgreSQL Essential Reference
PostgreSQL Essential Reference
ISBN: 0735711216
EAN: 2147483647
Year: 2001
Pages: 118
Authors: Barry Stinson

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net