8)Assume the BOOK table contains a column called TOTAL_ON_HAND that represents t

Question

8)Assume the BOOK table contains a column called TOTAL_ON_HAND that represents the total units on hand in all branches for that book. Following the style shown in the text, write the code in PL/SQL or T-SQL for the following triggers: a. When inserting a row in the INVENTORY table, add the ON_HAND value to the TOTAL_ON_HAND value for the appropriate book. b. When updating a row in the INVENTORY table, add the difference between the new ON_HAND value and the old ON_HAND value to the TOTAL_ON_HAND value for the appropriate book. c. When deleting a row in the INVENTORY table, subtract the ON_HAND value from the TOTAL_ON_HAND value for the appropriate book.

Explanation / Answer

PLease Rate with LifeSaver

a)

CREATE TRIGGER tR_INVENTORY
ON INVENTORY
AFTER INSERT
AS
DECLARE @ON_HAND int
SELECT @ON_HAND=(SELECT ON_HAND FROM INSERTED)
UPDATE BOOK
SET TOTAL_ON_HAND=TOTAL_ON_HAND+@ON_HAND
END;

b)

CREATE TRIGGER tR_INVENTORY
ON INVENTORY
AFTER Update
AS
DECLARE @New_ON_HAND int

DECLARE @Old_ON_HAND int

SELECT @New_ON_HAND = ( SELECT @New_ON_HAND FROM INSERTED)

SELECT  @Old_ON_HAND = ( SELECT @Old_ON_HAND FROM DELETED)
UPDATE BOOK
SET TOTAL_ON_HAND=TOTAL_ON_HAND+@New_ON_HAND-@Old_ON_HAND
END;

c)

CREATE TRIGGER tR_INVENTORY

ON INVENTORY
AFTER Delete
AS
DECLARE @ON_HAND int
SELECT @ON_HAND=(SELECT ON_HAND FROM DELETED)
UPDATE BOOK
SET TOTAL_ON_HAND=TOTAL_ON_HAND-@ON_HAND
END;

Related Questions

Navigate

• Browse (All)
• Browse 8
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.