0

I have a table (Meeting) with date type attribute (MeetDate) and another varchar2 type attribute (WorkWeek). I'm trying to do an After trigger to fill in the WorkWeek field based on the MeetDate value using the to_char function. Tried the following codes separately and they compile without errors but when I try to insert a row with Null for WorkWeek, it gives me a 'mutating trigger/function may not see it' error. What am I doing wrong here? thanks in advance to any help.

--Code 1
Create or Replace Trigger Update_WorkWeek
After Insert On Meeting
For Each Row
Begin
Update Meeting
Set WorkWeek  = (Select to_char(:new.MeetDate, 'YYYY IW') From Dual)
Where MeetID = :new.MeetID;
End;
/
show Errors;

--Code 2
Create or Replace Trigger Update_WorkWeek
After Insert On Meeting
For Each Row
Begin
if :New.WorkWeek is Null then
Update Meeting
Set WorkWeek  = (Select to_char(:new.MeetDate, 'YYYY IW') From Dual)
Where MeetID = :new.MeetID;
End if;
End;
/
show Errors;
Nimesh Wicks
  • 19
  • 1
  • 7
  • I am not seeing why you need the second trigger, which is likely the cause of the mutating tables error. THe second trigger just checks for null WorkWeek and goes and does exactly the same thing as the first trigger does. If I am missing something, can you explain why you need the second trigger. – ron tornambe May 01 '13 at 00:47
  • I was using them separately. I tried both separately but neither works. gives the same error. – Nimesh Wicks May 01 '13 at 00:53
  • I also tried using the same trigger using 'Before' instead of 'After'. When I did that and inserted a row with Null for WorkWeek, it just didn't the populate the field for that record. But didn't get the mutating error though. – Nimesh Wicks May 01 '13 at 00:56
  • Is there a way to declare this in DDL when creating the table as a constraint for the attribute? – Nimesh Wicks May 01 '13 at 01:07
  • 2
    I don't think you need the UPDATE statement if you are only assigning a new value to WorkWeek. Try just setting the ":new.WorkWeek = (Select to_char(:new.MeetDate, 'YYYY IW') From Dual) Where MeetID = :new.MeetID;" - remove Update Meeting – ron tornambe May 01 '13 at 01:09
  • No. You cannot declare a default value the references a column. http://docs.oracle.com/cd/E11882_01/server.112/e17118/statements_7002.htm – ron tornambe May 01 '13 at 01:14
  • Got the following error when I did that, 2/15 PLS-00103: Encountered the symbol "=" when expecting one of the f ollowing: := . ( @ % ; indicator 3/1 PLS-00103: Encountered the symbol "WHERE" when expecting one of t he following: ; return returning and or 4/1 PLS-00103: Encountered the symbol "END" – Nimesh Wicks May 01 '13 at 01:25
  • I think the correct syntax is "Select to_char(:new.MeetDate, 'YYYY IW') into :new.WorkWeek;" This would be the only statement in a BEFORE INSERT trigger. – ron tornambe May 01 '13 at 02:00

1 Answers1

1

You just want a trigger to change the value of a column before it gets inserted - and it's on the same row, so you don't need an UPDATE:

Create or Replace Trigger Update_WorkWeek
BEFORE Insert On Meeting
For Each Row
Begin
  :new.WorkWeek := to_char(:new.MeetDate, 'YYYY IW');
End;
/
show Errors;

You might want the column kept up-to-date if the MeetDate is changed, i.e.:

Create or Replace Trigger Update_WorkWeek
BEFORE Insert
    OR Update OF MeetDate
On Meeting
For Each Row
Begin
  :new.WorkWeek := to_char(:new.MeetDate, 'YYYY IW');
End;
/
show Errors;
Jeffrey Kemp
  • 59,135
  • 14
  • 106
  • 158
  • Thanks Jeffrey! That works great. So logically speaking, does Oracle insert the WorkWeek value first before populating the other attributes for that row? – Nimesh Wicks May 01 '13 at 06:37
  • No, the BEFORE trigger allows you to modify the row ("in memory", so to speak) before it inserts the row. The row is then inserted, after which the AFTER trigger fires (and then it's too late to change it). – Jeffrey Kemp May 01 '13 at 10:30