CODEWITHSUNDEEP
oracle-databasetriggersinsert
FenrisL
FenrisL

Reputation: 287

Creating a Trigger in Oracle to check date between tables

I have 2 tables in my Oracle DB

CLIENT_ID(PK) -  INSERT_DATE

    1         -  1/1/2017  


CLIENT_ID(FK) -  EXIT_DATE

    1         -  5/1/2017

I want to create a Before Insert Trigger that will displays an error if i try to insert a date on EXIT_DATE that is older than the INSERT_DATE column.

For example if i try to insert 31/12/2016 it will display an error "exit date cannot be older than the INSERT DATE" My problem also is that the trigger must match the Client_ID too between the 2 tables. (I use Oracle 11g)

Upvotes: 0

Views: 1718

Answers (1)

alexgibbs
alexgibbs

Reputation: 2480

Edited with info that there is a FOREIGN KEY preventing orphan CLIENT_IDs.

To generate a trigger that enforces the rule you've described, the below will work.
First, create some test tables:

CREATE TABLE INSERT_TABLE(
  CLIENT_ID NUMBER NOT NULL PRIMARY KEY,
  INSERT_DATE DATE NOT NULL
);

CREATE TABLE EXIT_TABLE(
  CLIENT_ID NUMBER NOT NULL REFERENCES INSERT_TABLE(CLIENT_ID),
  EXIT_DATE DATE NOT NULL
);
CREATE INDEX EXIT_INSERT_FKI ON EXIT_TABLE(CLIENT_ID);

Then, apply the trigger:

CREATE OR REPLACE TRIGGER EXIT_AFTER_INSERT_ENFORCER
BEFORE INSERT ON EXIT_TABLE
FOR EACH ROW
  DECLARE
    V_INSERT_DATE DATE;
  BEGIN
    SELECT INSERT_TABLE.INSERT_DATE
    INTO V_INSERT_DATE
    FROM INSERT_TABLE
    WHERE INSERT_TABLE.CLIENT_ID = :NEW.CLIENT_ID;

    IF :NEW.EXIT_DATE < V_INSERT_DATE
    THEN RAISE_APPLICATION_ERROR(-20100, 'EXIT DATE cannot be older than the INSERT DATE');
    END IF;
EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
  END;
/

Note: a trigger is not the only way to accomplish this. A MATERIALIZED VIEW could also enforce this constraint via a CHECK CONSTRAINT. Also alternative modeling will simplify things. I'll detail an alternative a little further below.

Then testing the trigger:

The following is ok: 

INSERT INTO INSERT_TABLE VALUES (1, TO_DATE('20170101', 'YYYYMMDD'));
INSERT INTO EXIT_TABLE VALUES (1, TO_DATE('20170501', 'YYYYMMDD'));
1 row inserted.
1 row inserted.

But the following is blocked, since the exit-date preceeds the insert date: 

INSERT INTO INSERT_TABLE VALUES (2, TO_DATE('20170101', 'YYYYMMDD'));
INSERT INTO EXIT_TABLE VALUES (2, TO_DATE('19910501', 'YYYYMMDD'));

ORA-20100: exit date cannot be older than the INSERT DATE

This should accomplish what you described.

I would add though as an additional consideration, that alternative data models can help with constraints like this. If the INSERT_DATE and the EXIT_DATE are both attributes of the same Client, that an alternative design with only one table can enforce this more simply:

CREATE TABLE INSERT_EXIT(
  CLIENT_ID NUMBER NOT NULL PRIMARY KEY,
  INSERT_DATE DATE NOT NULL,
  EXIT_DATE DATE NULL,
  CONSTRAINT EXIT_AFTER_INSERT CHECK (EXIT_DATE >= INSERT_DATE)
);

In this table, the following are allowed:

INSERT INTO INSERT_EXIT VALUES (1,TO_DATE('20170101', 'YYYYMMDD'),NULL);
INSERT INTO INSERT_EXIT VALUES (2,TO_DATE('20170101', 'YYYYMMDD'),TO_DATE('20170501', 'YYYYMMDD'));
1 row inserted.
1 row inserted.

But the following is disallowed naturally:

INSERT INTO INSERT_EXIT VALUES (3,TO_DATE('20170101', 'YYYYMMDD'),TO_DATE('19700101', 'YYYYMMDD'));

ORA-02290: check constraint (MYSCHEMA.EXIT_AFTER_INSERT) violated

Upvotes: 2

Related Questions