SQL 触发器无效，重新验证失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3440499/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Trigger is invalid and failed re-validation
提问by Raj More
Here is the code that I use to create a table, a sequence and a trigger
这是我用来创建表、序列和触发器的代码
DROP TABLE CDR.ExtDL_JobStatus;
--
-- TABLE: CDR.ExtDL_JobStatus
--
CREATE TABLE CDR.ExtDL_JobStatus(
Id NUMBER(38, 0) NOT NULL,
ShortName NUMBER(38, 0) NOT NULL,
Description NUMBER(38, 0) NOT NULL,
CONSTRAINT PK_ExtDL_JobStatus PRIMARY KEY (Id)
)
;
Declare NumOfSequences NUMBER :=0;
Begin
Select COUNT(*)
INTO NumOfSequences
FROM All_Sequences
WHERE 1=1
And upper (Sequence_Owner) = upper ('CDR')
And upper (Sequence_Name) = upper ('ExtDL_JobStatus_Seq');
If NumOfSequences > 0 Then
Execute IMMEDIATE 'DROP SEQUENCE CDR.ExtDL_JobStatus_Seq';
End If;
End;
/
CREATE SEQUENCE CDR.ExtDL_JobStatus_Seq
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOMINVALUE
;
/
Declare NumOfTriggers NUMBER :=0;
Begin
SELECT COUNT(*)
INTO NumOfTriggers
FROM All_Triggers
WHERE 1=1
And upper (Owner) = upper ('CDR')
And upper (Trigger_Name) = upper ('ExtDL_JobStatus_SeqTrg');
If NumOfTriggers > 0 Then
Execute IMMEDIATE 'DROP SEQUENCE CDR.ExtDL_JobStatus_SeqTrg';
End If;
End;
/
CREATE TRIGGER CDR.ExtDL_JobStatus_SeqTrg
BEFORE INSERT
ON CDR.ExtDL_JobStatus
FOR EACH ROW
WHEN (new.Id IS NULL)
BEGIN
SELECT ExtDL_JobStatus_SeqTrg.nextval into :new.Id from dual;
END;
/
INSERT INTO ExtDL_JobStatus (Id, ShortName, Description) Values (0, 'Success', 'Fail')
/
SELECT * FROM ExtDL_JobStatus
When I execute the code, I get the following output
当我执行代码时，我得到以下输出
DROP TABLE CDR.ExtDL_JobStatus succeeded.
CREATE TABLE succeeded.
anonymous block completed
CREATE SEQUENCE succeeded.
anonymous block completed
Warning: execution completed with warning
TRIGGER CDR.ExtDL_JobStatus_SeqTrg Compiled.
Error starting at line 62 in command:
INSERT INTO ExtDL_JobStatus (Id, ShortName, Description) Values (0, 'Success', 'Fail')
Error at Command Line:62 Column:12
Error report:
SQL Error: ORA-04098: trigger 'CDR.EXTDL_JOBSTATUS_SEQTRG' is invalid and failed re-validation
04098. 00000 - "trigger '%s.%s' is invalid and failed re-validation"
*Cause: A trigger was attempted to be retrieved for execution and was
found to be invalid. This also means that compilation/authorization
failed for the trigger.
*Action: Options are to resolve the compilation/authorization errors,
disable the trigger, or drop the trigger.
ID SHORTNAME DESCRIPTION
---------------------- ---------------------- ----------------------
0 rows selected
What makes my trigger invalid?
是什么让我的触发器无效？
回答by Rajesh Chamarthi
Warning: execution completed with warning TRIGGER CDR.ExtDL_JobStatus_SeqTrg Compiled.
警告：执行完成，警告 TRIGGER CDR.ExtDL_JobStatus_SeqTrg 已编译。
This is where your trigger compilation failed.
这是您的触发器编译失败的地方。
sql> CREATE TRIGGER ExtDL_JobStatus_SeqTrg
2 BEFORE INSERT
3 ON ExtDL_JobStatus
4 FOR EACH ROW
5 WHEN (new.Id IS NULL)
6 BEGIN
7 SELECT ExtDL_JobStatus_SeqTrg.nextval into :new.Id from dual;
8 END;
9 /
Warning: Trigger created with compilation errors.
sql> show errors;
Errors for TRIGGER EXTDL_JOBSTATUS_SEQTRG:
LINE/COL ERROR
-------- -----------------------------------------------------------------
2/9 PL/SQL: SQL Statement ignored
2/16 PL/SQL: ORA-02289: sequence does not exist
The problem is because you are using ExtDL_JobStatus_SeqTrgin your code and the sequence you created is ExtDL_JobStatus_Seq.
问题是因为您在代码中使用ExtDL_JobStatus_SeqTrg并且您创建的序列是ExtDL_JobStatus_Seq。
Also, if you are trying to run a script like this for creating (compiling) the objects, I would suggest you add the following clause after each trigger/procedure/function creatin statement.
此外，如果您尝试运行这样的脚本来创建（编译）对象，我建议您在每个触发器/过程/函数 creatin 语句之后添加以下子句。
SHOW ERRORS;
If your statement succceds, that will just produce no errors. If there are any erros, you'll have a detailed description of the errors instead of having to execute the script again.
如果您的语句成功，则不会产生任何错误。如果有任何错误，您将获得错误的详细描述，而不必再次执行脚本。
回答by APC
It's a simple typo: your sequence is called ExtDL_JobStatus_Seqbut in your trigger you reference ExtDL_JobStatus_SeqTrg.nextval.
这是一个简单的错字：您的序列被调用，ExtDL_JobStatus_Seq但在您的触发器中您引用了 ExtDL_JobStatus_SeqTrg.nextval.
For future reference it is a good idea to include a call to show error in scripts after each call which compiles PL/SQL (triggers, procedures, etc). Like this:
为了将来参考，在每次编译 PL/SQL（触发器、过程等）的调用之后，在脚本中包含一个显示错误的调用是一个好主意。像这样：
CREATE TRIGGER CDR.ExtDL_JobStatus_SeqTrg
BEFORE INSERT
ON CDR.ExtDL_JobStatus
FOR EACH ROW
WHEN (new.Id IS NULL)
BEGIN
SELECT ExtDL_JobStatus_SeqTrg.nextval into :new.Id from dual;
END;
/
show errors
Incidentally, there's the same typo in the anonymous block which attempst to drop the sequence.
顺便说一句，在试图删除序列的匿名块中有相同的拼写错误。
回答by AndyDan
In addition to everything previously mentioned, there are two additional typos/errors:
除了前面提到的所有内容之外，还有两个额外的拼写错误/错误：
- The anonymous PL/SQL block that tries to drop the trigger actually says
DROP SEQUENCE. - The insert statement attempts to insert characters strings into the ShortName and Description columns, which are both defined as NUMBER(38, 0).
- 试图删除触发器的匿名 PL/SQL 块实际上是
DROP SEQUENCE. - insert 语句尝试将字符串插入到定义为 NUMBER(38, 0) 的 ShortName 和 Description 列中。
