4 Replies Latest reply on May 21, 2009 4:59 AM by 702694

    ora-04098 trigger is invalid and failed re-validation

    702694
      I have a Trigger that will fire whenever any DDL statement is executed.Below the trigger code
      create or replace trigger
         DDLTrigger
      AFTER DDL ON DATABASE
      BEGIN
      insert into
         stats$ddl_log
      (
         user_name,
         ddl_date,
         ddl_type,
         object_type,
         owner,
         object_name
      )
      VALUES
      (
         ora_login_user,
         sysdate,
         ora_sysevent,
         ora_dict_obj_type,
         ora_dict_obj_owner,
         ora_dict_obj_name
      );
      END;
      When i m testing this trigger by truncating a table i m getting ORA-ERROR as
      "ora-04098 trigger is invalid and failed re-validation"

      i have created the trigger as USER . Below is what i get when i logon as SYS and do the following commands

      select * from dba_objects where object_name in ('DDLTRIGGER','STATS$DDL_LOG');
      OWNER     OBJECT_NAME     SUBOBJECT_NAME     OBJECT_ID     DATA_OBJECT_ID     OBJECT_TYPE     CREATED     LAST_DDL_TIME     TIMESTAMP     STATUS     TEMPORARY     GENERATED     SECONDARY
      SCOTT     DDLTRIGGER          30,359.00          TRIGGER     05/20/2009 18:07:36     05/20/2009 18:07:36     2009-05-20:18:07:36     INVALID     N     N     N
      SCOTT     STATS$DDL_LOG          30,347.00     30,347.00     TABLE     05/20/2009 15:39:43     05/20/2009 15:39:43     2009-05-20:15:39:43     VALID     N     N     N
      How should i solve this issue ?

      Edited by: user11187096 on May 20, 2009 9:46 PM