SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

[row trigger] generic access to fields

TPD-OpitzNov 15 2010 — edited Nov 29 2010

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

This post has been answered by Hoek on Nov 15 2010

Jump to Answer

Hoek

Answer

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

Marked as Answer by TPD-Opitz · Sep 27 2020

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 t@loopback 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.

1 person found this helpful

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.
«

1 person found this helpful

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 :(

1 person found this helpful

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/work@ORCL
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 emp@loopback
  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.

1 person found this helpful

Hoek

Great explanation/example. Thanks a lot, Toon.

TPD-Opitz

Hallo Toon,

thank you for that good example.

But:
The only (but valuable) thing about "Mutating Table" Errors is that it fails fast for design errors you described.

The reason why your trigger failed after reordering the entries is, that, as you stated, the function summerises all incomes after each row. In your example the bulk update means a raise for some employees and a decrease for others. That implies that the over all summe at some intermediate state may exceed the limit legally as long as some decreases follow.
So it should be obvious that this is a case for an after statement trigger indeed. And so it is a design error to put such an aggregation function into an each row trigger.

BTW: Restricting statements to update one record at a time only would not solve the problem. In the contrary the ambient logic would have to asure that decreases are applied before the raises to avoid the error! You would have moved the problem to another place...

My conclusion is:
The "Mutating Table" Error prevents unexpereinced progammers from doing logical mistakes that are hard to recover at the cost that programmers knowing their business are forced to do some extra work.

bye
TPD

Toon Koppelaars

T.PD wrote:

My conclusion is:
The "Mutating Table" Error prevents unexpereinced progammers from doing logical mistakes that are hard to recover at the cost that programmers knowing their business are forced to do some extra work.

Well I would like to counter that conclusion by asking you a question (and I might learn something today...):

Can you give me a (reallife) use-case where a programmer 'knowing their business', should be allowed to write a query in a row trigger that reads from the (mutating) table?

TPD-Opitz

Toon Koppelaars wrote:
Can you give me a (reallife) use-case where a programmer [...] should be allowed to write a query in a row trigger that reads from the (mutating) table?

This is from my current project:
I have an action list where each entry has a start and an end date. The restriction is that for a certain resource actions are not allowed to overlap. Therefor I create an after insert each row trigger that closes the previous task automatically as a new one is inserted.

Beause of the mutating table problem I either need an after statement trigger with a rather complex logic to find out actions that became a succesor including a scann over all open actions (which may influence performence) or a combination of ech row and statement triggers passing resource ID's around through temporary tables...

I decided for the latter since the customer is afraid of database links :o(.

The third solution was to force the ambient logic to close the previous entry by issuing an additional update statement, which is eaven worse in my view.

bye
TPD

Toon Koppelaars

Can you show me the table structure?

BEDE

I don't think "generic trigger" is the right thing.
Rather build a stored procedure that can generate triggers for various tables. And, calling that procedure, you will do an execute_immediate 'create trigger...'. And you would call that procedure giving for in parameter the table name.

MichaelS

Great example Toon, thanks for that!

I think I got your point and that gives a good occasion for some brain storming ;)

Now we will build code to enforce a business rule: sum(salary) cannot exceed 20000 in EMP for department 42.

Well, this is indeed something that one would consider NOT to implement in a row level based trigger - for the very reason you just demonstrated. A statement trigger would suffice in this case.

This almost inevitably leads to the point to »always use the right tool for a specific task« (quite often heard in this forum), which probably is one of the most common errors.

In the case above Oracle ensures that the (correlated) subquery always and only sees the begin state of yourtable.

That was my point when I said: »never actually really understood why it is not treated somehow similar to my above example«

To be treated the same the query inside the trigger needs to always start at the same point of time.
It is easily demonstrated with your example if you modify the select as

...
     select sum(salary) into pl_sum  from emp@loopback as of timestamp systimestamp where deptno = 42;
...

In the whole update process the same state of the table will be visible. But then of course you'd never catch the exception to »not exceed 20000 in EMP for department 42«.

So what I wanted to say is: Why doesn't oracle allow a select inside a for each row trigger but have it always in the same state as at the time the transaction actually started (as in a correlated update)? Or maybe even have two variants: One as above and the other following the update process (i.e. changing its state, but also allowing for an ordered update)?

Of course the loopback link is just a evil trick and should not be neccessary in my point of view: It's a hack that might be helpful in some situations though.

Code we build, should be immune to this order...

Of course: But we all now that especially table ordering all by itself is indeterminate. Whenever you have a process that relies on some kind of order well, then you need to ORDER BY somewhere.

Moral of the story (again): Pick the right tool for the task in question and (@oracle: build more flexible tools)

PS: Never had myself a real-life problem where I needed to query the same table again in a row-level trigger - but maybe I will learn something new today too ;)

1 person found this helpful

TPD-Opitz

Toon Koppelaars wrote:
Can you show me the table structure?

It's simple enough:

create table resource_action (
  resource_id number(10) not null,
  action_id number(10) not null,
  start_date date not null,
  end_date date);

With recource_id and action_id as combined primary key refferencing other tables...

In case you suspect a bad table design:
I think separating the dates into a separate table would brake the third normal form since it would lead to a one to one relationship. And it would raise complexity without real benefit.

bye
TPD

Edited by: T.PD on 18.11.2010 13:41 Type of action_id added.

Toon Koppelaars

A few more clarifications:

I have an action list where each entry has a start and an end date. The restriction is that for a certain resource actions are not allowed to overlap. Therefor I create an after insert each row trigger that closes the previous task automatically as a new one is inserted.

1) So they should also be 'consecutive'? I.e. no gaps allowed between two action-periods of the same resource?
If so, do I understand correctly that end-date is in fact a redundant column in that table?

2) Is {resource,begin-date} a unique key in that table? (next to the primary key you already mentioned)

3) Suppose you would be allowed to do the 'loopback' trick: can you show us the row-trigger you would have wanted to use?

TPD-Opitz

Hello Toon,

Toon Koppelaars wrote:
A few more clarifications:
1) So they should also be 'consecutive'? I.e. no gaps allowed between two action-periods of the same resource?

No.
There may be gaps but no overlaps.

If so, do I understand correctly that end-date is in fact a redundant column in that table?

No.
But eaven if the entries where consecutive: Without the end_date column, how would you tell that the last action finnished?

2) Is {resource,begin-date} a unique key in that table? (next to the primary key you already mentioned)

Yes, for logical reasons as well as action_id and the begin_date.

3) Suppose you would be allowed to do the 'loopback' trick: can you show us the row-trigger you would have wanted to use?

create trigger on resource_action before insert each row
begin
  update resource_action@loopback 
     set end_date = :new.start_date
   where end_date is null
     and start_date < :new.start_date
     and (   resource_id = :new.resource_id
          or action_id   = :new.action_id);
end;

There is still a chance to break this wenn a bulk insert would provide different new start dates for each record... but doing bulk inserts for the same resource or action does not make sense anyway. So I do not expect the application to do this.

bye
TPD

Toon Koppelaars

532531 wrote:

Now we will build code to enforce a business rule: sum(salary) cannot exceed 20000 in EMP for department 42.

Well, this is indeed something that one would consider NOT to implement in a row level based trigger - for the very reason you just demonstrated. A statement trigger would suffice in this case.

Agreed, as long as you do something that transfers the knowledge "Hey something changed with department 42" from row-trigger time to statement trigger time. For else you will check that rule at all times, also when something happened to department 7.
Which usually is the reason people (want to) do stuff like this in a row trigger, because at that time, this knowledge is readily available (in :new and/or :old variables).

>

So what I wanted to say is: Why doesn't oracle allow a select inside a for each row trigger but have it always in the same state as at the time the transaction actually started (as in a correlated update)? Or maybe even have two variants: One as above and the other following the update process (i.e. changing its state, but also allowing for an ordered update)?

I'm not convinced yet whether that is an execution model you would want (need to do some thinking on this).

Toon Koppelaars

T.PD wrote:
Hello Toon,

Toon Koppelaars wrote:
A few more clarifications:
1) So they should also be 'consecutive'? I.e. no gaps allowed between two action-periods of the same resource?

No.
There may be gaps but no overlaps.

Your trigger (below) ensures no gaps also. Or am I missing something?

If so, do I understand correctly that end-date is in fact a redundant column in that table?

No.
But eaven if the entries where consecutive: Without the end_date column, how would you tell that the last action finnished?

Just add another table that has zero or one row in it... The row telling that the last action finished.

2) Is {resource,begin-date} a unique key in that table? (next to the primary key you already mentioned)

Yes, for logical reasons as well as action_id and the begin_date.

3) Suppose you would be allowed to do the 'loopback' trick: can you show us the row-trigger you would have wanted to use?

create trigger on resource_action before insert each row
begin
update resource_action@loopback 
set end_date = :new.start_date
where end_date is null
and start_date < :new.start_date
and (   resource_id = :new.resource_id
or action_id   = :new.action_id);
end;

There is still a chance to break this wenn a bulk insert would provide different new start dates for each record...

but doing bulk inserts for the same resource or action does not make sense anyway. So I do not expect the application to do this.

Which was exactly my original point: any row-trigger that queries the mutating table is a potential bug waiting to happen.

TPD-Opitz

Hello Toon,

Toon Koppelaars wrote:
Your trigger (below) ensures no gaps also. Or am I missing something?

Only as long as end_date is not updated by any other process (which is expected).

Just add another table that has zero or one row in it... The row telling that the last action finished.

How would that fit in an ER model?

>

but doing bulk inserts for the same resource or action does not make sense anyway. So I do not expect the application to do this.

Which was exactly my original point: any row-trigger that queries the mutating table is a potential bug waiting to happen.

This is what I ment with knowing their business ...
It's a logical fact in my application that a particular action can not start more than once at a certain point in time and any resource cannot be used mor than once at the same time. That's why bulk inserts using same resource_id or action_id more than once are errors themselfs. That's not a general rule but true for my application.
My task as developer is to point out that problem and vote the risk it carries against the complexity of the code neded to go around it.

bye
TPD

1 - 22

Locked Post

New comments cannot be posted to this locked post.

Locked on Dec 27 2010

Added on Nov 15 2010

#trigger

22 comments

401 views

SQL & PL/SQL

[row trigger] generic access to fields

Comments

Post Details