4

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
Jason Aller
  • 3,541
  • 28
  • 38
  • 38
ada109
  • 53
  • 4
  • 1
    . . 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 Jan 04 '20 at 13:46
  • 1
    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: http://stevenfeuersteinonplsql.blogspot.com/2016/12/get-rid-of-mutating-table-trigger.html. – Gordon Linoff Jan 04 '20 at 13:54
  • Indeed, the mutating data gives me head aches. Thank you very much for the material! Unfortunately I am running out of time to use a different database. Oracle APEX does not really specify the error I am having, at least in a way I can read (in class I have seen that SQL developer is more specific). Again, thank you very very much for the material and the suggestions! (I am currently reading the blog post) – ada109 Jan 04 '20 at 14:12
  • 1
    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 Jan 04 '20 at 14:58
  • 1
    If you really need to do it ion a trigger, then use a statement trigger , not a row trigger. See [here](https://codingsight.com/oracle-as-workaround-of-mutating-tables/) for an example. – gsalem Jan 04 '20 at 17:08
  • 1
    One approach might be to have the trigger populate a second table with a row for each day booked for the room. Then a unique constraint on that table would prevent overlaps. I haven’t tried this though. – William Robertson Jan 04 '20 at 19:40
  • 1
    By the way, in PL/SQL syntax `declare` marks the start of the declaration section which contains multiple declarations, not one per `declare`, the assignment operator is `:=` and each statement must be terminated with a semicolon. Also `char` is a fixed-length type which adds blank spaces up to the declared width. I suspect only a tiny percentage of developers who use it actually want that. – William Robertson Jan 04 '20 at 19:55
  • 1
    Also there is no `when exists` construct. I’m not sure what manual you are using, but the Oracle one is [here](https://docs.oracle.com/en/database/oracle/oracle-database/12.2/lnpls/toc.htm). – William Robertson Jan 04 '20 at 20:35
  • 1
    @WilliamRobertson - I suspect OP meant `WHEN (EXISTS...`. The condition in the `WHEN` clause of a trigger has to be enclosed in parentheses, but other than that `EXISTS` is legal as the condition part of a `WHEN ()`, at least AFAIK. (...he said, surreptitiously adjusting his Nomex long-johns...) :-) – Bob Jarvis - Слава Україні Jan 04 '20 at 20:57
  • 2
    If the OP is new to triggers, I strongly suggest he read this: https://blogs.oracle.com/oraclemagazine/the-trouble-with-triggers and https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2575882200346616184 and https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/create_trigger.htm#LNPLS01374 – Joe Jan 04 '20 at 21:30
  • Wow! I never hoped for so many answers! Thank you very very much, I've learnt so many new things from your suggestions, corrections and comments! Thank you! – ada109 Jan 06 '20 at 01:49
  • 1
    I corrected my answer below: the stats need to be gathered on the materialized view LOG when it has no rows, then those stats need to be locked. This is to work around a problem (at least in versions 12.2 and preceding, don't know about 18+) where the SQL to refresh the materialized view may include unfortunate hints. – Stew Ashton Jan 06 '20 at 14:18
  • 1
    The materialized view approach is interesting, I wouldn't have thought of that. But I think a stored procedure is potentially a better solution. It should be fairly easy to use the DBMS_LOCK package to serialize writes to the table to prevent anomalies between the time you run your select and the insert/update. You could probably even lock at the room level to allow more concurrent access via dbms_lock.allocate_unique. I'm not sure which approach would perform better in the long term. – Dan McGhan Jan 06 '20 at 23:12

1 Answers1

3

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
....
Stew Ashton
  • 1,499
  • 9
  • 6