Oracle Trigger Ora-04098: Trigger Is Invalid and Failed Re-Validation

Oracle Trigger ORA-04098: trigger is invalid and failed re-validation

Oracle will try to recompile invalid objects as they are referred to. Here the trigger is invalid, and every time you try to insert a row it will try to recompile the trigger, and fail, which leads to the ORA-04098 error.

You can select * from user_errors where type = 'TRIGGER' and name = 'NEWALERT' to see what error(s) the trigger actually gets and why it won't compile. In this case it appears you're missing a semicolon at the end of the insert line:

INSERT INTO Users (userID, firstName, lastName, password)
VALUES ('how', 'im', 'testing', 'this trigger')

So make it:

CREATE OR REPLACE TRIGGER newAlert
AFTER INSERT OR UPDATE ON Alerts
  BEGIN
        INSERT INTO Users (userID, firstName, lastName, password)
        VALUES ('how', 'im', 'testing', 'this trigger');
  END;           
/

If you get a compilation warning when you do that you can do show errors if you're in SQL*Plus or SQL Developer, or query user_errors again.

Of course, this assumes your Users tables does have those column names, and they are all varchar2... but presumably you'll be doing something more interesting with the trigger really.

ORA-04098: trigger is invalid and failed re-validation

You are missing a couple of semicolons and the declaration of you variable should be NUMBER.

Please see below correct code:

CREATE OR REPLACE trigger "bi_login"
  before insert on login
  for each row
DECLARE 
  qntdRows number;
BEGIN 
  select "login_seq".nextval
  INTO qntdRows 
  FROM dual;
  :NEW.idLogin := qntdRows;
END;

The above will replace your invalid trigger and will compile without any errors!

Furthermore, you can achieve the same with the below alternate code:

CREATE OR REPLACE trigger "bi_login"
  before insert on login
  for each row
BEGIN 
  :NEW.idLogin := "login_seq".nextval;
END;  

Successful Compilation Screenshot

I find it simpler and more elegant :)

Ted.

Trigger is invalid and failed re-validation

Warning: execution completed with
warning TRIGGER
CDR.ExtDL_JobStatus_SeqTrg Compiled.

This is where your trigger compilation failed.

sql> CREATE TRIGGER ExtDL_JobStatus_SeqTrg
  2  BEFORE INSERT
  3  ON ExtDL_JobStatus
  4      FOR EACH ROW
  5      WHEN (new.Id IS NULL)
  6      BEGIN
  7          SELECT ExtDL_JobStatus_SeqTrg.nextval into :new.Id from dual;
  8      END;
  9  /

Warning: Trigger created with compilation errors.

sql> show errors;
Errors for TRIGGER EXTDL_JOBSTATUS_SEQTRG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/9      PL/SQL: SQL Statement ignored
2/16     PL/SQL: ORA-02289: sequence does not exist

The problem is because you are using ExtDL_JobStatus_SeqTrg in your code and the sequence you created is ExtDL_JobStatus_Seq.

Also, if you are trying to run a script like this for creating (compiling) the objects, I would suggest you add the following clause after each trigger/procedure/function creatin statement.

SHOW ERRORS;

If your statement succceds, that will just produce no errors. If there are any erros, you'll have a detailed description of the errors instead of having to execute the script again.

---

Related Topics