SQL触发器错误-无效触发器 [英] SQL trigger error - invalid trigger

问题描述

我正在使用pl \ sql开发人员，并且有一个带有数字(38)ID列的报告表.

I'm using pl\sql developer and I have a report table with a number(38) ID column.

我想跟踪该表的所有更新，因此我创建了另一个表，如下所示:

I want to keep track of all updates for this table so I created another table like this:

 CREATE TABLE reportUpdate (report_id number(38), updatedate number(32));

然后我创建了一个触发器:

And I created a trigger:

CREATE or REPLACE TRIGGER BeforeUpdateReport 
BEFORE 
UPDATE ON REPORT 
FOR EACH ROW 
Begin 
   INSERT INTO reportUpdate 
   Values(old.ID,sysdate); 
END;

当我运行它时，我得到一个错误，说:trigger 'SYSTEM.BEFOREUPDATEREPORT' is invalidand failed re-validation.

And when I run it, I get an error, saying: trigger 'SYSTEM.BEFOREUPDATEREPORT' is invalidand failed re-validation.

有人可以帮忙

推荐答案

您可以在看到compiled with warnings后使用show errors，或查询user_errors视图以查看稍后出了什么问题.

You can use show errors after you see compiled with warnings, or query the user_errors view to see what is wrong later.

一个明显的事情是，您没有在old引用前面加上冒号:

One obvious thing is that you haven't prefixed the old reference with a colon:

CREATE or REPLACE TRIGGER BeforeUpdateReport 
BEFORE 
UPDATE ON REPORT 
FOR EACH ROW 
Begin 
   INSERT INTO reportUpdate 
   Values(:old.ID,sysdate); 
END;
/

最好在insert语句中指定目标表字段:

It's also better to specify the target table fields in the insert statement:

   INSERT INTO reportUpdate (report_id, updatedate)
   Values(:old.ID,sysdate); 

但是您在表创建脚本中将update_date定义为number(32)，这没有任何意义.正如@realspirituals指出的那样，应该为:

But you have update_date defined in your table creation script as number(32), which doesn't make sense. As @realspirituals pointed out, it should be:

CREATE TABLE reportUpdate (report_id number, updatedate date);

这篇关于SQL触发器错误-无效触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！