Re: Please help me - SQL - Urgent

>> I am trying to create a trigger for the table below (It has to be a
trigger):

A few hints:

1. use plural or collective nouns for table names unless there is only one of them/
2. There is no such thing as VARCHAR2 in SQL; that is proprietary Oracle crap.
3. Lowercase is 12.7 times easier to read than all uppercase
4. post DDL instead of pseudo-code

>> What I want to do is ensure that when I try to insert a row that has
dates that fall between or on the resv_date and pto_date of an already existing row (these are a booking start and end date) the insertion is rejected. <<

Is this what you meant to say?

CREATE TABLE Bookings
(name CHAR(6) NOT NULL PRIMARY KEY,
 room_nbr INTEGER NOT NULL,
 resv_date DATE NOT NULL UNIQUE,
 pto_date DATE NOT NULL UNIQUE,
 CHECK (resv_date <= pto_date),
 UNIQUE (resv_date, pto_date),
 CHECK (NOT EXISTS

        (SELECT *
           FROM Bookings AS B1
          WHERE resv_date BETWEEN B1.resv_date AND B1.pto_date),
             OR pro_date BETWEEN B1.resv_date AND B1.pto_date)));

Oracle is a weak language, but you could do this in DB2 or any other real SQL-92. You will have to put these predicates into a trigger and then lose all of the power of the optimizer (which is not much in Oracle) to integrate them into the queires.

--CELKO--
Joe Celko, SQL and Database
--CELKO--
Joe Celko, SQL and Database Consultant

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed May 31 2000 - 00:00:00 CDT