SQL Newbie :) Whats wrong with ths statement can anyone tell me? 
Author Message
 SQL Newbie :) Whats wrong with ths statement can anyone tell me?

SQL> CREATE OR REPLACE TRIGGER secure_booking
  2  BEFORE INSERT OR UPDATE ON booking
  3  FOR EACH ROW
  4  DECLARE
  5       CURSOR C1 IS
  6            SELECT patient_no,room_no,resv_date,to_date
  7            FROM BOOKING;
  8            booking_record C1%ROWTYPE;
  9  BEGIN
 10       IF INSERT OR UPDATE THEN
 11            FOR booking_record IN C1 LOOP
 12                 IF  :new.resv_date >= booking_record.resv_date
 13                 AND :new.to_date <= booking_record.to_date
 14                 OR  :new.resv_date <= booking_record.resv_date
 15                 AND :new.to_date >= booking_record.to_date
 16                 OR  :new.resv_date <= booking_record.resv_date
 17                 AND :new.to_date <= booking_record.to_date
 18                 OR  :new.resv_date >= booking_record.resv_date
 19                 AND :new.to_date >= booking_record.to_date
 20                 THEN
 21                      RAISE_APPLICATION_ERROR (-20501,
 22                          'There is already a booking for this room at
this date');
 23                 END IF;
 24            END LOOP;
 25       END IF;
 26  END;
 27  /
CREATE OR REPLACE TRIGGER secure_booking
*
ERROR at line 1:
OCA-30021: error preparing/executing SQL statement
[POL-5228] syntax error


Wed, 18 Jun 1902 08:00:00 GMT
 SQL Newbie :) Whats wrong with ths statement can anyone tell me?

INSERT AND UPDATE are sql-reserved words. You have to use

if inserting or updating...

instead.

Uli

Quote:
> SQL> CREATE OR REPLACE TRIGGER secure_booking
>   2  BEFORE INSERT OR UPDATE ON booking
>   3  FOR EACH ROW
>   4  DECLARE
>   5       CURSOR C1 IS
>   6            SELECT patient_no,room_no,resv_date,to_date
>   7            FROM BOOKING;
>   8            booking_record C1%ROWTYPE;
>   9  BEGIN
>  10       IF INSERT OR UPDATE THEN
>  11            FOR booking_record IN C1 LOOP
>  12                 IF  :new.resv_date >= booking_record.resv_date
>  13                 AND :new.to_date <= booking_record.to_date
>  14                 OR  :new.resv_date <= booking_record.resv_date
>  15                 AND :new.to_date >= booking_record.to_date
>  16                 OR  :new.resv_date <= booking_record.resv_date
>  17                 AND :new.to_date <= booking_record.to_date
>  18                 OR  :new.resv_date >= booking_record.resv_date
>  19                 AND :new.to_date >= booking_record.to_date
>  20                 THEN
>  21                      RAISE_APPLICATION_ERROR (-20501,
>  22                          'There is already a booking for this
room at
> this date');
>  23                 END IF;
>  24            END LOOP;
>  25       END IF;
>  26  END;
>  27  /
> CREATE OR REPLACE TRIGGER secure_booking
> *
> ERROR at line 1:
> OCA-30021: error preparing/executing SQL statement
> [POL-5228] syntax error



Wed, 18 Jun 1902 08:00:00 GMT
 SQL Newbie :) Whats wrong with ths statement can anyone tell me?
Sorry,

you have the insert or update clause in the trigger definition!
You don't have to use the whole if-clause!

bye
Uli

Quote:
> INSERT AND UPDATE are sql-reserved words. You have to use

> if inserting or updating...

> instead.

> Uli

> > SQL> CREATE OR REPLACE TRIGGER secure_booking
> >   2  BEFORE INSERT OR UPDATE ON booking
> >   3  FOR EACH ROW
> >   4  DECLARE
> >   5       CURSOR C1 IS
> >   6            SELECT patient_no,room_no,resv_date,to_date
> >   7            FROM BOOKING;
> >   8            booking_record C1%ROWTYPE;
> >   9  BEGIN
> >  10       IF INSERT OR UPDATE THEN
> >  11            FOR booking_record IN C1 LOOP
> >  12                 IF  :new.resv_date >= booking_record.resv_date
> >  13                 AND :new.to_date <= booking_record.to_date
> >  14                 OR  :new.resv_date <= booking_record.resv_date
> >  15                 AND :new.to_date >= booking_record.to_date
> >  16                 OR  :new.resv_date <= booking_record.resv_date
> >  17                 AND :new.to_date <= booking_record.to_date
> >  18                 OR  :new.resv_date >= booking_record.resv_date
> >  19                 AND :new.to_date >= booking_record.to_date
> >  20                 THEN
> >  21                      RAISE_APPLICATION_ERROR (-20501,
> >  22                          'There is already a booking for this
> room at
> > this date');
> >  23                 END IF;
> >  24            END LOOP;
> >  25       END IF;
> >  26  END;
> >  27  /
> > CREATE OR REPLACE TRIGGER secure_booking
> > *
> > ERROR at line 1:
> > OCA-30021: error preparing/executing SQL statement
> > [POL-5228] syntax error



Wed, 18 Jun 1902 08:00:00 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. SQL Newbie :) Whats wrong with this statement?

2. Whats wrong with this SQL statement?

3. Whats Wrong in my Case Statement ?

4. whats wrong with this statement; error close LEFT??/

5. Whats wrong with this case statement?

6. Whats wrong with my CreateIndex statement?

7. Whats wrong with this select statement???

8. Anyone know of some canned (cheap or free) DB performance testing software

9. whats wrong with this SQL?

10. whats wrong with the T-SQL Statment

11. Whats wrong with this SQL string?

12. ListView\DAO\SQL\Access, Whats wrong?


 
Powered by phpBB® Forum Software