Examples of defining actions using triggers

Assume that your general manager wants to keep the names of customers who have sent three or more complaints in the last 72 hours in a separate table. The general manager also wants to be informed whenever a customer name is inserted in this table more than once.

To define such actions, you define:

An UNHAPPY_CUSTOMERS table:

     CREATE TABLE UNHAPPY_CUSTOMERS ( 
       NAME           VARCHAR (30), 
       EMAIL_ADDRESS  VARCHAR (200), 
       INSERTION_DATE DATE)

A trigger to automatically insert a row in UNHAPPY_CUSTOMERS if 3 or more messages were received in the last 3 days (assumes the existence of a CUSTOMERS table that includes a NAME column and an E_MAIL_ADDRESS column):

     CREATE TRIGGER STORE_UNHAPPY_CUST 
       AFTER INSERT ON ELECTRONIC_MAIL 
       REFERENCING NEW AS N 
       FOR EACH ROW  
       WHEN (3 <= (SELECT COUNT(*) 
                   FROM ELECTRONIC_MAIL 
                   WHERE SENDER = N.SENDER 
                     AND SENDING_DATE(MESSAGE) > CURRENT DATE - 3 DAYS) 
            ) 
       BEGIN ATOMIC 
         INSERT INTO UNHAPPY_CUSTOMERS 
         VALUES ((SELECT NAME 
         FROM CUSTOMERS 
         WHERE EMAIL_ADDRESS = N.SENDER), N.SENDER, CURRENT DATE); 
       END

A trigger to send a note to the general manager if the same customer is inserted in UNHAPPY_CUSTOMERS more than once (assumes the existence of a SEND_NOTE function that takes 2 character strings as input):

     CREATE TRIGGER INFORM_GEN_MGR 
       AFTER INSERT ON UNHAPPY_CUSTOMERS 
       REFERENCING NEW AS N 
       FOR EACH ROW 
       WHEN (1 <(SELECT COUNT(*) 
                 FROM UNHAPPY_CUSTOMERS 
                 WHERE EMAIL_ADDRESS = N.EMAIL_ADDRESS) 
            ) 
       BEGIN ATOMIC 
         VALUES(SEND_NOTE('Check customer:' CONCAT N.NAME, 
                          'bigboss@vnet.ibm.com')); 
       END