Syntax error for a before trigger

Question

I have a table BOOKING(hotelID, roomNo, guestID, startDate, endDate) and I want to create a trigger to do a validation (before insert) by comparing the startDate and endDate to see if the time slot is already been taken by other guests.

CREATE OR REPLACE TRIGGER MYTRIGGER 
BEFORE insert ON BOOKING 

referencing new as newTuple
for each row

declare
  t boolean;
  cursor c is 
    select startDate,endDate from ROOM where hotelID = newTuple.hotelID and ROOMNO = newTuple.roomNo;
BEGIN
  t := true;
  open c;
  loop
    fetch c into mStartDate, mEndDate;
    exit when c%NOTFOUND;

    if (NOT((newTuple.startDate >= mEndDate and newTuple.endDate >= mEndDate)
        or(newTuple.startDate <= mStartDate and newTuple.endDate <= mStartDate))) then
      t := false;
    end if;

  end loop;
  close c;

  WHEN (t=true) then
  INSERT INTO BOOKING(hotelID,roomNo,guestID,startDate,endDate) 
  values(newTuple.hotelID, newTuple.roomNo, newTuple.guestID, newTuple.startDate,
         newTyple.endDate);
END;

But it gives me syntax errors messages which I don't know how to solve (I am new to Oracle):

Error(26,3): PLS-00103: Encountered the symbol "WHEN" when expecting one of the following:
( begin case declare end exception exit for goto if loop mod
null pragma raise return select update while with
<an identifier> <a double-quoted delimited-identifier>
<a bind variable> << continue close current delete fetch lock
insert open rollback savepoint set sql execute commit forall
merge pipe purge
The symbol "case" was substituted for "WHEN" to continue. 
Error(30,4): PLS-00103: Encountered the symbol ";" when expecting one of the following:
case   

Answer 1

You are incorrectly using the CASE..WHEN structure on PL/SQL. Check the PL/SQL Control Structures manual page on Oracle documentation site. Here is another sample of CASE..WHEN use on a SO question and its answer.

Basically you will need to change this

WHEN (t=true) then
INSERT INTO BOOKING(hotelID,roomNo,guestID,startDate,endDate) 
VALUES (newTuple.hotelID, newTuple.roomNo, newTuple.guestID, newTuple.startDate,
        newTyple.endDate);

By this:

CASE
  WHEN (t=true) THEN INSERT INTO BOOKING(hotelID,roomNo,guestID,startDate,endDate) 
                     VALUES (newTuple.hotelID, newTuple.roomNo, newTuple.guestID, 
                             newTuple.startDate, newTyple.endDate);
END CASE;

Or as suggested on a comment to your question, if you are checking just one condition, why not using and IF structure? It will be cleaner and easier to understand/maintain.

Answer 2

The immediate problem is that you're using WHEN, which is part of a CASE construct. You would need to use IF here:

IF t then
    INSERT INTO BOOKING(hotelID,roomNo,guestID,startDate,endDate) ...
END IF;

But that's not how triggers work - you don't insert again from within them, you would be attempting to insert an exact duplicate of the row you're inserting, which would cause your trigger to fire again. Probably only once more - hopefully the second insert would see the first in the cursor and stop. But both would actually do the insert - when there is a conflict with the dates, you aren't actually preventing the insertion, you're just not attempting the duplicate; and the second would still insert, so you'd get two identical rows. The normal way to prevent an insert occurring is for the trigger to raise an exception; conceptually:

IF NOT t THEN
    RAISE_APPLICATION_ERROR(-20001, 'Overlapping dates');
END IF;

But this still won't work - you have at least three other problems. First, you can't (easily) query the table you're inserting into; you'll get an ORA-04091 'table is mutating' error. Secondly, when you reference the new values you need to prefix them with a colon, so :newTuple.hotelID etc. And thirdly, you have a concurrency problem; two rows inserting simultaneously with overlapping dates won't see each other and both will succeed. (And not strictly an error, but looping over all records to find a match is going to be inefficient - why not only look for existing rows that conflict with the dates being inserted?)

A trigger doesn't seem to be an appropriate way to enforce this constraint.

---

OK, this doesn't actually get a mutating table error:

create table booking(hotelid number, roomno number, guestid number,
    startdate date, enddate date);

create or replace trigger mytrigger
before insert on booking 
referencing new as new
for each row
declare
    cnt number;
begin
    select count(*) into cnt from booking
    where hotelid = :new.hotelid
    and roomno = :new.roomno
    and not (enddate < :new.startdate or startdate > :new.enddate);

    if cnt > 0 then
        raise_application_error(-20001, 'Overlapping dates');
    end if;
end;
/
TRIGGER MYTRIGGER compiled

Inserting some data:

insert into booking values (1, 1, 1, date '2013-02-28', date '2013-03-05');

1 rows inserted.

insert into booking values (1, 1, 2, date '2013-02-27', date '2013-03-01');

Error starting at line 24 in command:
insert into booking values (1, 1, 2, date '2013-02-27', date '2013-03-01')
Error report:
SQL Error: ORA-20001: Overlapping dates
ORA-06512: at "STACKOVERFLOW.MYTRIGGER", line 10
ORA-04088: error during execution of trigger 'STACKOVERFLOW.MYTRIGGER'

insert into booking values (1, 1, 3, date '2013-03-05', date '2013-03-06');

Error starting at line 25 in command:
insert into booking values (1, 1, 3, date '2013-03-05', date '2013-03-06')
Error report:
SQL Error: ORA-20001: Overlapping dates
ORA-06512: at "STACKOVERFLOW.MYTRIGGER", line 10
ORA-04088: error during execution of trigger 'STACKOVERFLOW.MYTRIGGER'

insert into booking values (1, 1, 4, date '2013-03-06', date '2013-03-07');

1 rows inserted.

The two attempts to enter overlapping dates got our -20001 exception, and only the two non-overlapping rows were inserted:

select * from booking;

   HOTELID     ROOMNO    GUESTID STARTDATE  ENDDATE  
---------- ---------- ---------- ---------- ----------
         1          1          1 28/02/2013 05/03/2013 
         1          1          4 06/03/2013 07/03/2013 

But you still have a concurrency problem, as two sessions could insert overlapping data simultaneously. As they will both be uncommitted, the select count(*) in each trigger instance won't see the other, so they'll both report zero, neither will raise an exception, and both will be inserted.