触发器中的Oracle to_char子查询 [英] Oracle to_char subquery in Trigger

问题描述

我有一个具有日期类型属性(MeetDate)和另一个varchar2类型属性(WorkWeek)的表(Meeting).我正在尝试使用to_char函数基于MeetDate值执行After触发器以填充WorkWeek字段.分别尝试了以下代码，并且它们没有错误地进行编译，但是当我尝试使用Null为WorkWeek插入一行时，它给我一个更改触发器/函数可能看不到它"错误.我在这里做错了什么?预先感谢您的任何帮助.

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;

推荐答案

您只希望触发器在插入列之前更改其值-该列位于同一行，因此您不需要更新:

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;

如果更改了MeetDate，您可能希望该列保持最新状态，即:

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;

这篇关于触发器中的Oracle to_char子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！