Example of defining business rules using triggers

Suppose your company has the policy that all email dealing with customer complaints must have Mr. Nelson, the marketing manager, in the carbon copy (CC) list.

Because this is a rule, you might want to express it as a constraint such as one of the following (assuming the existence of a CC_LIST UDF to check it):

     ALTER TABLE ELECTRONIC_MAIL ADD 
       CHECK (SUBJECT <> 'Customer complaint' OR 
              CONTAINS (CC_LIST(MESSAGE), 'nelson@vnet.ibm.com') = 1)

However, such a constraint prevents the insertion of email dealing with customer complaints that do not have the marketing manager in the cc list. This is certainly not the intent of your company's business rule. The intent is to forward to the marketing manager any email dealing with customer complaints that were not copied to the marketing manager. Such a business rule can only be expressed with a trigger because it requires taking actions that cannot be expressed with declarative constraints. The trigger assumes the existence of a SEND_NOTE function with parameters of type E_MAIL and character string.

     CREATE TRIGGER INFORM_MANAGER 
       AFTER INSERT ON ELECTRONIC_MAIL 
       REFERENCING NEW AS N 
       FOR EACH ROW  
       WHEN (N.SUBJECT = 'Customer complaint' AND 
         CONTAINS (CC_LIST(MESSAGE), 'nelson@vnet.ibm.com') = 0) 
       BEGIN ATOMIC 
         VALUES(SEND_NOTE(N.MESSAGE, 'nelson@vnet.ibm.com')); 
       END