MUTATING TABLE (2)

MUTATING TABLE (2) - MUTATING TABLE INTRODUCTION A mutating...

Info iconThis preview shows pages 1–7. Sign up to view the full content.

View Full Document Right Arrow Icon
MUTATING TABLE
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
INTRODUCTION A mutating table is a table that is in the process of being modified by an UDPATE, DELETE or INSERT statement. A table may also be considered to be mutating if it needs to be updated due to a DELETE CASCADE referential integrity constraint.
Background image of page 2
The reason you may get a mutating table error (i.e. ORA-04091) is that if you fire a trigger which attempts to look at or modify the same table, the trigger is effectively prevented from seeing a consistent view of the data.
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Example Give every employee who makes $10/hour a 10% salary increase as follows: UPDATE employee SET salary = salary * 1.10 WHERE salary = 10 ; This would mean that the changed salary for all these employees would be $11.
Background image of page 4
Your UPDATE statement caused an AFTER ROW trigger to be fired that contained the following statement: SELECT salary FROM employee WHERE . . . You would get the mutating table error because the trigger tried to query the original employee table but the original table was in the process of change.
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
The Problem! Which version of the table should Oracle
Background image of page 6
Image of page 7
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 10

MUTATING TABLE (2) - MUTATING TABLE INTRODUCTION A mutating...

This preview shows document pages 1 - 7. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online