Re: table mutating error - how do i get the :old, :new values?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 26 Jul 2001 16:46:33 -0700
Message-ID: <9jqa4p02dt6@drn.newsguy.com>

In article <3B607A27.16CBB9C0_at_attws.com>, "Daniel says...
>
>Udaya wrote:
>
>> Hi,
>>

...

>>
>> Thanks in advance,
>> Udaya.
>
>A trigger can not modify records in its host table except the single
>specific record whose values are in the trigger as :OLD/:NEW.
>
>Daniel A. Morgan
>

Or in the other cases of the BEFORE and AFTER trigger -- they most certainly may modify the table, do it all of the time. It is only a row level trigger that cannot modify the table upon which it is firing.

You do have to be careful to prevent recursive firing of your own triggers.

This example just shows it is possible, doesn't do anything meaningful really:

ops$tkyte_at_ORA817.US.ORACLE.COM> create or replace package state_pkg   2 as
  3 in_trigger_already boolean default FALSE;   4 end;
  5 /

Package created.

ops$tkyte_at_ORA817.US.ORACLE.COM>
ops$tkyte_at_ORA817.US.ORACLE.COM> drop table t;

Table dropped.

ops$tkyte_at_ORA817.US.ORACLE.COM> create table t ( x int );

Table created.

ops$tkyte_at_ORA817.US.ORACLE.COM>
ops$tkyte_at_ORA817.US.ORACLE.COM> create trigger t_au 2 after update on t
3 begin

  4          if ( NOT state_pkg.in_trigger_already )
  5          then
  6                  state_pkg.in_trigger_already := TRUE;
  7                  begin
  8                          update t set x = x+1;
  9                          state_pkg.in_trigger_already := FALSE;
 10                  exception
 11                    when others then state_pkg.in_trigger_already := FALSE;
                             RAISE;
 12                  end;
 13          end if;

14 end;
15 /

Trigger created.

ops$tkyte_at_ORA817.US.ORACLE.COM> show err No errors.
ops$tkyte_at_ORA817.US.ORACLE.COM>
ops$tkyte_at_ORA817.US.ORACLE.COM> insert into t values ( 1 );

1 row created.

ops$tkyte_at_ORA817.US.ORACLE.COM>
ops$tkyte_at_ORA817.US.ORACLE.COM> update t set x = 2;

1 row updated.

ops$tkyte_at_ORA817.US.ORACLE.COM>
ops$tkyte_at_ORA817.US.ORACLE.COM> select * from t;

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp

Received on Thu Jul 26 2001 - 18:46:33 CDT