-- The following is an example of creating a trigger 
-- that prevents dates from overlapping on a table. 

-- This is a mutating table example!!

-- ©2005 Edward Stoever 

-- It has been pointed out to me by Thomas Kyte and David Aldridge (here) that
-- this trigger needs to lock the entire table in order for it to work in a multiuser
-- environment. Note the following caution by Mr. Kyte:

         -- "Virtually every integrity constraint I've seen in the last 10 years that tries 
	 -- to do this (in an application, in a stored procedure, in a trigger) has been 
	 -- done wrong -- going across rows or across objects without some level of manual 
	 -- locking is hard."

-- So, I post this code as an example for learning purposes. Caution should be applied.
-- Edward 
-- ;-)
	 
CREATE TABLE MY_TEST
(
  MY_TEST_ID           NUMBER(8)                NOT NULL,
  MY_TEST_START_DATE   DATE                     NOT NULL,
  MY_TEST_END_DATE     DATE                     NOT NULL,
  MY_TEST_DESCRIPTION  VARCHAR2(70 BYTE)
)
TABLESPACE USERS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCACHE
NOPARALLEL;


CREATE UNIQUE INDEX PK_MY_TEST_ID ON MY_TEST
(MY_TEST_ID)
LOGGING
TABLESPACE DEVELOPMENT
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;

CREATE OR REPLACE PACKAGE my_test_temporary_data
AS
  TYPE t_my_temp_table IS TABLE OF MY_TEST%ROWTYPE
     INDEX BY BINARY_INTEGER;

  v_empty_table         t_my_temp_table;
  v_my_temp_table       t_my_temp_table;
  v_number_of_entries   BINARY_INTEGER  := 0;
END my_test_temporary_data;
/

CREATE OR REPLACE TRIGGER MY_TEST_NO_OVERLAP_DATES_STEP1
   BEFORE INSERT OR UPDATE
   ON MY_TEST
   REFERENCING NEW AS NEW OLD AS OLD
   FOR EACH ROW
BEGIN
   IF :NEW.my_test_start_date > :NEW.my_test_end_date
   THEN
      -- reset values:
      my_test_temporary_data.v_my_temp_table := my_test_temporary_data.v_empty_table;
      my_test_temporary_data.v_number_of_entries := 0;
	  
      RAISE_APPLICATION_ERROR
         (-20023,
          'Cannot insert of update! Date Conflict! my_test_start_date come AFTER my_test_end_date!'
         );
   END IF;

--  The following code will add the appropriate data to the v_my_temp_table variable
--  So that it can be compared. This will avoid using :new.value on mutating table!
   my_test_temporary_data.v_number_of_entries :=
                               my_test_temporary_data.v_number_of_entries + 1;
   my_test_temporary_data.v_my_temp_table
                                  (my_test_temporary_data.v_number_of_entries).my_test_id :=
                                                              :NEW.my_test_id;
   my_test_temporary_data.v_my_temp_table
                                  (my_test_temporary_data.v_number_of_entries).my_test_start_date :=
                                                      :NEW.my_test_start_date;
   my_test_temporary_data.v_my_temp_table
                                  (my_test_temporary_data.v_number_of_entries).my_test_end_date :=
                                                        :NEW.my_test_end_date;
   my_test_temporary_data.v_my_temp_table
                                  (my_test_temporary_data.v_number_of_entries).my_test_description :=
                                                     :NEW.my_test_description;
END my_test_no_overlap_dates_step1;
/
SHOW ERRORS;



CREATE OR REPLACE TRIGGER MY_TEST_NO_OVERLAP_DATES_STEP2
   AFTER INSERT OR UPDATE
   ON MY_TEST
DECLARE
   v_temp_id       MY_TEST.my_test_id%TYPE; --used in error message
   my_var_a        NUMBER                                 := 0;
   my_var_b        NUMBER                                 := 0;
BEGIN
   FOR i IN 1 .. my_test_temporary_data.v_number_of_entries
   LOOP

	  v_temp_id :=my_test_temporary_data.v_my_temp_table (i).my_test_id;

/* The tricky part here was remembering that the select
   below must include data that was just inserted or updated! */
      SELECT COUNT (*)
        INTO my_var_a
        FROM (SELECT 'x'
                FROM MY_TEST
               WHERE my_test_temporary_data.v_my_temp_table (i).my_test_start_date
                        BETWEEN my_test_start_date
                            AND my_test_end_date
                 AND my_test_temporary_data.v_my_temp_table (i).my_test_id <>
                                                                    my_test_id);

      SELECT COUNT (*)
        INTO my_var_b
        FROM (SELECT 'x'
                FROM MY_TEST
               WHERE my_test_temporary_data.v_my_temp_table (i).my_test_end_date
                        BETWEEN my_test_start_date
                            AND my_test_end_date
                 AND my_test_temporary_data.v_my_temp_table (i).my_test_id <>
                                                                    my_test_id);

      IF my_var_a <> 0 AND my_var_b = 0
      THEN
      -- reset values:
      my_test_temporary_data.v_my_temp_table := my_test_temporary_data.v_empty_table;
      my_test_temporary_data.v_number_of_entries := 0;

         RAISE_APPLICATION_ERROR
                     (-20020,
                         'Cannot insert or update! Date Conflict! '
                      || CHR (10)
                      || '(MY_TEST_ID: '
                      ||  TO_CHAR(v_temp_id)
                      || ' -- my_test_start_date conflicts)'
                     );
      END IF;

      IF my_var_b <> 0 AND my_var_a = 0
      THEN
      -- reset values:
      my_test_temporary_data.v_my_temp_table := my_test_temporary_data.v_empty_table;
      my_test_temporary_data.v_number_of_entries := 0;

         RAISE_APPLICATION_ERROR
                     (-20021,
                         'Cannot insert or update! Date Conflict! '
                      || CHR (10)
                      || '(MY_TEST_ID: '
                      ||  TO_CHAR(v_temp_id)
                      || ' -- my_test_end_date conflicts)'
                     );
      END IF;

      IF my_var_b <> 0 AND my_var_a <> 0
      THEN
      -- reset values:
      my_test_temporary_data.v_my_temp_table := my_test_temporary_data.v_empty_table;
      my_test_temporary_data.v_number_of_entries := 0;

         RAISE_APPLICATION_ERROR
                     (-20022,
                         'Cannot insert or update! Date Conflict! '
                      || CHR (10)
                      || '(MY_TEST_ID: '
                      || TO_CHAR(v_temp_id)
                      || ' -- Both Dates)'
                     );
      END IF;

   END LOOP;

      -- reset values:
      my_test_temporary_data.v_my_temp_table := my_test_temporary_data.v_empty_table;
      my_test_temporary_data.v_number_of_entries := 0;

EXCEPTION
   WHEN OTHERS
   THEN
      -- reset values:
      my_test_temporary_data.v_my_temp_table := my_test_temporary_data.v_empty_table;
      my_test_temporary_data.v_number_of_entries := 0;
      RAISE;
END;
/
SHOW ERRORS;



ALTER TABLE MY_TEST ADD (
  CONSTRAINT PK_MY_TEST_ID PRIMARY KEY (MY_TEST_ID)
    USING INDEX 
    TABLESPACE DEVELOPMENT
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       2147483645
                PCTINCREASE      0
               ));


INSERT INTO MY_TEST ( MY_TEST_ID, MY_TEST_START_DATE, MY_TEST_END_DATE,
MY_TEST_DESCRIPTION ) VALUES ( 
1,  TO_Date( '01/10/2005 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),  TO_Date( '01/15/2005 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'Trip to Pasadena'); 
INSERT INTO MY_TEST ( MY_TEST_ID, MY_TEST_START_DATE, MY_TEST_END_DATE,
MY_TEST_DESCRIPTION ) VALUES ( 
2,  TO_Date( '01/22/2005 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),  TO_Date( '01/24/2005 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'Trip to Glendale'); 
INSERT INTO MY_TEST ( MY_TEST_ID, MY_TEST_START_DATE, MY_TEST_END_DATE,
MY_TEST_DESCRIPTION ) VALUES ( 
3,  TO_Date( '02/02/2005 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),  TO_Date( '02/08/2005 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'Trip to Los Angeles'); 
COMMIT;


select * from my_test
MY_TEST_ID MY_TEST_START_DATE    MY_TEST_END_DATE      MY_TEST_DESCRIPTION      
---------- --------------------- --------------------- -------------------------
         1 1/10/2005             1/15/2005             Trip to Pasadena         
         2 1/22/2005             1/24/2005             Trip to Glendale         
         3 2/2/2005              2/8/2005              Trip to Los Angeles      
3 rows selected
 


INSERT INTO MY_TEST
     VALUES (4, TO_DATE ('1/10/2005', 'MM/DD/YYYY'),
             TO_DATE ('1/19/2005', 'MM/DD/YYYY'), 'Trip to Germany');
 
ORA-20020: Cannot insert or update! Date Conflict! 
(MY_TEST_ID: 4 -- my_test_start_date conflicts)
ORA-06512: at "OPS$STOEVER.MY_TEST_NO_OVERLAP_DATES_STEP2", line 93
ORA-04088: error during execution of trigger 'OPS$STOEVER.MY_TEST_NO_OVERLAP_DAT

INSERT INTO MY_TEST
     VALUES (4, TO_DATE ('6/10/2005', 'MM/DD/YYYY'),
             TO_DATE ('6/9/2005', 'MM/DD/YYYY'), 'Trip to Germany');
ORA-20023: Cannot insert of update! Date Conflict! my_test_start_date come AFTER
ORA-06512: at "OPS$STOEVER.MY_TEST_NO_OVERLAP_DATES_STEP1", line 4
ORA-04088: error during execution of trigger 'OPS$STOEVER.MY_TEST_NO_OVERLAP_DAT

INSERT INTO MY_TEST
     VALUES (4, TO_DATE ('3/10/2005', 'MM/DD/YYYY'),
             TO_DATE ('3/13/2005', 'MM/DD/YYYY'), 'Trip to Germany');
			 
COMMIT;

select * from my_test;
MY_TEST_ID MY_TEST_START_DATE    MY_TEST_END_DATE      MY_TEST_DESCRIPTION      
---------- --------------------- --------------------- -------------------------
         1 1/10/2005             1/15/2005             Trip to Pasadena         
         2 1/22/2005             1/24/2005             Trip to Glendale         
         3 2/2/2005              2/8/2005              Trip to Los Angeles      
         4 3/10/2005             3/13/2005             Trip to Germany          
4 rows selected
 


INSERT INTO MY_TEST
     VALUES (5, TO_DATE ('3/11/2005', 'MM/DD/YYYY'),
             TO_DATE ('3/13/2005', 'MM/DD/YYYY'), 'Trip to Hong Kong');
ORA-20022: Cannot insert or update! Date Conflict! 
(MY_TEST_ID: 5 -- Both Dates)
ORA-06512: at "OPS$STOEVER.MY_TEST_NO_OVERLAP_DATES_STEP2", line 92
ORA-04088: error during execution of trigger 'OPS$STOEVER.MY_TEST_NO_OVERLAP_DAT