| |
 |
|
Oracle Tips by Burleson |
PL/SQL,
Triggers, and Mutating Tables
A mutation table is defined as a table that is
changing. But in dealing with triggers, it is a table that has the
possibility of changing. What this means to a trigger is that
if the trigger reads a table, it can not change the table that it read
from. This does not impact the exclusive use of :OLD and :NEW. It
says that if the trigger reads the table (such as using a SELECT
query), that changes (even using :NEW) will fail. This can also
happen when a trigger on a parent table causes an insert on a child
table referencing a foreign key. The insert to the child table caused
the foreign key to validate the data on the parent (which fired the
trigger) causing the insert of the child table to result in a mutating
table error on the parent table.
Each new release of the Oracle database reduces
the impact of the mutating table error on triggers and they are much
less of a problem with Oracle9i and above. If a trigger does
result in a mutating table error, the only real option is to rewrite
the trigger as a statement-level trigger. Mutating table errors only
impact row level triggers. But to use a statement level trigger, some
data may need to be preserved from each row, to be used by the
statement level trigger. This data can be stored in a PL/SQL
collection or in a temporary table. A simple row level trigger that
causes a mutating table error can result in a very complicated
statement level trigger to achieve the needed result.
Here are some important items to remember about
triggers.
-
On insert triggers have no :OLD values.
-
On delete triggers have no :NEW values.
-
Triggers do not commit transactions. If a
transaction is rolled back, the data changed by the trigger is also
rolled back.
-
Commits, rollbacks and save points are not
allowed in the trigger
body. A commit/rollback affects
the entire transaction, it is all or none.
-
Unhandled exceptions in the trigger will
cause a rollback of the entire transaction, not just the trigger.
-
If more than one trigger is
defined on an event, the order in which they fire is not defined.
If the triggers must fire in order, you must create one trigger that
executes all the actions in the required order.
-
A trigger
can cause other events to
execute triggers.
-
A trigger
can not change a table that it
has read from. This is the mutating table error issue.
The fact that a trigger can cause
other triggers to fire is an important item to remember. A trigger
that causes other database events to execute triggers can cause the
database crash. For example, the database can capture server errors
by defining a trigger on the database server error event.
But if this trigger causes a server error, the
database will spin in a loop, with each firing of the trigger causing
the error, firing the trigger again, and again, and again. The only
way to regain control of the database is to disable the trigger.
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
