Forums | Aggregator | Books | Industry News | Firefox Plugins | Social | Links

Comments

Mutating Table Exceptions - A simple method to prevent triggers producing mutating table exceptions.



Fabrizio Sitzia said...

Excellent article!

It helped me understand, and solve, a similar mutating table problem which was caused by cascaded deletes.

Ernesto Serravalle said...

This article helped me to save a lot of time I would have to waste redesigning a new solution to avoid mutating tables error.

Thank you very much!

Tim... said...

My pleasure.

Cheers

Tim...

Emily said...

The method did solve the mutating table error problem. Thank you very much!!!

Tim said...

This article use sample show readers what is problems
and what is solution
let reader easy to understand it
best article so far since I have read others

Raj said...

Excellent !!

This was not exactly the problem i was facing, but it opened my eyes to a fact that what is mutating within a ROW level trigger may not be mutating in a STATEMENT level trigger.

Hats Off :)

KarthikC said...

Simply Great ............

Srikanth said...

What happens one one insert statement inserts multiple rows? The row account in the audit table wont be accurate. It will reflect the row account at the end of insert stamenet instead of for each row that was inserted.

Mike G. said...

Hi:

Great article! I just have one question. Why was the SELECT COUNT(*) put INSIDE the FOR loop in the following procedure?

PROCEDURE tab1_statement_change IS
l_count NUMBER(10);
BEGIN
FOR i IN g_change_tab.first ..g_change_tab.last LOOP
SELECT COUNT(*)
INTO l_count
FROM tab1;

INSERT INTO tab1_audit (id, action, tab1_id, record_count, created_time)
VALUES (tab1_audit_seq.NEXTVAL, g_change_tab(i).action, g_change_tab(i).id, l_count, SYSTIMESTAMP);
END LOOP;
g_change_tab.delete;
END tab1_statement_change;


I just want to confirm that by the time we get to this statement level trigger, count(*) will always return the same number every time through the loop, correct? Therefore we only need to do it before entering the loop.

Thanks.

DO NOT ask technical questions here! They will be deleted!

These comments should relate to the contents of a specific article. Constructive criticism is good. Advertising and offensive comments are bad and will be deleted!

Add your comments here.
Name
Comment
(max 400 chars - plain text)