Triggers

SQL99

SQL99 only mandates triggers that automatically execute on DML events; here is the syntax to create a trigger:

CREATE TRIGGER <trigger_name>
[BEFORE | AFTER]
{INSERT | UPDATE | DELETE}
ON <table_name>
[FOR EACH ROW]
<trigger_body>

BEFORE and AFTER keywords specify whether the trigger fires off before or after the DML event actually takes place. In other words, if a particular trigger is designed to execute whenever a row is inserted into table A and the BEFORE keyword is specified, the trigger will fire off just before the RDBMS tries to perform the insert; if the trigger produced an error, the insert will never take place. The AFTER triggers execute upon the triggering DML statement completion; if an error occurs, the transaction will be rolled back.

Triggers with a FOR EACH ROW optional clause are sometimes referred as row triggers. A row trigger executes once for each row affected by a DML statement; for example if an UPDATE statement modifies column values in ten rows, the trigger will fire off ten times. If the clause is omitted, the trigger will only execute once no matter how many rows are affected (table trigger).

PL/SQL

Oracle's PL/SQL has all the options required by the SQL99 standards and many more. The following syntax includes only DML trigger clauses; in addition, PL/SQL allows you to create DDL and database triggers that fire off on specified DDLs (CREATE, ALTER, DROP, etc.) or database events (user logon/logoff, database startup/shutdown, and so on).

CREATE [OR REPLACE] TRIGGER [qualifier.]<trigger_name>
{BEFORE | AFTER | INSTEAD OF}
{INSERT | DELETE | UPDATE OF <column_name>,...} [OR],...
ON <table_or_view_name>
[REFERENCING OLD AS <name_for_old> NEW AS <name_for_new>]
[FOR EACH ROW]
[WHEN <condition> <sql_block>],... |
<trigger_body>

Like many other Oracle objects (stored procedures, functions, views, synonyms, sequences, etc.). PL/SQL syntax has an optional OR REPLACE clause (discussed in Chapter 4).

In addition to the BEFORE and AFTER keywords, INSTEAD OF could be specified (for views only); in that case Oracle performs actions coded in its body instead of executing the triggering event. That is especially useful when working with nonupdateable views (see Chapter 4) to simulate the updatable view behavior. For example, you cannot use a DML on a view that has a GROUP BY or DISTINCT clause in its definition; however, an INSTEAD OF trigger could include special logic that overrides that rule using procedural language constructs to execute appropriate DML statements.

Another advanced feature is the UPDATE OF clause. Unlike the SQL99 standard that only allows you to execute a trigger if any column of the triggered table is being updated, PL/SQL lets you specify update of which column (or a set of columns) fires off the trigger.

The REFERENCING clause allows you to specify alternative correlation names for OLD and NEW keywords used to prefix column names to access old (not modified) or new (modified) column values, correspondingly.

Some other PL/SQL advanced features include the ability to create triggers for multiple DML events and specify different actions for different events in the trigger body:

CREATE TRIGGER multi_action
BEFORE INSERT OR UPDATE ON my_table
...
WHEN INSERTING
   <code_to_execute_on_insert>
WHEN UPDATING
   <code_to_execute_on_update>
...

The following example creates trigger TRBU_PRODUCT that inserts a row into special auditing table PRODUCT_AUDIT each time PROD_PRICE_N value changes in PRODUCT table. The information includes the primary key of the modified row, the new and the old price values, the username of the person who modified the record, and the timestamp. The trigger fires off for every modified row in PRODUCT that meets the condition, so if someone issued an UPDATE statement that changes the price for five products, five rows would be inserted into PRODUCT_AUDIT.

CREATE OR REPLACE TRIGGER trbu_product
BEFORE  UPDATE OF prod_price_n ON product
FOR EACH ROW
BEGIN
    INSERT INTO  product_audit
    VALUES (:NEW.prod_id_n,
            :OLD.prod_price_n,
            :NEW.prod_price_n,
            USER,
            SYSDATE);
END;
/

DB2

DB2's procedural language has the following syntax to create a trigger:

CREATE TRIGGER <trigger_name>
[NO CASCADE BEFORE | AFTER]
{INSERT | DELETE | UPDATE [OF COLUMN <column_name>,...]}
ON <table_name>
[FOR EACH ROW MODE DB2SQL | FOR EACH STATEMENT]
[REFERENCING OLD AS <name_for_old> NEW AS <name_for_new>]
<trigger_body>

The syntax in general resembles SQL99 specifications with the exception of some minor details. For example, simply BEFORE is not good enough — you have to specify NO CASCADE BEFORE. Also, to create a row trigger, MODE DB2SQL keywords have to be added to the FOR EACH ROW clause.

The REFERENCING clause is similar to that of PL/SQL except even though the clause itself is optional, you have to specify alternative names for OLD and NEW if you want to use them in the trigger's body.

The trigger TRAU_PRODUCT created using the code in the following example has the same functionality as the trigger TRBU_PRODUCT described in the PL/SQL example earlier in this chapter:

CREATE TRIGGER trau_product
AFTER UPDATE OF prod_price_n ON product
REFERENCING NEW AS NNN OLD AS OOO
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
        INSERT INTO product_audit
        VALUES (NNN.prod_id_n,
                OOO.prod_price_n, 
                NNN.prod_price_n,
                USER,
                CURRENT DATE);
END
@

Transact-SQL

Here is the generalized Transact-SQL syntax for creating triggers:

CREATE TRIGGER <trigger_name>
ON <table_or_view>
{ FOR | AFTER | INSTEAD OF }
{INSERT | UPDATE | DELETE} 
AS 
[IF UPDATE ( <column_name> )
           [AND | OR UPDATE ( <column_name> ) ],...]
<trigger_body>

FOR is the Transact-SQL equivalent to the SQL99 keyword BEFORE.

Unlike PL/SQL, the INSTEAD OF clause can be specified for both table and view triggers.

The IF UPDATE clause allows you specify the updating of those columns (or combinations of columns) that cause the trigger to fire off.

The following syntax creates a trigger that functions identically to the PL/SQL one discussed in the previous section:

CREATE TRIGGER trbu_product ON product
FOR UPDATE
AS
IF UPDATE (prod_price_n)
    INSERT INTO product_audit
    SELECT  i.prod_id_n,
            d.prod_price_n,
            i.prod_price_n,
            USER,
            GETDATE()
     FROM   inserted i
       JOIN
            deleted d
       ON i.prod_id_n = d.prod_id_n

Several things in this example require explanation. Unlike PL/SQL or DB2 UDB, Transact-SQL does not have special structures OLD and NEW that hold the original and modified values of table columns. Instead, MS SQL Server provides two special virtual tables, DELETED (stores copies of the affected rows during DELETE and UPDATE operations) and INSERTED (holds copies of the affected rows during INSERT and UPDATE). We can join the two tables as shown above to get the values that have to be inserted into PRODUCT_AUDIT.