trigger in oracle that ensures that the value of ExpDate from the table ExpIt is less than or equal to the ERSubDate from the ExpReport

Question

I am trying to create a trigger in oracle that ensures that the value of ExpDate from the table ExpIt is less than or equal to the ERSubDate from the ExpReport on INSERT and UPDATE statements that change the ExpDate in the ExpIt table.

When ran in the command prompt, the following comes up

warning:  Trigger created with compilation errors.

Here is what I have tried so far, where am I going wrong?

Thank you in advance.

CREATE OR REPLACE TRIGGER Expense_Date

BEFORE INSERT OR UPDATE OF ExpDate
ON ExpIt
FOR EACH ROW
DECLARE 
    anExpDate ExpIts.ExpDate%TYPE;
    anERSubDate ExpReport.ERSubDate%TYPE;
    DateError EXCEPTION;
    ExMessage VARCHAR(200);
BEGIN
   SELECT ExpDate, ERSubDate
     INTO anExpDate, anERSubDate
     FROM ExpIt, ExpReport
     WHERE ExpIt.ExpDate = :NEW.ExpDate;

   IF anExpDate <= anERSubDate THEN
      RAISE DateError;
   END IF;
EXCEPTION
  WHEN DateError THEN
   
     ExMessage := ExMessage || 'Expense Date is Incorrect as it is after the Expense Report Submition date' || 
              to_date(anExpDate);
     raise_application_error(-20001, ExMessage);
END;
/

Answer 1

Before you go too far down this track - be aware that you generally cannot access the table you are triggering on from within the trigger itself.

In your case, your trigger is on EXPIT and you want to query EXPIT. That won't work.

Here's a trivial example of that:

SQL> create table t (x int );

Table created.

SQL> insert into t values (1);

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> create or replace
  2  trigger TRG
  3  before insert on T
  4  for each row
  5  declare
  6    blah int;
  7  begin
  8    select count(*) into blah from t;
  9  end;
 10  /

Trigger created.

SQL>
SQL> insert into t values (2);

1 row created.

It looks fine, but in reality, there are plenty of cases where it will NOT work

SQL> insert into t
  2  select rownum from dual
  3  connect by level <= 5;
insert into t
           *
ERROR at line 1:
ORA-04091: table MCDONAC.T is mutating, trigger/function may not see it
ORA-06512: at "MCDONAC.TRG", line 4
ORA-04088: error during execution of trigger 'MCDONAC.TRG'

This is a big topic, and more details on the issue and how to work around it are here

https://asktom.oracle.com/pls/apex/asktom.search?file=MutatingTable.html#presentation-downloads-reg