Consider a brokerage ?rm database with relations Holdings(AccountId, StockSymbol, CurrentPrice, 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), sold (a tuple is deleted from Holdings or Quantity is decremented), or a price change occurs. Solve the

Question

problem using both row-level and statement-level triggers. Give an example of a situation when row-level triggers are more appropriate for the above problem and when statement-level triggers are more appropriate.

What will be an ideal response?

Answer 1

```
CREATE TRIGGER UpdateBalanceRealTime
BEFORE AFTER INSERT, DELETE, UPDATE ON Holdings
REFERENCING NEW AS N
FOR EACH ROW
UPDATE Balance
SET Balance =
(SELECT SUM(H.CurrentPrice*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., everyday), then a statement level trigger would be more e?cient. This trigger can work by erasing the old contents of Balance and then recomputing it from scratch:

```
CREATE TRIGGER UpdateBalanceAllAtOnce
BEFORE AFTER INSERT, DELETE, UPDATE ON Holdings
FOR EACH STATEMENT
BEGIN
DELETE FROM Balance; -- Erase
INSERT INTO Balance
SELECT DISTINCT H.AccountId,SUM(H.CurrentPrice*H.Quantity)
FROM Holdings H
GROUP BY H.AccountId
END
```