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.
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
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
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