EX 5.5) Write triggers to enforce the referential integrity constraint from sect

Question

EX 5.5) Write triggers to enforce the referential integrity constraint from section to time slot, on updates to section, and time slot. Note that the ones we wrote in Figure 5.8 do not cover the update operation.

Please provide the answer in terms of the syntax from Database Systems Concepts, 6th Edition, Chapter 5.

Thank you.

The below SQL querry is the only thing provided in the book.

create trigger timeslot check1 after insert on section
referencing new row as nrow
for each row
when (nrow.time slot id not in (
select time slot id
from time slot)) /* time slot id not present in time slot */
begin
rollback
end;
create trigger timeslot check2 after delete on timeslot
referencing old row as orow
for each row
when (orow.time slot id not in (
select time slot id
from time slot) /* last tuple for time slot id deleted from time slot */
and orow.time slot id in (
select time slot id
from section)) /* and time slot id still referenced from section*/
begin
rollback
end;

Figure 5.8

Explanation / Answer

Trigger to enforce the referential integrity ON update on section

CREATE TRIGGER timeslotcheck3 AFTER UPDATE OF timeslotid ON section
REFERENCING NEW ROW AS urow
FOR EACH ROW
WHEN (urow.timeslotid NOT IN (
SELECT timeslotid FROM timeslot)) /* updated time slot id not present in time slot */
BEGIN
ROLLBACK
END;

This trigger will check on all updates on section to make sure updated timeslotid exists in timeslot table. If not it will rollback the operation.

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

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