CREATE TRIGGER Sample SQL - IBM Database Add-Ins for Visual Studio

The following examples show sample SQL code for creating different types of DB2^® triggers.

Example 1

Create two triggers that will result in the automatic tracking of the number of employees a company manages. The triggers will interact with the following tables:

EMPLOYEE table with these columns: ID, NAME, ADDRESS, and POSITION.
COMPANY_STATS table with these columns: NBEMP, NBPRODUCT, and REVENUE.

The first trigger increments the number of employees each time a new person is hired; that is, each time a new row is inserted into the EMPLOYEE table:


   CREATE TRIGGER NEW_HIRED
     AFTER INSERT ON EMPLOYEE
     FOR EACH ROW
     UPDATE COMPANY_STATS SET NBEMP = NBEMP + 1

The second trigger decrements the number of employees each time an employee leaves the company; that is, each time a row is deleted from the table EMPLOYEE:


   CREATE TRIGGER FORMER_EMP
     AFTER DELETE ON EMPLOYEE
     FOR EACH ROW
     UPDATE COMPANY_STATS SET NBEMP = NBEMP - 1

Example 2

Create a trigger that ensures that whenever a parts record is updated, the following check and (if necessary) action is taken:

If the on-hand quantity is less than 10% of the maximum stocked quantity, then issue a shipping request ordering the number of items for the affected part to be equal to the maximum stocked quantity minus the on-hand quantity.

The trigger will interact with the PARTS table with these columns: PARTNO, DESCRIPTION, ON_HAND, MAX_STOCKED, and PRICE.

ISSUE_SHIP_REQUEST is a user-defined function that sends an order form for additional parts to the appropriate company.


   CREATE TRIGGER REORDER
     AFTER UPDATE OF ON_HAND, MAX_STOCKED ON PARTS
     REFERENCING NEW AS N
     FOR EACH ROW
     WHEN (N.ON_HAND < 0.10 * N.MAX_STOCKED)
     BEGIN ATOMIC
     VALUES(ISSUE_SHIP_REQUEST(N.MAX_STOCKED - N.ON_HAND, N.PARTNO));
     END

Example 3

Create a trigger that will cause an error when an update occurs that would result in a salary increase greater than ten percent of the current salary.


   CREATE TRIGGER RAISE_LIMIT
     AFTER UPDATE OF SALARY ON EMPLOYEE
     REFERENCING NEW AS N OLD AS O
     FOR EACH ROW
     WHEN (N.SALARY > 1.1 * O.SALARY)
            SIGNAL SQLSTATE '75000' SET MESSAGE_TEXT='Salary increase>10%'

Example 4

Consider an application which records and tracks changes to stock prices. The database contains two tables, CURRENTQUOTE and QUOTEHISTORY.

   Tables: CURRENTQUOTE (SYMBOL, QUOTE, STATUS)
           QUOTEHISTORY (SYMBOL, QUOTE, QUOTE_TIMESTAMP)

When the QUOTE column of CURRENTQUOTE is updated, the new quote should be copied, with a timestamp, to the QUOTEHISTORY table. Also, the STATUS column of CURRENTQUOTE should be updated to reflect whether the stock is:

   1. rising in value
   2. at a new high for the year
   3. dropping in value
   4. at a new low for the year
   5. steady in value

CREATE TRIGGER statements that accomplish this are as follows.

Trigger Definition to set the status:


   CREATE TRIGGER STOCK_STATUS
     NO CASCADE BEFORE UPDATE OF QUOTE ON CURRENTQUOTE
     REFERENCING NEW AS NEWQUOTE OLD AS OLDQUOTE
     FOR EACH ROW
     BEGIN ATOMIC
        SET NEWQUOTE.STATUS =
          CASE
             WHEN NEWQUOTE.QUOTE >
                   (SELECT MAX(QUOTE) FROM QUOTEHISTORY
                   WHERE SYMBOL = NEWQUOTE.SYMBOL
                   AND YEAR(QUOTE_TIMESTAMP) = YEAR(CURRENT DATE) )
                THEN 'High'
             WHEN NEWQUOTE.QUOTE <
                   (SELECT MIN(QUOTE) FROM QUOTEHISTORY
                   WHERE SYMBOL = NEWQUOTE.SYMBOL
                   AND YEAR(QUOTE_TIMESTAMP) = YEAR(CURRENT DATE) )
                THEN 'Low'
             WHEN NEWQUOTE.QUOTE > OLDQUOTE.QUOTE
                THEN 'Rising'
             WHEN NEWQUOTE.QUOTE < OLDQUOTE.QUOTE
                THEN 'Dropping'
             WHEN NEWQUOTE.QUOTE = OLDQUOTE.QUOTE
                THEN 'Steady'
          END;
     END

Trigger Definition to record change in QUOTEHISTORY table:


   CREATE TRIGGER RECORD_HISTORY
     AFTER UPDATE OF QUOTE ON CURRENTQUOTE
     REFERENCING NEW AS NEWQUOTE
     FOR EACH ROW
     BEGIN ATOMIC
       INSERT INTO QUOTEHISTORY
         VALUES (NEWQUOTE.SYMBOL, NEWQUOTE.QUOTE, CURRENT TIMESTAMP);
     END

CREATE TRIGGER Sample SQL (DB2)

Example 1

Example 2

Example 3

Example 4

See Also