from ora8i documentation:Mutating and Constraining Tables
A mutating table is a table that is currently being modified by an UPDATE, DELETE, or INSERT statement, or it is a table that might need to be updated by the effects of a declarative DELETE CASCADE referential integrity constraint. 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. A table is mutating or constraining only to the session that issued the statement in progress. Tables are never considered mutating or constraining for statement triggers unless the trigger is fired as the result of a DELETE CASCADE. Views are not considered mutating or constraining in INSTEAD OF triggers. 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. The SQL statements of a trigger cannot read from (query) or modify a mutating table of the triggering statement. The statements of a trigger cannot change the PRIMARY, FOREIGN, or UNIQUE KEY columns of a constraining table of the triggering statement. There is an exception to this restriction: For a single row INSERT, constraining tables are mutating for AFTER row triggers, but not for BEFORE row triggers. INSERT statements that involve more than one row, such as INSERT INTO Emp_tab SELECT..., are not considered single row inserts, even if they only result in one row being inserted. Figure 13-1 illustrates the restriction placed on mutating tables. Figure 13-1 Mutating Tables
Notice that the SQL statement is run for the first row of the table, and then an AFTER row trigger is fired. In turn, a statement in the AFTER row trigger body attempts to query the original table. However, because the EMP table is mutating, this query is not allowed by Oracle. If attempted, then a runtime error occurs, the effects of the trigger body and triggering statement are rolled back, and control is returned to the user or application. Consider the following trigger: CREATE OR REPLACE TRIGGER Emp_count
AFTER DELETE ON Emp_tab
FOR EACH ROW
DECLARE
n INTEGER;
BEGIN
SELECT COUNT(*) INTO n FROM Emp_tab;
DBMS_OUTPUT.PUT_LINE(' There are now ' || n ||
' employees.');
END;
If the following SQL statement is entered: DELETE FROM Emp_tab WHERE Empno = 7499;
Then, the following error is returned: ORA-04091: table SCOTT.Emp_tab is mutating, trigger/function may not see it
Oracle returns this error when the trigger fires, because the table is mutating when the first row is deleted. (Only one row is deleted by the statement, because Empno is a primary key, but Oracle has no way of knowing that.) If you delete the line "FOR EACH ROW" from the trigger above, then the trigger becomes a statement trigger, the table is not mutating when the trigger fires, and the trigger does output the correct data. If you need to update a mutating or constraining table, then you could use a temporary table, a PL/SQL table, or a package variable to bypass these restrictions. For example, in place of a single AFTER row trigger that updates the original table, resulting in a mutating table error, you may be able to use two triggers--an AFTER row trigger that updates a temporary table, and an AFTER statement trigger that updates the original table with the values from the temporary table. Declarative integrity constraints are checked at various times with respect to row triggers. See Also:
Oracle8i Concepts has information about the interaction of triggers and integrity constraints.
Because declarative referential integrity constraints are currently not supported between tables on different nodes of a distributed database, the constraining table restrictions do not apply to triggers that access remote nodes. These restrictions are also not enforced among tables in the same database that are connected by loop-back database links. A loop-back database link makes a local table appear remote by defining a Net8 path back to the database that contains the link. You should not use loop-back database links to circumvent the trigger restrictions. Such applications might behave unpredictably.
A mutating table is a table that is currently being modified by an UPDATE, DELETE, or INSERT statement, or it is a table that might need to be updated by the effects of a declarative DELETE CASCADE referential integrity constraint. 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. A table is mutating or constraining only to the session that issued the statement in progress. Tables are never considered mutating or constraining for statement triggers unless the trigger is fired as the result of a DELETE CASCADE. Views are not considered mutating or constraining in INSTEAD OF triggers. 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. The SQL statements of a trigger cannot read from (query) or modify a mutating table of the triggering statement. The statements of a trigger cannot change the PRIMARY, FOREIGN, or UNIQUE KEY columns of a constraining table of the triggering statement. There is an exception to this restriction: For a single row INSERT, constraining tables are mutating for AFTER row triggers, but not for BEFORE row triggers. INSERT statements that involve more than one row, such as INSERT INTO Emp_tab SELECT..., are not considered single row inserts, even if they only result in one row being inserted. Figure 13-1 illustrates the restriction placed on mutating tables. Figure 13-1 Mutating Tables
Notice that the SQL statement is run for the first row of the table, and then an AFTER row trigger is fired. In turn, a statement in the AFTER row trigger body attempts to query the original table. However, because the EMP table is mutating, this query is not allowed by Oracle. If attempted, then a runtime error occurs, the effects of the trigger body and triggering statement are rolled back, and control is returned to the user or application. Consider the following trigger: CREATE OR REPLACE TRIGGER Emp_count
AFTER DELETE ON Emp_tab
FOR EACH ROW
DECLARE
n INTEGER;
BEGIN
SELECT COUNT(*) INTO n FROM Emp_tab;
DBMS_OUTPUT.PUT_LINE(' There are now ' || n ||
' employees.');
END;
If the following SQL statement is entered: DELETE FROM Emp_tab WHERE Empno = 7499;
Then, the following error is returned: ORA-04091: table SCOTT.Emp_tab is mutating, trigger/function may not see it
Oracle returns this error when the trigger fires, because the table is mutating when the first row is deleted. (Only one row is deleted by the statement, because Empno is a primary key, but Oracle has no way of knowing that.) If you delete the line "FOR EACH ROW" from the trigger above, then the trigger becomes a statement trigger, the table is not mutating when the trigger fires, and the trigger does output the correct data. If you need to update a mutating or constraining table, then you could use a temporary table, a PL/SQL table, or a package variable to bypass these restrictions. For example, in place of a single AFTER row trigger that updates the original table, resulting in a mutating table error, you may be able to use two triggers--an AFTER row trigger that updates a temporary table, and an AFTER statement trigger that updates the original table with the values from the temporary table. Declarative integrity constraints are checked at various times with respect to row triggers. See Also:
Oracle8i Concepts has information about the interaction of triggers and integrity constraints.
Because declarative referential integrity constraints are currently not supported between tables on different nodes of a distributed database, the constraining table restrictions do not apply to triggers that access remote nodes. These restrictions are also not enforced among tables in the same database that are connected by loop-back database links. A loop-back database link makes a local table appear remote by defining a Net8 path back to the database that contains the link. You should not use loop-back database links to circumvent the trigger restrictions. Such applications might behave unpredictably.
解决方案 »
免费领取超大流量手机卡，每月29元包185G流量+100分钟通话, 中国电信官方发货