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
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?
```
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
```
You might also like to view...
Which of the following will properly encode the string "\w\\\t" into variable s?
a. s = "\w\\\t"; b. s = R"(\w\\\t)"; c. s = "\\w\\\\\\t"; d. s = "/w///t";
In the Allow list box in the Data Validation dialog box, the _____ option means that the cell will accept only times.
A. Chronology B. Clock C. Time D. Calendar
Android offers a GUI experience by employing programs called _______________ that enable users to customize their Android device extensively.
Fill in the blank(s) with the appropriate word(s).
The ____ data type is used to store integers.
A. INTEGER B. NUMERAL C. NUMBER D. INT