触发器在更新后自动设置日期

Question

一些背景信息:我有一个名为缺陷的表，其中列名为status_id和另一列名为date_closed，我想在status_id已更新后设置date_closed我已经尝试用以下代码使用after update触发器来做到这一点:

after update on eba_bt_sw_defects
for each row
declare
l_status number(20) := null;
begin
select status_id into l_status  from eba_bt_sw_defects D,eba_bt_status S where D.status_id = S.id;
if  l_status in ( select id from eba_bt_status where is_open = 'N' and NVL(is_enhancement,'N')='N') then
:NEW.DATE_CLOSED  := LOCALTIMESTAMP ; 
end if;
end;

但发生错误(此上下文不允许子查询编译失败)我需要帮助

Answer 1

代码中需要修复的几个问题:

• 在触发器中，不要从表中选择您所处的触发器。这可能会引发ORA-04091: table name is mutating, trigger/function may not see it错误。
• IF l_variable IN (SELECT ...)不是一个有效的oracle语法。升高PLS-00405: subquery not allowed in this context

我没有你的数据，所以这里有一个类似的例子:

drop table todos;
drop table statuses;
-- create tables
create table statuses (
id                             number generated by default on null as identity 
constraint statuses_id_pk primary key,
status                         varchar2(60 char),
is_open                        varchar2(1 char) constraint statuses_is_open_ck
check (is_open in ('Y','N'))
)
;
create table todos (
id                             number generated by default on null as identity 
constraint todos_id_pk primary key,
name                           varchar2(255 char) not null,
close_date                     timestamp with local time zone,
status_id                      number
constraint todos_status_id_fk
references statuses on delete cascade
)
;

-- load data

insert into statuses (id, status, is_open ) values (1, 'OPEN', 'Y' );
insert into statuses (id, status, is_open ) values (2, 'COMPLETE', 'N' );
insert into statuses (id, status, is_open ) values (3, 'ON HOLD', 'Y' );
insert into statuses (id, status, is_open ) values (4, 'CANCELLED', 'N' );
commit;
insert into todos (name, close_date, status_id ) values ( 'Y2 Security Review', NULL, 1 );
-- triggers
CREATE OR REPLACE TRIGGER todos_biu BEFORE
INSERT OR UPDATE ON todos
FOR EACH ROW
DECLARE
l_dummy NUMBER;
BEGIN
SELECT
1
INTO l_dummy
FROM
statuses
WHERE
is_open = 'N' AND
id = :new.status_id;
:new.close_date := localtimestamp;
EXCEPTION
WHEN no_data_found THEN
-- I'm assuming you want close_date to NULL if todo is re-opened.
:new.close_date := NULL; 
END todos_biu;
/
update todos set status_id = 2;
select  * from todos;
id  name                close_date                      status_id
1   Y2 Security Review  11-MAY-22 05.27.04.987117000 PM 2