Question

Consider the bank database of Figure 5.25. Write an SQL trigger to carry out the following action: On delete of an account, for each owner of the account, check if the owner has any remaining accounts, and if she does not, deleteher from the depositor relation

branch(branch name, branch city, assets)

customer (customer name, customer street, cust omer city)

loan (loan number, branch name, amount)

borrower (customer name, loan number) a

ccount (account number, branch name, balance )

depositor (customer name, account number)
Figure 5.25 Banking database

Answer 1

create trigger check-and-delete-trigger after delete on account

referencing old row as oldrow

for each row

delete from depositor

where depositor.customer name not in

( select customer name from depositor

where account number <> oldrow.account number )

end

Explanation:

This trigger (check-and-delete-trigger) first checks the customername in depositor and if the name is not found then it deletes from depositor

Answer 2

[REMOVED]