|
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
|
|
Stop making mediocre tutorials.The best tutorials are video! Camtasia Studio makes it easy to create engaging, buzz-building screen videos at any size, in any popular format. Download the free trial!
|
|
#1
|
|||
|
|||
|
Hi There,
I have created a trigger on a table which should update value in another table. Trigger is created without any error but while executing I get mutate error! Here is code - CREATE OR REPLACE TRIGGER holiday_trig AFTER UPDATE OR DELETE OF status ON holidays FOR EACH ROW DECLARE totHolidays number; BEGIN IF UPDATING THEN SELECT SUM(workingdays) INTO totHolidays FROM holidays WHERE id = ld.id;UPDATE ssgemployee SET holidaystaken = totHolidays WHERE id = ld.id;ELSIF DELETING THEN UPDATE ssgemployee SET holidaystaken = holidaystaken - ld.workingdaysWHERE id = ld.id;END IF; END; / Could some one please tell me where I am going wrong? Error code is - ERROR at line 1: ORA-04091: table xxx.HOLIDAYS is mutating, trigger/function may not see it ORA-06512: at "xxx.HOLIDAY_TRIG", line 5 ORA-04088: error during execution of trigger 'xxx.HOLIDAY_TRIG' |
|
#2
|
|||
|
|||
|
the following is from oracle:
A mutating table is a table that is currently being modified by an update, delete, or insert statement. You will encounter the ORA-04091 error if you have a row trigger that reads or modifies the mutating table. For example, if your trigger contains a select statement or an update statement referencing the table it is triggering off of you will receive the error. Another way this error can occur is if the trigger has statements to change the primary, foreign or unique key columns of the table the trigger is triggering from. i didn't try it but you can try and use autonomous transaction to do the updates (i'm no so sure it will work though). |
![]() |
| Viewing: Dev Shed Forums > Databases > Oracle Development > Trigger Error |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|
|