Consider the relational schema shown in Figure 26.18. Write active rules for keeping the SUM_COMMISSIONS attribute of SALES_PERSON equal to the sum of the COMMISSION attribute in SALES for each sales person. Your rules should also check if the SUM_COMMISSIONS exceeds 100000; if it does, call a procedure NOTIFY_MANAGER(S_ID). Write both statement-level rules in STARBURST notation and row-level rules in Oracle.

Question

What will be an ideal response?

Answer 1

Oracle notation rules:
CREATE TRIGGER KEEP_EM_SAME
AFTER INSERT OR UPDATE OF COMMISION ON SALES
FOR EACH ROW
UPDATE SALES_PERSON
SET SUM_COMISSIONS = (SELECT SUM (COMISSION)
FROM SALES
WHERE S_ID = NEW.S_ID);
CREATE TRIGGER NOTIFY_MANAGEMENT
AFTER INSERT OR UPDATE OF SUM_COMISSIONS OF SALES_PERSON
FOR EACH ROW
WHEN ((SELECT SUM(COMISSION)
FROM SALES_PERSON
WHERE SALES_PERSON_ID = NEW.SALES_PERSON_ID) >
100000
NOTIFY_MANAGER(NEW.SALES_PERSON_ID);
Starburst notation rules:
CREATE RULE KEEP_EM_SAME ON SALES
WHEN INSERTED OR UPDATED(COMISSION)
THEN UPDATE SALES_PERSON AS S
SET S.SUM_COMISSIONS = ( SELECT SUM(COMISSIONS)
FROM SALES AS L
WHERE S.S_ID = L.S_ID)
WHERE S.SALESPERSON_ID IN ((SELECT S_ID FROM UPDATED)
OR (SELECT S_ID FROM INSERTED));
CREATE RULE NOTIFY_MANAGEMENT ON SALES_PERSON
WHEN INSERTED OR UPDATED(SUM_COMISSIONS)
THEN NOTIFY_MANAGER(SELECT SALESPERSON_ID
FROM SALES_PERSON AS S
WHERE S.SUM_COMISSIONS > 100000)
AND
S.SALESPERSON_ID IN
((SELECT SALESPERSON_ID FROM
UPDATED)
OR (SELECT SALESPERSON_ID FROM
INSERTED)));
We assumed that there would be no deletions of sales. If deletion of sales were a
possibility, we would simply enhance the above rules to accommodate the delete option as
well.