Understanding Triggers

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

Unlike stored procedures (which are simply stored SQL statements), triggers are tied to individual tables. A trigger associated with INSERT operations on the Orders table will only be executed when a row is inserted into the Orders table. Similarly, a trigger on INSERT and UPDATE operations on the Customers table will only be executed 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 can be executed before or after a specified operation is performed.

Triggers have three primary uses:

• Ensuring data consistency—for example, converting all states to uppercase during INSERT or UPDATE operations

• Performing actions on other tables based on changes to a table—for 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 needed—for example, making sure a customer's available credit has not been exceeded and blocking the insertion if it has

• Calculating computed column values or update timestamps

As you probably expect by now, trigger creation syntax varies dramatically from one DBMS to another. Check your documentation for more details.

The following example creates a trigger that converts the cust_state on the Customers table field to uppercase on all INSERT and UPDATE operations. 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;