Tutorial 4 Solutions

Triggers

1. Here is a suggestion that, like the example in Figure 7.6 of the textbook, has a subquery in the "WHEN" condition.

   CREATE OR REPLACE TRIGGER BidHighEnough
   BEFORE INSERT ON Bids
   REFERENCING NEW AS new
   FOR EACH ROW
   WHEN ( EXISTS ( SELECT *
                   FROM   Bids
                   WHERE  property = :new.property AND amount > :new.amount ) )
   BEGIN
       RAISE_APPLICATION_ERROR(-20000, 'bid not high enough');
   END;
   

   However Oracle does not allow subqueries in the "WHEN" condition. The following alternative trigger definition works in Oracle:

   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;
   

   The "DECLARE" block in this trigger is not standard SQL, but it is allowed here since (as noted by Ullman: "Constraints and Triggers") in Oracle "Triggers are a special PL/SQL construct similar to procedures", and the DECLARE block is part of a PL/SQL construct.

2. 1. 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.)

   2. 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
      

   3. 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.

   4. 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.)

   5. 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?