Triggers


Triggers are special stored procedures that are executed automatically when specific database activity occurs. Triggers may be associated with INSERT, UPDATE, and DELETE operations on specific tables (or any combination thereof).

Unlike stored procedures (which are simply stored SQL statements), triggers are tied to individual tables. A trigger associated with INSERT operations on an Orders table will be executed only when a row is inserted into that Orders table. Similarly, a trigger on INSERT and UPDATE operations on a Customers table will be executed only when those specific operations occur on that table.

Within triggers, your code has access to the following:

  • All new data in INSERT operations

  • All new data and old data in UPDATE operations

  • Deleted data in DELETE operations

Depending on the DBMS being used, triggers may be executed before or after the specified operation is performed.

Triggers have three primary uses:

  • Ensuring data consistencyfor example, converting all states to uppercase during INSERT and UPDATE operations

  • Performing actions on other tables based on changes to a tablefor example, writing an audit trail record to a log table each time a row is updated or deleted

  • Performing additional validation and rolling back data if neededfor example, making sure that a customer's available credit has not been exceeded, and blocking the insertion if it has

Trigger creation syntax varies from one database to another. The following example creates a trigger that converts the cust_state field to uppercase on all INSERT and UPDATE operations on a Customers table. This is the SQL Server version:

 CREATE TRIGGER customer_state ON Customers FOR INSERT UPDATE AS UPDATE Customers SET cust_state = Upper(cust_state) WHERE Customers.cust_id = inserted.cust_id 

This is the Oracle version:

 CREATE TRIGGER customer_state AFTER INSERT UPDATE FOR EACH ROW BEGIN  UPDATE Customers  SET cust_state = Upper(cust_state)  WHERE Customers.cust_id = :OLD.cust_id END; 

TIP

As a rule, constraints are processed quicker than triggers, so whenever possible use constraints instead.




Macromedia ColdFusion MX 7 Certified Developer Study Guide
Macromedia ColdFusion MX 7 Certified Developer Study Guide
ISBN: 0321330110
EAN: 2147483647
Year: 2004
Pages: 389
Authors: Ben Forta

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