Mutating TableConstraining Table

{\rtf1\ansi\ansicpg1252\deff0\deflang16393{\fonttbl{\f0\froman\fprq2\fcharset0 Times New Roman;}{\f1\froman\fprq2\fcharset2 Symbol;}{\f2\fnil\fcharset0 Calibri;}}
{\*\generator Msftedit 5.41.21.2509;}\viewkind4\uc1\pard\li720\qj\lang1033\f0\fs28 "Mutating" means "changing". A mutating table is a table that is currently being modified by an update, delete, or insert statement. When a trigger tries to reference a table that is in state of flux (being changed), it is considered "mutating" and raises an error since Oracle should not return data that has not yet reached its final state. \par
Another way this error can occur is if the trigger has statements to change the primary, foreign or unique key columns of the table off which it fires. If you must have triggers on tables that have referential constraints, the workaround is to enforce the referential integrity through triggers as well. \par
\pard\li720 A constraining table is a table that a triggering statement might need to read either directly, for a SQL statement, or indirectly, for a declarative referential integrity constraint. \par
\pard\li720\qj There are several restrictions in Oracle regarding triggers: \par
A row-level trigger cannot query or modify a mutating table. (Of course, NEW and OLD still can be accessed by the trigger). \par
A statement-level trigger cannot query or modify a mutating table if the trigger is fired as the result of a CASCADE delete. Etc. \par
\pard\li720 Mutating Table error happens with triggers. It occurs because the trigger is trying to update a row it is currently using. The usual fix involves either use of views or temporary tables so the database is selecting from one while updating the other.\par
For all row triggers, or for statement triggers that were fired as the result of a DELETE CASCADE, there are two important restrictions regarding mutating and constraining tables. These restrictions prevent a trigger from seeing an inconsistent set of data. \par
\pard\fi-360\li720\sb100\sa100\tx720 The SQL statements of a trigger cannot read from (query) or modify a mutating table of the triggering statement. \par
\f1\fs24\'b7\tab\f0\fs28 The statements of a trigger cannot change the PRIMARY, FOREIGN, or UNIQUE KEY columns of a constraining table of the triggering statement.\par
\pard\li720 If you need to update a mutating or constraining table, you could use a temporary table, a PL/SQL table, or a package variable to bypass these restrictions. \par
\pard\sa200\sl276\slmult1\lang9\f2\par
}