Skip to Main Content
  • Questions
  • When Oracle Trigger Becomes Invalid? How to fix?

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Mehdi.

Asked: March 16, 2016 - 9:50 pm UTC

Last updated: March 17, 2016 - 2:47 am UTC

Version: 11G

Viewed 10K+ times! This question is

You Asked

Hi Tom,

This Mike,

I would like to know when/how oracle triggers become invalid?

Secondly, if a trigger gets invalid, will it be recompiled automatically next time it is triggered or it should be recompiled manually.

Please let me know.

Thank you,

and Connor said...

When you change something that the trigger depends upon (just like most other plsql) we'll mark the trigger as needing a check. We'll try to auto recompile it, eg

SQL> create table T as select * from all_Objects;

Table created.

SQL>
SQL> create or replace
  2  trigger TRG before insert on T
  3  for each row
  4  begin
  5    :new.object_id := 12;
  6  end;
  7  /

Trigger created.

SQL>
SQL> alter table T drop column owner;

Table altered.

SQL>
SQL> select status from user_objects where object_name = 'TRG';

STATUS
-------
INVALID

SQL>
SQL> insert into T
  2  select
  3  object_name
  4  ,subobject_name
  5  ,object_id
  6  ,data_object_id
  7  ,object_type
  8  ,created
  9  ,last_ddl_time
 10  ,timestamp
 11  ,status
 12  ,temporary
 13  ,generated
 14  ,secondary
 15  ,namespace
 16  ,edition_name
 17  ,sharing
 18  ,editionable
 19  ,oracle_maintained
 20  from all_Objects where rownum = 1;

1 row created.

SQL>
SQL> select status from user_objects where object_name = 'TRG';

STATUS
-------
VALID


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks, this was helpful

A reader, March 08, 2018 - 12:26 pm UTC

After I've done an update on the table that the trigger was created for, the trigger became valid again.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library