Re: Triggers

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 01 Oct 1999 12:00:57 -0400
Message-ID: <etb0N0gZwb5n1rOIJsR8V5StswBU@4ax.com>

A copy of this was sent to flavoue_at_my-deja.com (if that email address didn't require changing) On Fri, 01 Oct 1999 14:32:19 GMT, you wrote:

>Hi,
>Here is my problem :
>I have one table that I would like to update automatically.
>I used triggers to do that but it doesn't work.
>
>Table :
>ID_objet Attribute Value
>
>A Z1 12
>A Z2 10
>A Z3 120 (12*10)
>
>B Z1 5
>B Z2 3
>B Z3 15 (5*3)
>
>I would like to calculate automatically the values 120 and 15 each time
>I insert an object
>X with the attributes Z1 and Z2.
>The help documentation of oracle says that I have to create 2 triggers,
>one (type : for each row) that insert in a temporary temble the value
>of the product and
>one that update (type : for statement) the table. The second doesn't
>work.
>Does any body know why?
>

very hazy details here. very very hazy.

Actually, to avoid the mutating table error, you can use 3 triggers and package (i find that better then a scratch table). You can see the URL in my signature for a howto on mutating tables.

You *probably* have a recursive error in your trigger. When you get to the AFTER statement trigger, you are going back to update that same table. That will fire your triggers again.

Here is an example that shows how to avoid the mutating table error, the recursion you will hit, and not use a temporary table:

tkyte_at_8.0> create table t ( id char(1), attr char(2), val int );

Table created.

tkyte_at_8.0> 
tkyte_at_8.0> 
tkyte_at_8.0> create or replace package t_state_pkg
  2  as
  3      type array is table of t.id%type index by binary_integer;
  4  
  4      newones    array;
  5      empty    array;
  6  
  6      is_recursive boolean default FALSE;

7 end;
8 /

Package created.

tkyte_at_8.0>
tkyte_at_8.0> create or replace trigger t_biu 2 before insert or update on T
3 begin

  4      if ( NOT t_state_pkg.is_recursive )
  5      then
  6          t_state_pkg.newones := t_state_pkg.empty ;
  7      end if;

8 end;
9 /

Trigger created.

tkyte_at_8.0>
tkyte_at_8.0> create or replace trigger t_aiufer 2 after insert or update on T for each row 3 begin

  4      if ( NOT t_state_pkg.is_recursive )
  5      then
  6          t_state_pkg.newones(t_state_pkg.newones.count+1) := :new.id;
  7      end if;

8 end;
9 /

Trigger created.

tkyte_at_8.0>
tkyte_at_8.0> create or replace trigger t_aiu   2 after insert or update on T
  3 begin
  4 if ( t_state_pkg.is_recursive ) then return; end if;   5
  5 t_state_pkg.is_recursive := TRUE;   6

  6      for i in 1 .. t_state_pkg.newones.count loop
  7          update T
  8            set val = ( select t1.val*t2.val
  9                          from t t1, t t2
 10                         where t1.id = t_state_pkg.newones(i)
 11                           and t1.attr = 'Z1'
 12                           and t2.id = t_state_pkg.newones(i)
 13                           and t2.attr = 'Z2' )
 14          where id = t_state_pkg.newones(i)
 15            and attr = 'Z3';
 16  
 16          if ( sql%rowcount = 0 ) then
 17              insert into t ( id, attr, val )
 18              select t_state_pkg.newones(i), 'Z3', t1.val*t2.val
 19                from t t1, t t2
 20               where t1.id = t_state_pkg.newones(i)
 21                 and t1.attr = 'Z1'
 22                 and t2.id = t_state_pkg.newones(i)
 23                 and t2.attr = 'Z2';
 24          end if;
 25      end loop;
 26  
 26      t_state_pkg.is_recursive := FALSE;
 27  exception
 28      when others then
 29          t_state_pkg.is_recursive := FALSE;
 30          RAISE;

31 end;
32 /

Trigger created.

tkyte_at_8.0>
tkyte_at_8.0> insert into t values ( 'A', 'Z1', 12 );

1 row created.