Combining UDTs, UDFs, and LOBs describes how to combine UDTs, UDFs, and LOBs to represent and manipulate large, complex structures. The following section describes how to exploit triggers and constraints to complete the modeling of such application structures. With triggers, you can:
In Combining UDTs, UDFs, and LOBs, the complete electronic mail is stored within the column message of the ELECTRONIC_MAIL table. To manipulate the electronic mail, UDFs were used to extract information from the message column every time such information was required within an SQL statement.
Notice that the queries do not extract information once and store it explicitly as columns of tables. If this was done, it would increase the performance of the queries, not only because the UDFs are not invoked repeatedly, but also because you can then define indexes on the extracted information.
Using triggers, you can extract this information whenever new electronic mail is stored in the database. To achieve this, add new columns to the ELECTRONIC_MAIL table and define a BEFORE trigger to extract the corresponding information as follows:
ALTER TABLE ELECTRONIC_MAIL
ADD COLUMN SENDER VARCHAR (200)
ADD COLUMN RECEIVER VARCHAR (200)
ADD COLUMN SENT_ON DATE
ADD COLUMN SUBJECT VARCHAR (200)
CREATE TRIGGER EXTRACT_INFO
NO CASCADE BEFORE INSERT ON ELECTRONIC_MAIL
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
SET N.SENDER = SENDER(N.MESSAGE);
SET N.RECEIVER = RECEIVER(N.MESSAGE);
SET N.SENT_ON = SENDING_DATE(N.MESSAGE);
SET N.SUBJECT = SUBJECT(N.MESSAGE);
END
Now, whenever new electronic mail is inserted into the message column, its sender, its receiver, the date on which it was sent, and its subject are extracted from the message and stored in separate columns.
Suppose you want to prevent mail you sent, which was undelivered and returned to you (perhaps because the e-mail address was incorrect), from being stored in the e-mail's table.
To do so, you need to prevent the execution of certain SQL INSERT statements. There are two ways to do this:
CREATE TRIGGER BLOCK_INSERT
NO CASCADE BEFORE INSERT ON ELECTRONIC_MAIL
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
WHEN (SUBJECT(N.MESSAGE) = 'undelivered mail')
BEGIN ATOMIC
SIGNAL SQLSTATE '85101' ('Attempt to insert undelivered mail');
END
ALTER TABLE ELECTRONIC_MAIL
ADD CONSTRAINT NO_UNDELIVERED
CHECK (SUBJECT <> 'undelivered mail')
Because of the advantages of the declarative nature of constraints, the constraint should generally be defined instead of the trigger.
Suppose your company has the policy that all e-mail 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 e-mail 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 e-mail 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 MODE DB2SQL
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
Now 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:
CREATE TABLE UNHAPPY_CUSTOMERS (
NAME VARCHAR (30),
EMAIL_ADDRESS VARCHAR (200),
INSERTION_DATE DATE)
CREATE TRIGGER STORE_UNHAPPY_CUST
AFTER INSERT ON ELECTRONIC_MAIL
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
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 E_MAIL_ADDRESS = N.SENDER), N.SENDER, CURRENT DATE);
END
CREATE TRIGGER INFORM_GEN_MGR
AFTER INSERT ON UNHAPPY_CUSTOMERS
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
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