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

Question

I am trying to create a simple trigger in an oracle 10g database  This script to Create the trigger runs clean   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                 But when I run   INSERT INTO Alerts observationID  dateSent  message  dateViewed  VALUES  3  CURRENT TIMESTAMP   Alert  You have exceeded the Max Threshold   NULL     to activate the trigger  I get this error message      ORA-04098  trigger  JMD NEWALERT  is invalid and failed re-validation    0 rows affected    I don t understand whats causes this error  Do you know what causes this error  Or why this is happening   Thank you in advance   -David

User · Answer

Cause  A trigger was attempted to be retrieved for execution and was found to be invalid  This also means that compilation authorization failed for the trigger   Action  Options are to resolve the compilation authorization errors  disable the trigger  or drop the trigger   Syntax  ALTER TRIGGER trigger Name DISABLE   ALTER TRIGGER trigger Name ENABLE

User · Answer

in my case  this error is raised due to sequence was not created    CREATE SEQUENCE  J SOME SEQ  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE

User · Answer

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

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

Examples related to sql

Examples related to oracle

Examples related to oracle10g

Examples related to ora-04098