Skip to Main Content
  • Questions

  • Mutating Trigger with insert as select

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Susan.

Asked: March 17, 2005 - 10:01 am UTC

Last updated: March 19, 2005 - 5:29 am UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

We have a table (T1) that inserts an id through a trigger using the dreaded select MAX(id)+1 method. I know this is a horrible method and the fix is to use a sequence, but, I was curious about something. When an insert is done as insert into T1 (select c1 from T2) the inserts fails with a table is mutating error. However when we just do insert into T1 values ('ZYZ') it works. Why the difference? Also, I experimented with making the trigger an autonomous transaction (just experimenting - I know this is a bad idea) and tried the insert into select from which errored out with a cannot use autonomous transaction in a distributed environment. So, an insert as a select is considered a disributed environment? Thanks.

and Tom said...

it is worse than horrible.



insert into t values ( 'xyz' );

is KNOWN to insert one row. The table is known to be consistent since there is just ONE row.

In the case of a multi-row insert, insert into t select .... -- the row triggers fire AS the rows are inserted and the trigger would see totally inconsistent results.

so the single row insert is just a special case.


autonomous transactions would definitely not work -- as they would not see the value of the "max" of the previously inserted rows!

definitely time to fix this for once and for all -- why cannot you just drop the trigger and create it with select s.nextval into :new.id? (start s with a value larger than max(id) is now)

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Just a thought

reviewer, March 17, 2005 - 2:31 pm UTC

I have seen cases where for some reason (usually not a valid one) people are overly conserned with having the sequence numbers completly in sync. I was wondering if creating a view like the one below would be a valid solution to that problem:

SELECT
row_number() over(order by id) as id
, ...rest of the columns
FROM
table t

That would always give the numbers in order, and you could even use an instead of trigger to do the updates(in order to match the row_number output with the id output).

Tom Kyte
March 17, 2005 - 3:50 pm UTC

the row_number() would give you a sequential number -- at a high cost. And it would be a number that is not imutable (usually a requirement, the number cannot change)

Sequences without skips are never useful

Mike Friedman, March 19, 2005 - 5:29 am UTC

I have yet to see an application with a real need for real time sequential item numbering.

All cases I have seen to date are purely esthetic (ie. "I want my order numbers in seqence, wah!") or do not require numbers set at insert (ie. Order Line Numbers which can just be displayed in order when needed or added after the fact to order lines).


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library