MUTATING TABLES
There are restrictions on the tables and columns that a trigger body may access. In order to define these restrictions, it is necessary to understand mutating and constraining tables.
A mutating table is table that is currentlty being modified by a DML statement and the trigger event also DML statement. A mutating table error occurs when a row-level trigger tries to examine or change a table that is already undergoing change.
A constraining table is a table that might need to be read from for a referential integrity constraint.
Ex:
CREATE OR REPLACE TRIGGER MUTATING_TRIGGER
before delete on student
for each row
DECLARE
ct number;
BEGIN
select count(*) into ct from student where no = :old.no;
END MUTATING_TRIGGER;
Output:
SQL> delete student where no = 1;
delete student where no = 1
*
ERROR at line 1:
ORA-04091: table SCOTT.STUDENT is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.T", line 4
ORA-04088: error during execution of trigger 'SCOTT.T'
HOW TO AVOID MUTATING TABLE ERROR ?
Ø By using autonomous transaction
Ø By using statement level trigger
Hello There,
ReplyDeleteI’ve often thought about this PLSQL-MUTATING TABLES. Nice to have it laid out so clearly. Great eye opener.
Our project DBA Team is planning to migrate Oracle DB from version 11.2.0.4 to 12.2.0.1.
Could you please let mw know what the Ares which gets impacted from DB Development Team prospective.
Our DBA Team will take care of up gradation
But I want to know any deprecated concepts/impacted Ares in Higher version?
Current Version :Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
Thanks a lot. This was a perfect step-by-step guide. Don’t think it could have been done better.
Obrigado,
John
Hi There,
ReplyDeleteSuch vivid info on the PLSQL-MUTATING TABLES ! Flabbergasted! Thank you for making the read a smooth sail!
for non rac servers, the query works and
for rac servers
SELECT host_name,count(*)
FROM (SELECT b.host_name, c.VALUE
FROM gv$session a,
gv$instance b,
(SELECT TO_NUMBER (
ROUND (
TO_CHAR (
(CURRENT_UTILIZATION / LIMIT_VALUE) * 100),
0))
VALUE
FROM gv$resource_limit
WHERE resource_name LIKE '%proces%') c
WHERE a.inst_id = b.inst_id AND c.VALUE >= 80)
GROUP BY host_name
/
SELECT host_name,count(*)
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server PZ99, instance
zimgrid45.vzbi.com:dev1d (2)
ORA-01722: invalid number.
Follow my new blog if you interested in just tag along me in any social media platforms!
Many Thanks,
Preethi.