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
You might also like to view...
In the Convert to Profile dialog box, the ____________________ profile is the color profile that is currently attached to the document.
Fill in the blank(s) with the appropriate word(s).
If you write a computer program, and want to execute the same program many times without rewriting it each time, you must save the program on disk.
Answer the following statement true (T) or false (F)
What can be described as the series of actions from the beginning of an Activity to its end?
A. life cycle B. flow chart C. run time D. execution period
_____ is a project type that upgrades an existing system to provide new capabilities that meet new business needs.
a. Enhancement b. Mandatory c. Innovation d. Breakthrough