Consider a brokerage firm database with relations Holdings(AccountId, StockSymbol, Price, Quantity) and Balance(AccountId, Balance). Write the triggers for maintaining the correctness of the account balance when stock is bought (a tuple is added to Holdings or Quantity is incremented) or sold (a tuple is deleted from Holdings or Quantity is decremented).

Write both row level and statement level triggers.

CREATE TRIGGER UpdateBalanceRealTime

AFTER INSERT, DELETE, UPDATE ON Holdings

REFERENCING NEW AS N

FOR EACH ROW

UPDATE Balance

SET Balance =

(SELECT SUM(H.Price*H.Quantity)

FROM Holdings H

WHERE H.AccountId = N.AccountId )

The above trigger is appropriate for real-time updates of Holdings, so the balances are also updated in real time. If Holdings is updated only periodically (e.g., every day), then a statement level trigger would be more ecient. This trigger can work by erasing the old contents of Balance and then recomputing it from scratch:

CREATE TRIGGER UpdateBalanceAllAtOnce

AFTER INSERT, DELETE, UPDATE ON Holdings

FOR EACH STATEMENT

BEGIN

DELETE FROM Balance; -- Erase

INSERT INTO Balance

SELECT DISTINCT H.AccountId, SUM(H.Price*H.Quantity)

FROM Holdings H

GROUP BY H.AccountId

END