5.6

Consider the bank database of Figure 5.21. Let us define a view branch_cust as follows:

CREATE VIEW branch_cust AS 
    SELECT branch_name, customer_name
    FROM depositor, account
    WHERE depositor.account_number = account.account_number

Suppose that the view is materialized; that is, the view is computed and stored. Write triggers to maintain the view, that is, to keep it up-to-date on insertions to depositor or account. It is not necessary to handle deltions or updates. Note that, for simplicity, we have not required elimination of duplicates.

---

CREATE TRIGGER insert_into_branch_cust_via_depositor
AFTER INSERT ON depositor
REFERENCING NEW ROW AS inserted
FOR EACH ROW
INSERT INTO branch_cust
    SELECT branch_name, inserted.customer_name
    FROM account
    WHERE inserted.account_number = account.account_number;

CREATE TRIGGER insert_into_branch_cust_via_account
AFTER INSERT ON account
REFERENCING NEW ROW AS inserted
FOR EACH STATEMENT
INSERT INTO branch_cust
    SELECT inserted.branch_name,customer_name
    FROM depositor
    WHERE depositor.account_number = inserted.account_number;