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:
Depending on the DBMS being used, triggers may be executed before or after the specified operation is performed. Triggers have three primary uses:
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. |