-- 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