Validate data inserted based on other data in the same table (Oracle)

Question

I am doing a project in SQL Oracle and have found a problem, as far as I am quite new to SQL.

I have a system that administrates booked rooms, in a table called bookings. Some of the attributes are:

room_id
arrival_date
nights_nr
booking_id (which is the PK).

I have a UNIQUE(room_id, arrival_date) constraint.

To have a coherent DB I would need to check, for every inserted or updated row, whether any of the intended-to-book days for that room are already booked. This being a constraint based on the table's values, I would need a select so I cannot perform this in a CHECK (or at least I haven't figured out another possibility).

I am trying to do this with a trigger, but this is my first contact with triggers.

What I thought was to have a select in a when and to verify if, for another booking_id and the same room_id I am currently intending to update, the range of days I am intending to book intersects with the range booked.

Due to lack of knowledge I have written something like this:

CREATE OR REPLACE TRIGGER validate_free_room
BEFORE INSERT OR UPDATE OF arrival_date, night_nr ON bookings
FOR each row
DECLARE a char(8)
DECLARE b char(3)
SET a=booking_id
SET b=room_id
WHEN EXISTS (
    SELECT booking_id
    FROM bookings r
    WHERE 
    (r.booking_id!=a 
     AND 
     ((arrival_date BETWEEN r.arrival_date AND r.arrival_date + nights_nr)
      OR
      (arrival_date + nights_nr BETWEEN r.arrival_date AND r.arrival_date + r.nights_nr)
      )
     AND
     b=r.room_id
    )
   )
BEGIN
    RAISE_APPLICATION_ERROR (-20107,'Room already booked')
         end;

I have just found out that I cannot perform selects in a table that is being modified with a "for each row".

Do you have any ideas how I could do this in a correct way? (I know the precedent lines are a complete disaster).

I am using Oracle Application Express, which gave me the following error suggestions:

ORA-24344: success with compilation error
ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_190200", line 592
ORA-06512: at "SYS.DBMS_SYS_SQL", line 1658
ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_190200", line 578
ORA-06512: at "APEX_190200.WWV_FLOW_DYNAMIC_EXEC", line 2057


3. for each row
4. declare a char(8)
5. declare b char(3)
6. set a=id_rezerva
7. set b=id_camera

and

Error computing plan for statement.
ORA-00900: invalid SQL statement

. . As a note: Your overlap logic is not correct. But the more important issue you are having is the mutating data trigger error. You should be clear about the error you are getting. — Gordon Linoff
– Gordon Linoff, Commented Jan 4, 2020 at 13:46
This is quite tricky to work around. I might suggest you start with a simpler problem, or do the check in the application (using a stored procedure), or use a different database. Here is one discussion: stevenfeuersteinonplsql.blogspot.com/2016/12/…. — Gordon Linoff
– Gordon Linoff, Commented Jan 4, 2020 at 13:54
you'll need another condition to look for existing bookings that are entirely between your proposed dates. Also might be best to utilise a time component to your dates and have all arrivals commence at 1300h and departures occur at 1000h. If you just use dates/peg all your times to the same value then BETWEEN will prevent rooms being taken by guest 2 on the same day they are vacated by guest 1 (between is inclusive) but every hotel would want to support same day occupancy change of a room — Caius Jard
– Caius Jard, Commented Jan 4, 2020 at 14:58
If you really need to do it ion a trigger, then use a statement trigger , not a row trigger. See here for an example. — gsalem
– gsalem, Commented Jan 4, 2020 at 17:08
If the OP is new to triggers, I strongly suggest he read this: blogs.oracle.com/oraclemagazine/the-trouble-with-triggers and asktom.oracle.com/pls/apex/… and docs.oracle.com/cd/B28359_01/appdev.111/b28370/… — Joe
– Joe, Commented Jan 4, 2020 at 21:30

Stew Ashton · Accepted Answer · 2020-01-06 14:07:19Z

There is another way to do this that does not involve triggers:

create a materialized view that joins the table on itself and contains rows only when there is an overlap.
This materialized view should refresh on commit.
It has a constraint 1=0 that will fail every time there is a row.
So at every commit, if there is an overlap, the commit will fail and the materialized view will always be empty.

There are DBA-type things to be done so this will perform OK, such as gathering statistics on the materialized view log when empty, then locking thoses statistics.

SQL> create table bookings(
  2    booking_id integer primary key,
  3    room_id integer not null,
  4    arrival_date date not null check (arrival_date = trunc(arrival_date)),
  5    nights_nr integer not null,
  6    UNIQUE(room_id, arrival_date)
  7  );

Table BOOKINGS created.

SQL> create materialized view log on bookings with rowid including new values;

Materialized view log BOOKINGS created.

SQL> create materialized view bookings_conflicts
  2  refresh fast on commit as
  3  select a.rowid arid, b.rowid brid
  4  from bookings a, bookings b
  5  where a.room_id = b.room_id 
  6    and a.arrival_date < b.arrival_date
  7    and a.arrival_date + a.nights_nr > b.arrival_date;

Materialized view BOOKINGS_CONFLICTS created.

SQL> alter materialized view bookings_conflicts add constraint no_overlaps check(1=0) deferrable;

Materialized view BOOKINGS_CONFLICTS altered.

SQL> insert into bookings
  2  select 1, 1, date '2020-01-01', 5 from dual union all
  3  select 2, 1, date '2020-01-05', 1 from dual union all
  4  select 3, 1, date '2020-01-06', 1 from dual;

3 rows inserted.

SQL> commit;

Error starting at line : 24 in command -
commit
Error report -
ORA-12008: error in materialized view or zonemap refresh path
ORA-02290: check constraint (STEW.NO_OVERLAPS) violated
....

Collectives™ on Stack Overflow

Validate data inserted based on other data in the same table (Oracle)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest