10 Replies Latest reply on Jan 10, 2005 4:06 PM by Brian Camire

    constraint that prevents overlapping

    200754
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            Brian Camire
                            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.