Use the following PDM for a Veterinary Clinic. The Total Amount Due is a sum of

Question

Use the following PDM for a Veterinary Clinic. The Total Amount Due is a sum of the amounts due for all visits for all pets belonging to the same owner. Write Oracle trigger(s) to recalculate the total amount due (in OWNER table), whenever a new visit is added (with the amount due > 0);

Physical Data Model Project ModelVeterinary Clinic Author COMP481 Version 99/04/20 PET PET IDENT FER PET IDENTIFER OWNER ID PET NAME BREED NUMBER(8) VARCHAR2(20) OWNER D-OWNER ID VISIT OWNER VISIT ID PET_IDENTIFIER NUMBER(6) DATE TIME AMOUNT DUE OWNER ID OWNER NAME STREET ADDRESS TOTAL_AMOUNT_DUE NUMBER(8,2) VARCHAR2(20) VARCHAR2(20) DATE NUMBER(8.2)

Explanation / Answer

CREATE OR REPLACE TRIGGER total_due_after_visit
AFTER INSERT
ON VISIT
FOR EACH ROW

DECLARE
due int;

BEGIN

SELECT AMOUNT_DUE INTO due FROM VISIT;

update OWNER INNER JOIN PET ON OWNER.OWNER_ID = PET.OWNER_ID INNER JOIN VISIT ON PET.PET_IDENTIFIER = VISIT.PET_IDENTIFIER

set
:new.TOTAL_AMOUNT_DUE = TOTAL_AMOUNT_DUE + :new.due ;

END;

/

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

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