This discussion is archived
10 Replies Latest reply: Jan 10, 2005 8:06 AM by BrianCamire RSS

constraint that prevents overlapping

200754 Newbie
Currently Being Moderated
Hi all,

Can I write a trigger on a table that prevents overlapping among period dates when insert or update?

For example:
Table1
Col1 number,
D1 date, -- start date of a period
D2 date, -- end date of a period

if data in the table are
1 1-1-05 30-1-05
2 1-3-05 31-3-05

then
the following insert will fail because it overlaps with period of first row:
3 3-1-05 10-2-05

the following insert will fail because it overlaps with period of second row:
3 20-2-05 10-4-05

whears the following insert will succeed it won't overlap with any of existing rows:
3 2-2-05 10-2-05


I hope to find any kind of solutions that can be implemented in the database side.

Thank in advance.
  • 1. Re: constraint that prevents overlapping
    Barbara Boehmer Oracle ACE
    Currently Being Moderated
    scott@ORA92> -- starting data:
    scott@ORA92> SELECT * FROM table1
      2  /
    
          COL1 D1          D2
    ---------- ----------- -----------
             1 01-JAN-2005 30-JAN-2005
             2 01-MAR-2005 31-MAR-2005
    
    scott@ORA92> -- package and triggers:
    scott@ORA92> CREATE OR REPLACE PACKAGE state_pkg
      2  AS
      3    TYPE myArray IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
      4    rowids            myArray;
      5    empty            myArray;
      6  END state_pkg;
      7  /
    
    Package created.
    
    scott@ORA92> SHOW ERRORS
    No errors.
    scott@ORA92> CREATE OR REPLACE TRIGGER table1_biu
      2    BEFORE INSERT OR UPDATE ON table1
      3  BEGIN
      4    state_pkg.rowids := state_pkg.empty;
      5  END table1_biu;
      6  /
    
    Trigger created.
    
    scott@ORA92> SHOW ERRORS
    No errors.
    scott@ORA92> CREATE OR REPLACE TRIGGER table1_aiur
      2    AFTER INSERT OR UPDATE ON table1
      3    FOR EACH ROW
      4  BEGIN
      5    state_pkg.rowids (state_pkg.rowids.COUNT+1) := :NEW.ROWID;
      6  END table1_aiur;
      7  /
    
    Trigger created.
    
    scott@ORA92> SHOW ERRORS
    No errors.
    scott@ORA92> CREATE OR REPLACE TRIGGER table1_aiu
      2    AFTER INSERT OR UPDATE ON table1
      3  DECLARE
      4    v_status INTEGER;
      5    v_rec     table1%ROWTYPE;
      6    v_count     NUMBER;
      7  BEGIN
      8    v_status := DBMS_LOCK.REQUEST
      9               (ID                => 123,
     10                LOCKMODE           => DBMS_LOCK.X_MODE,
     11                RELEASE_ON_COMMIT => TRUE);
     12    FOR i IN 1 .. state_pkg.rowids.COUNT
     13    LOOP
     14        SELECT *
     15        INTO     v_rec
     16        FROM     table1
     17        WHERE     ROWID = state_pkg.rowids(i);
     18        --
     19        SELECT COUNT(*)
     20        INTO     v_count
     21        FROM     table1
     22        WHERE     ROWID <> state_pkg.rowids(i)
     23        AND     v_rec.d1 <= d2
     24        AND     v_rec.d2 >= d1;
     25        --
     26        IF v_count > 0 THEN
     27          RAISE_APPLICATION_ERROR
     28            (-20001, v_rec.d1 || ' to ' || v_rec.d2 || ' overlaps existing values.');
     29        END IF;
     30    END LOOP;
     31  END table1_aiu;
     32  /
    
    Trigger created.
    
    scott@ORA92> SHOW ERRORS
    No errors.
    scott@ORA92> -- inserts that should fail:
    scott@ORA92> INSERT INTO table1 (col1, d1, d2)
      2  VALUES (3, TO_DATE ('03-JAN-2005', 'DD-MON-YYYY'), TO_DATE ('10-FEB-2005', 'DD-MON-YYYY'))
      3  /
    INSERT INTO table1 (col1, d1, d2)
                *
    ERROR at line 1:
    ORA-20001: 03-JAN-2005 to 10-FEB-2005 overlaps existing values.
    ORA-06512: at "SCOTT.TABLE1_AIU", line 25
    ORA-04088: error during execution of trigger 'SCOTT.TABLE1_AIU'
    
    
    scott@ORA92> INSERT INTO table1 (col1, d1, d2)
      2  VALUES (4, TO_DATE ('20-FEB-2005', 'DD-MON-YYYY'), TO_DATE ('10-APR-2005', 'DD-MON-YYYY'))
      3  /
    INSERT INTO table1 (col1, d1, d2)
                *
    ERROR at line 1:
    ORA-20001: 20-FEB-2005 to 10-APR-2005 overlaps existing values.
    ORA-06512: at "SCOTT.TABLE1_AIU", line 25
    ORA-04088: error during execution of trigger 'SCOTT.TABLE1_AIU'
    
    
    scott@ORA92> -- insert that shold succeed:
    scott@ORA92> INSERT INTO table1 (col1, d1, d2)
      2  VALUES (5, TO_DATE ('02-FEB-2005', 'DD-MON-YYYY'), TO_DATE ('10-FEB-2005', 'DD-MON-YYYY'))
      3  /
    
    1 row created.
    
    scott@ORA92> -- ending data after inserts:
    scott@ORA92> SELECT * FROM table1
      2  /
    
          COL1 D1          D2
    ---------- ----------- -----------
             1 01-JAN-2005 30-JAN-2005
             2 01-MAR-2005 31-MAR-2005
             5 02-FEB-2005 10-FEB-2005
    
    scott@ORA92> -- udpates that should fail:
    scott@ORA92> UPDATE table1
      2  SET    d1 = TO_DATE ('03-JAN-2005', 'DD-MON-YYYY'),
      3           d2 = TO_DATE ('10-FEB-2005', 'DD-MON-YYYY')
      4  WHERE  col1 = 5
      5  /
    UPDATE table1
           *
    ERROR at line 1:
    ORA-20001: 03-JAN-2005 to 10-FEB-2005 overlaps existing values.
    ORA-06512: at "SCOTT.TABLE1_AIU", line 25
    ORA-04088: error during execution of trigger 'SCOTT.TABLE1_AIU'
    
    
    scott@ORA92> UPDATE table1
      2  SET    d1 = TO_DATE ('20-FEB-2005', 'DD-MON-YYYY'),
      3           d2 = TO_DATE ('10-APR-2005', 'DD-MON-YYYY')
      4  WHERE  col1 = 5
      5  /
    UPDATE table1
           *
    ERROR at line 1:
    ORA-20001: 20-FEB-2005 to 10-APR-2005 overlaps existing values.
    ORA-06512: at "SCOTT.TABLE1_AIU", line 25
    ORA-04088: error during execution of trigger 'SCOTT.TABLE1_AIU'
    
    
    scott@ORA92> -- update that shold succeed:
    scott@ORA92> UPDATE table1
      2  SET    d1 = TO_DATE ('01-FEB-2005', 'DD-MON-YYYY'),
      3           d2 = TO_DATE ('28-FEB-2005', 'DD-MON-YYYY')
      4  WHERE  col1 = 5
      5  /
    
    1 row updated.
    
    scott@ORA92> -- ending data after updates:
    scott@ORA92> SELECT * FROM table1
      2  /
    
          COL1 D1          D2
    ---------- ----------- -----------
             1 01-JAN-2005 30-JAN-2005
             2 01-MAR-2005 31-MAR-2005
             5 01-FEB-2005 28-FEB-2005
    
    scott@ORA92> 
  • 2. Re: constraint that prevents overlapping
    Gabe2 Newbie
    Currently Being Moderated
    Ahmed,

    Why do you specifically need a trigger based solution?

    The solution from Barbara has some big disadvantage ... it won't scale. To test, insert a record in one session and don't commit ... then go open other N sessions and try some inserts/updates/deletes through them ... all of them will hang until the first session closes that original transaction; after that one of those N will succeed and the other N-1 will hang ... and so on. Now imagine how the users of your system will react.

    For a solution to the overlapping problem read and understand ...
    http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:21389386132607
    ... essentially you'll need a materialized view.

    Also check Vadim's article ...
    http://www.dbazine.com/tropashko8.shtml
  • 3. Re: constraint that prevents overlapping
    Barbara Boehmer Oracle ACE
    Currently Being Moderated
    Gabe,

    Within the first link that you provided, when asked about preventing overlapping values, Tom Kyte provided the following link which uses the same solution that I provided.

    http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:474221407101

    I don't see how a materialized view would help here. Would you please demonstrate the method that you are suggesting specific to the problem in this thread, as I did?

    Thanks,
    Barbara
  • 4. Re: constraint that prevents overlapping
    200754 Newbie
    Currently Being Moderated
    Thanks Barbara, Gabe for your time.

    I tried code provided by Barbara and it really worked fine.

    However, in trying to find a simpler code, I read the article recommended by Gabe and searched the Oracle forum and finally ended up with this code:

    CREATE OR REPLACE TRIGGER TABLE1_NO_OVERLAP
    AFTER INSERT OR UPDATE ON TABLE1
    DECLARE
    N NUMBER;
    BEGIN
    SELECT COUNT(*)
    INTO N
    FROM TABLE1 X1
    WHERE EXISTS
    (SELECT 1
    from TABLE1 X2
    WHERE X1.ROWID != X2.ROWID
    AND (X1.D1 BETWEEN X2.D2 AND X2.D2
    OR X1.D2 BETWEEN X2.D1 and X2.D2));
    IF N >0 THEN
    RAISE_APPLICATION_ERROR('-20100','Dates cannot be overlapped.');
    END IF;
    END;
    /


    Is there anything wrong with that?

    Thanks again,


    PS: I don't know how to preserve spaces in this Oracle forum message (that's why identation didn't appear my code in this message) sorry for that.
    Ahmed.
  • 5. Re: constraint that prevents overlapping
    Gabe2 Newbie
    Currently Being Moderated
    Ahmed,

    It looks OK … just one typo in there
    AND (X1.D1 BETWEEN X2.D2 AND X2.D2
    should be
    AND (X1.D1 BETWEEN X2.D1 AND X2.D2
    Good to see that the links helped … especially since I goofed up big time on my comments related to a mv-based solution … one cannot get a constrained MV with fast refresh “on commit” … without it the mv won't be good here. I had a similar problem in mind and rushed through an answer without proper backing.

    Barbara,

    Thank you for clearing my understanding on this one … learnt few things here. Cheers.

    PS. To preserve indentation … somebody gave me the answer here
    Query suggestions needed

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
  • 6. Re: constraint that prevents overlapping
    Barbara Boehmer Oracle ACE
    Currently Being Moderated
    It seems like this simple trigger shouldn't work, and yet it does seem to work. I have been taught that selecting from the same table that a trigger is on is a bad idea, because doing so produces an error that the table is mutating (changing), which it is, because the insert or update has not been committed yet. This has always been the case with row level before triggers when used with multi-row inserts or updates. However, I am unable to produce a test case that produces a mutating error with the statement level after trigger in this scenario. I doubt that Tom Kyte would provide such an unnecessarily complicated solution if such a simple one would suffice, unless there is something that causes this to work in 9i where it wouldn't have when he provided the solution for an earlier version. I can't help thinking that there might be something that I am missing. But, unless someone else can see something wrong with it, it looks good to me. I am still a bit baffled by why it does not produce the mutating error. Can you provide a link to where you found this solution? Is there some additional explanation there that we all might learn from?


  • 7. Re: constraint that prevents overlapping
    Gabe2 Newbie
    Currently Being Moderated
    Ahmed,

    On second thought ... your should reverse to Barbara's solution. The trigger won't work without serializing access to the table ... that is, open one session and insert a non-overlapping date range (something that will succeed) and don't commit ... then open another session and insert the same data range ... both inserts will succeed ... commit in both and now you have overlapping ranges.

    There is no escape… upon modification (insert/update) of a range one has to check all the other rows in the table in order to check for over-lapping … hence access to the table has to serialized (what Barbara does with the dbms_lock.request in x_mode).
  • 8. Re: constraint that prevents overlapping
    Barbara Boehmer Oracle ACE
    Currently Being Moderated
    Thanks for figuring it out, Gabe. I had been using that method for so long that I had forgotten why I was using it. I did all of my previous testing from the same session. Testing from two separate sessions confirms what you said.
  • 9. Re: constraint that prevents overlapping
    Gabe2 Newbie
    Currently Being Moderated
    Barbara,

    <quote>However, I am unable to produce a test case that produces a mutating error with the statement level after trigger in this scenario</quote>

    The mutating table restriction <doco>applies to all triggers that use the FOR EACH ROW clause, and statement triggers that are fired as the result of a DELETE CASCADE</doco>.

    So, by enlarge, the mutating table restriction applies to row-level triggers ... in our case here we are fine from that angle ... the trigger just doesn't solve our mutually-exclusive-ranges requirement.

    Cheers.
  • 10. Re: constraint that prevents overlapping
    BrianCamire Explorer
    Currently Being Moderated
    As the thread at:

    http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:474221407101

    discusses, the trigger-based approach may fail to detect overlaps introduced by concurrent transactions if one of the transactions is using serializable isolation level.