News

What is mutating trigger in Oracle with an example?

What is mutating trigger in Oracle with an example?

A mutating table error (ORA-04091) occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement). In particular, this error occurs when a row-level trigger attempts to read or write the table from which the trigger was fired.

What are mutating trigger?

Mutating trigger is trigger that is currently being modified by DML opertion. For eg.,You created a trigger trigger1 on table1, it should fire after update for each row. And you wrote some update statement on the same table (table1) inside the trigger .

How do you overcome a mutating trigger in Oracle?

Fixing the mutating table error

  1. First, declare an array of customer record that includes customer id and credit limit.
  2. Second, collect affected rows into the array in the row-level trigger.
  3. Third, update each affected row in the statement-level trigger.

Is mutating trigger function may not see?

table_name is mutating, trigger/function may not see it. In many cases, the cause of this error is due to code within a trigger that looks at or touches the data within the table the trigger is being called or invoked from.

How do I stop Ora 04091 table mutating?

The Oracle mutating trigger error occurs when a trigger references the table that owns the trigger, resulting in the “ORA-04091: table name is mutating, trigger/function may not see it.” message. Don’t use triggers – The best way to avoid the mutating table error is not to use triggers.

What is Pragma AUTONOMOUS_TRANSACTION in Oracle with example?

The AUTONOMOUS_TRANSACTION pragma changes the way a subprogram works within a transaction. A subprogram marked with this pragma can do SQL operations and commit or roll back those operations, without committing or rolling back the data in the main transaction.

Can we use Pragma AUTONOMOUS_TRANSACTION in trigger?

Yes, you can commit inside the trigger. But for this you have to make this trigger transaction to be an Independent transaction from its parent transaction, you can do this by using Pragma. Pragma AUTONOMOUS_TRANSACTION allow you to build the Independent (child) Transaction, started by another.