CREATE OR REPLACE TRIGGER BidHighEnough BEFORE INSERT ON Bids REFERENCING NEW AS new FOR EACH ROW DECLARE numHigherBids INT; BEGIN SELECT COUNT(amount) INTO numHigherBids FROM Bids WHERE item = :new.item AND amount > :new.amount; IF ( numHigherBids > 0 ) THEN RAISE_APPLICATION_ERROR(-20000, 'bid not high enough'); END IF; END;
CREATE ASSERTION NotOverFullWard CHECK ( NOT EXISTS ( SELECT wid FROM Wards JOIN PatientInWard ON wid=ward GROUP BY wid, numBeds HAVING numBeds < COUNT(pid) ) );
(While assertions are in the SQL standard, it can be difficult to implement them efficiently, so most relational database management systems do not support them. See, for example, the discussion at the start of section 7.4 of the course book, or this Stack Overflow discussion.)
CREATE VIEW FreeBeds AS SELECT wid as ward, numBeds - COUNT(pid) AS numBeds FROM Wards LEFT OUTER JOIN PatientInWard ON ward = wid GROUP BY wid, numBeds
CREATE TRIGGER WardFull BEFORE INSERT ON PatientInWard REFERENCING NEW AS new FOR EACH ROW DECLARE numAvailable INT; availableWard INT; BEGIN SELECT numBeds INTO numAvailable FROM FreeBeds WHERE ward = :new.ward; IF numAvailable = 0 THEN SELECT MIN(ward) into availableWard FROM FreeBeds WHERE numBeds > 0; :new.ward := availableWard; END IF; END;
The trigger given above makes an assumption about the contents of the database, and if that assumption does not hold we will get a run-time error. What is that assumption? Modify the trigger code so that this case is handled in an appropriate way.
Start by identifying all events that could cause the constraint to be violated. Then implement an appropriate action to deal with each event.
If we assume that the trigger from part (c) is implemented in the database, then there is less work to be done in part (d).
We need to handle UPDATE operations on attribute numBeds in relation Wards, but a trigger on this event should only fire if the new value is lower than the old value (there's no point doing extra work if more beds have been added to a ward).
(While assertions are convenient for programmers to write, this question illustrates why it is difficult to implement a mechanism to handle arbitrary assertions — in general there can be many different modifications that can cause a constraint to be violated, and it is difficult to spot each one automatically.)
We could CASCADE the deletion, so that corresponding rows in the PatientInWard relation are also deleted. However, it might be better to reassign the patients in that ward to other wards. How could this be implemented using a trigger?