[row trigger] generic access to fields

TPD-Opitz

Hello,

I'm trying to create an after insert/update row trigger that copies (all) current values to a corresponding history table. I'd like to create a generic triger that does not need any change if source and target table have their fields modified.

Unfortunately a insert into target_table select :new.* from dual; does not compile on a 10g database.

insert into target_table select * from source_table where ID = :new.id; raises the mutating table error and encapsulating this select in an autonomous transaction does not see the new values.

The only way out of this problem I found was to remember the altered ID's in a temporary table and copy the values within an after statement trigger.

Is there any other solution?

bye
TPD

Hoek

Why not use AUDIT instead of going down a cumbersome road?

Read about it in the Online Oracle Docs:
http://www.oracle.com/pls/db102/search?word=audit+DML&partno=
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_4007.htm#SQLRF01107

Here's an ancient thread regarding using a 'generic tigger':
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:59412348055

Hoek

Why not use AUDIT instead of going down a cumbersome road?

Read about it in the Online Oracle Docs:
http://www.oracle.com/pls/db102/search?word=audit+DML&partno=
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_4007.htm#SQLRF01107

Here's an ancient thread regarding using a 'generic tigger':
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:59412348055

TPD-Opitz

hoek wrote:
Why not use AUDIT instead of going down a cumbersome road?

I'm currently not in the position to start a revolution... ;o)

Read about it in the Online Oracle Docs:
[...]
Here's an ancient thread regarding using a 'generic tigger':
[...]

Thanks for that quick answer!

bye
TPD

MichaelS

insert into target_table select * from source_table where ID = :new.id; raises the mutating table error and encapsulating this select in an autonomous transaction does not see the new values.

You can avoid a mutating trigger and SELECT the new rows by using a loopback db-link:

SQL> create table t (a int)
/
Table created.

SQL> create or replace trigger t_trg
   after insert
   on t
   for each row
declare
   l              int;
begin
   execute immediate 'select a from [email protected] where a = :a' into l using :new.a;

   dbms_output.put_line ('a = ' || l || ' already inserted');
end t_trg;
/
Trigger created.

SQL> insert into t select level from dual connect by level <= 3
/
a = 1 already inserted
a = 2 already inserted
a = 3 already inserted
3 rows created.

Hoek

+...flabbergasted...+

Amazing! Where/how did you discover that?

MichaelS

Where/how did you discover that?

Where else as in the documentation?

Mutating-Table Restriction:

»
...
Similarly, the database does not enforce the mutating-table restriction for tables in the same database that are connected by loop-back database links. A loop-back database link makes a local table appear remote by defining an Oracle Net path back to the database that contains the link.
«

Hoek

smacks forehead

Ah, I should have known! And it was already documented in the 9i docs, I just found out...
Thanks for sharing, Michael, never seen that approach before (and probably chances are small I'll see it again on a production system), but these are nice things to know

Toon Koppelaars

You can avoid a mutating trigger and SELECT the new rows by using a loopback db-link:

You can avoid the mutating table error in your trigger, yes, but you cannot avoid the fact that you have now potentially built indeterministic software, which is why Oracle traps a row trigger that selects from the table that's currently being mutated.

MichaelS

you have now potentially built indeterministic software

Toon,

I don't see any kind of indeterminism here? Seems to me like a straightforward procedural kind of way of programming (though maybe not quite that elegant). Somehow similar to a DML statement referencing the same table:

  update yourtable set some_column = (select some_other_column from yourtable where some_condition)

Even transactional integrity seems not to be violated.
Would you mind to elaborate - with an example - on how indeterminism would come into play?

which is why Oracle traps a row trigger that selects from the table that's currently being mutated.

never actually really understood why it is not treated somehow similar to my above example

Toon Koppelaars

MichaelS wrote:

you have now potentially built indeterministic software

I don't see any kind of indeterminism here?

Depends...
Would you always expect the output to be in that order? Maybe the next (patch) release of Oracle has some radical different way of computing the resultset of that query you wrote which returns those 3 rows in a different order:

a = 3 already inserted
a = 2 already inserted
a = 1 already inserted

So you install that patch and bingo, your software behaves differently. The behavior of your code now depends on the order in which the rows get processed.

Let me explain with a different, somewhat more realistic row-trigger example (your's just proves the point that loopback links don't suffer the mutating table error).

Let's create a little EMP table:

SQL> connect work/[email protected]
Connected.

Session altered.

SQL>
SQL> drop table emp
  2  /

Table dropped.

SQL>
SQL> create table emp
  2  (empno     number not null primary key
  3  ,ename     varchar2(20) not null
  4  ,job       varchar2(10) not null
  5  ,deptno    number       not null
  6  ,salary    number(6,2)  not null)
  7  /

Table created.

SQL>
SQL> insert into emp values(100,'xxx','MANAGER',42,9000);

1 row created.

SQL> insert into emp values(200,'yyy','TRAINER',42,7000);

1 row created.

SQL> insert into emp values(300,'zzz','ADMIN',42,4000);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from emp;
More...

     EMPNO ENAME                JOB            DEPTNO     SALARY
---------- -------------------- ---------- ---------- ----------
       100 xxx                  MANAGER            42       9000
       200 yyy                  TRAINER            42       7000
       300 zzz                  ADMIN              42       4000

3 rows selected.

SQL>

And create a loopback link:

SQL> drop database link loopback
  2  /

Database link dropped.

SQL> create database link loopback connect to work identified by work using 'ORCL'
  2  /

Database link created.

SQL>

Now we will build code to enforce a business rule: sum(salary) cannot exceed 20000 in EMP for department 42.
We build a function to retrieve the sum(salary) of deptartment 42, and use that in a row trigger to check this rule.

SQL> create or replace function f_get_sumsal return number is
  2  pl_sum number;
  3  begin
  4    --
  5    select sum(salary) into pl_sum
  6    from emp
  7    where deptno = 42;
  8    --
  9    return pl_sum;
 10    --
 11  end;
 12  /

Function created.

SQL>
SQL> create or replace trigger non_deterministic
  2  after update on emp
  3  for each row
  4  begin
  5    --
  6    if :new.deptno = 42 and :new.salary > :old.salary
  7    then
  8      --
  9      if f_get_sumsal > 20000
 10      then
 11        --
 12        raise_application_error(-20000,'Salary budget of department 42 cannot exceed 20000.');
 13        --
 14      end if;
 15      --
 16    end if;
 17    --
 18  end;
 19  /

Trigger created.

SQL>

And sure enough, if we update the rows, Oracle throws the mutating table at us:

SQL> update emp set salary = 6000;
update emp set salary = 6000
       *
ERROR at line 1:
ORA-04091: table WORK.EMP is mutating, trigger/function may not see it
ORA-06512: at "WORK.F_GET_SUMSAL", line 5
ORA-06512: at "WORK.NON_DETERMINISTIC", line 6
ORA-04088: error during execution of trigger 'WORK.NON_DETERMINISTIC'

SQL>

So lets "fix" this by using the loopback database link:

SQL> create or replace function f_get_sumsal return number is
  2  pl_sum number;
  3  begin
  4    --
  5    select sum(salary) into pl_sum
  6    from [email protected]
  7    where deptno = 42;
  8    --
  9    return pl_sum;
 10    --
 11  end;
 12  /

Function created.

SQL>
SQL> alter trigger non_deterministic compile
  2  /

Trigger altered.

SQL>

And now the update works (note by the way that the sum(sal) becomes 18000, which is allowed by our trigger):

SQL> update emp set salary = 6000;

3 rows updated.

SQL>

Now let's change the order of the rows in that table (note the MANAGER now comes last):

SQL> rollback;

Rollback complete.

SQL>
SQL> delete from emp where empno=100;

1 row deleted.

SQL> insert into emp values(100,'xxx','MANAGER',42,9000);

1 row created.

SQL> commit;
SQL> select * from emp;
More...

     EMPNO ENAME                JOB            DEPTNO     SALARY
---------- -------------------- ---------- ---------- ----------
       200 yyy                  TRAINER            42       7000
       300 zzz                  ADMIN              42       4000
       100 xxx                  MANAGER            42       9000

3 rows selected.

SQL>

So same table (only rows are stored in different order), same trigger. And now let's update with the same update statement:

SQL> update emp set salary = 6000;
update emp set salary = 6000
       *
ERROR at line 1:
ORA-20000: Salary budget of department 42 cannot exceed 20000.
ORA-06512: at "WORK.NON_DETERMINISTIC", line 9
ORA-04088: error during execution of trigger 'WORK.NON_DETERMINISTIC'


SQL>

Make sure you understand why our trigger raises the application error now, and why it didn't in the previous case: it is because our trigger now sees different intermediate table-states that exist during execution of our update. Which states the trigger sees, depends on the order in which the rows are processed. Code we build, should be immune to this order...

Voila. We have built non-deterministic software: sometimes it allows an update to succesfully complete, sometimes it doesn't. It all depends on the order of the rows stored (physicallY) in the data block, which drives (in this case) the order in which the rows are updated.

[some other thoughts...]
When Oracle opens a db-link, it also communicates scn numbers between the two sessions that now live as one. This has as a side-effect that whenever you open a loopback db-link, the db-link session is able to see the changes that are posted (but not yet committed) by the 'parent' session.
By the way: I think it's absolutely radical that this other(!) database session is in fact able to see the intermediate table states.
[some other thoughts...]

You also say:

Seems to me like a straightforward procedural kind of way of programming (though maybe not quite that elegant). Somehow similar to a DML statement referencing the same table:

update yourtable 
set some_column = (select some_other_column from yourtable where some_condition)

I assume that the [some_condition] at the end has a correlation with the 'current' row that's being updated otherwise the whole subquery could be computed once, and some_column gets the same value for all rows updated.
So the correlated subquery needs to be re-evaluated for every row updated.

Well it's not similar...

In the case above Oracle ensures that the (correlated) subquery always and only sees the begin state of yourtable. It never sees a partially updated table, which could influence the outcome (i.e. the end state) of the whole update statement, depending on the order in which the rows got updated. And you don't want that. Oracle prevents that for you by giving a read-consistent copy of yourtable to every evaluation of that subquery.

So it's not similar: the outcome of that update does not depend on the order in which the rows got updated.
Verstehen Sie? ;-)

Moral of this whole story (part 1): you need to understand why Oracle throws the 'mutating table' exception. Once you do, you should be happy that you're not working with some other DBMS that doesn't.

Moral of this whole story (part 2): the indeterminism only manifests itself when a dml-statement (insert,update,delete) processes multiple rows. If your software only ever processes one row at a time, then whatever code you put in a row-trigger always executes in a deterministic way: before-row see the begin state of the table, after-row see the end-state of the table. In fact row-triggers in this case are the same as statement-triggers.

Hoek

Great explanation/example. Thanks a lot, Toon.