Invalid Triggers

3042310

Hello!

We have an Oracle Database 11g Release and some days ago we found a problem with some data of a table because a trigger of the table wasn't executed due to being Invalid. There weren't any errors in the code of the trigger.

I have read that when the trigger is going to be executed, Oracle automatically notices that its status is Invalid and recompile it. So, I doubt why it didn't happen in our case, and if there is an automatic way to avoid this case.

I have found some ways like using "utlrp.sql" or "alter trigger....compile" but to do that we should create some jobs or include that code in every trigger or code. I wonder if there is any automatic code.

Thanks a lot!

Unknown

Triggers don't go invalid just like that.

If they go invalid just like that, the reason is in your code.

There is no reason to set up jobs to automatically recompile and

this community will not be able to resolve your problem without looking at your code.

-----------

Sybrand Bakker

Senior Oracle DBA

John Spencer

There are several reasons why a block of code might go invalid, but Oracle will recompile the code if it can so the code will execute.  If, for whatever reason, the code cannot be recompiled, then Oracle will throw an error:

SQL> create table t (id number, descr varchar2(20));

Table created.

SQL> create table t_hist (id number, descr varchar2(20));

Table created.

SQL> insert into t values (1, 'One');

1 row created.

SQL> commit;

Commit complete.

SQL> create trigger t_bu
  2    before update on t
  3    for each row
  4  begin
  5    insert into t_hist values (:new.id, : old.descr);
  6  end;
  7  /

Trigger created.

SQL> select object_name, status
  2  from user_objects
  3  where object_name = 'T_BU' and
  4        object_type = 'TRIGGER';

OBJECT_NAME    STATUS
--------------- -------
T_BU            VALID

SQL> update t
  2  set descr = 'Un'
  3  where id = 1;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from t_hist;

        ID DESCR
---------- --------------------
        1 One

SQL> alter table t_hist add (udp_dt date);

Table altered.

SQL> select object_name, status
  2  from user_objects
  3  where object_name = 'T_BU' and
  4        object_type = 'TRIGGER';

OBJECT_NAME    STATUS
--------------- -------
T_BU            INVALID

SQL> update t
  2  set descr = 'One'
  3  where id = 1;
update t
      *
ERROR at line 1:
ORA-04098: trigger 'OPS$ORACLE.T_BU' is invalid and failed re-validation

So, either the trigger did try to execute but failed to recompile and the code that executed the triggering event did not report the error (a poor when others handler spring to mind), or the trigger did successfully execute and the trigger code does not actually do what you want it to do, at least in some circumstances.

John

BPeaslandDBA

we found a problem with some data of a table because a trigger of the table wasn't executed due to being Invalid.

At this point in your post, I become suspicious. If your trigger was invalid and could not be made valid, then any DML operation that would cause the trigger to fire would not work. So your data should not have a problem. DML operations would have a problem in that they won't be allowed, but the data in the table won't have a problem. . As proof, I'll offer a simple example:

-- Create a test table.

SQL> create table test_tab (id number);

Table created.

-- create a trigger that will purposely be invalid. I cant insert SYSDATE into a NUMBER datatype.

SQL> create trigger test_tab_trig

  2  before insert on test_tab

  3  declare

  4    v_numbers number;

  5  begin

  6    select sysdate into v_numbers from dual;

  7  end;

  8  /

Warning: Trigger created with compilation errors.

-- Verify trigger is invalid

SQL> select status from dba_objects where object_name='TEST_TAB_TRIG';

STATUS

-------

INVALID

-- Attempt to insert a value into the table.

SQL> insert into test_tab values (10);

insert into test_tab values (10)

            *

ERROR at line 1:

ORA-04098: trigger 'PEASLAND.TEST_TAB_TRIG' is invalid and failed re-validation

-- The insert failed and the data is not in the table.

SQL> select * From test_tab;

no rows selected

So how does your data have a problem if the trigger is invalid? The data in the table still conforms to the trigger's rules and new data is not allowed in.

I have read that when the trigger is going to be executed, Oracle automatically notices that its status is Invalid and recompile it.

That is true. Oracle will attempt to recompile it. However, there is probably an underlying reason why the trigger is invalid. In my example above, I have bad code. Oracle can't fix it and a recompile will still result in an invalid trigger. If the recompile works, then Oracle goes on its merry way. If the recompile fails, the trigger will still be invalid and the DML that caused the trigger to fire will fail.

I wouldn't waste my energies on an automated routine to recompile triggers.

Cheers,
Brian

wbriceno

Hi,

think you have to check trigger status and look for messages in alert log.

This behavior is not normal as indicate BPeaslandDBA.

Regards.

Willy