PL/SQL触发器问题 [英] PL/SQL Trigger Issues

问题描述

我正在尝试编写一个触发器来填充一个包含有关雇员最新工资信息的表格.我有一个问题，我现在无法完全包住头.

I am trying to write a trigger to populate a table containing information on an employee's updated salary. I'm having a problem that I can't quite wrap my head around at the moment.

这是要填充的表:

 drop table SalUpdates cascade constraints;
 create table SalUpdates(
 SalSSN char(9), 
 newSalary decimal(10,2), 
 oldSalary decimal(10,2)

 );

这是我的触发器:

 create or replace trigger t1
 after update of salary on employee
 for each row
 begin
 insert into SalUpdates values (:old.Ssn, :new.salary, :old.salary);  
 end;

触发器可以毫无问题地进行编译，但是当我尝试运行此更新时，Oracle告诉我我的触发器无效.可能是什么原因造成的?

The trigger compiles with no issues, but when I try to run this update, Oracle tells me my trigger is invalid. What could be causing this?

update employee
set salary=4000
where ssn='123456789';

推荐答案

您已按块显示了代码.但似乎您正在运行一起显示为脚本的内容，最初没有更新:

You've shown the code in chunks. but it seems you're running what you've shown together as a script, initially without the update:

drop table SalUpdates cascade constraints;
create table SalUpdates(
SalSSN char(9), 
newSalary decimal(10,2), 
oldSalary decimal(10,2)
);

create or replace trigger t1
after update of salary on employee
for each row
begin
insert into SalUpdates values (:old.Ssn, :new.salary, :old.salary);  
end;

在SQL Developer中作为脚本运行时，脚本输出窗口显示:

When run as a script in SQL Developer the script output window shows:

drop table SalUpdates cascade constraints
Error report -
ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:

Table SALUPDATES created.


Trigger T1 compiled

如果您随后将更新语句添加到脚本:

If you then add the update statement to the script:

drop table SalUpdates cascade constraints;
create table SalUpdates(
SalSSN char(9), 
newSalary decimal(10,2), 
oldSalary decimal(10,2)
);

create or replace trigger t1
after update of salary on employee
for each row
begin
insert into SalUpdates values (:old.Ssn, :new.salary, :old.salary);  
end;

update employee
set salary=4000
where ssn='123456789';

您得到:

Table SALUPDATES dropped.


Table SALUPDATES created.


Trigger T1 compiled

Errors: check compiler log

如果您随后尝试独自运行更新(作为语句而不是脚本；或者通过选择该测试并作为脚本运行)，则确实会得到:

If you then try to run the update on it's own (as a statement instead of a script; or by selecting that test and running as a script) you do indeed get:

SQL Error: ORA-04098: trigger 'MYSCHEMA.T1' 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.

如果您查询 user_errors 视图或运行 show errors ，则会看到:

If you query the user_errors view, or run show errors, you'll see:

PLS-00103: Encountered the symbol "UPDATE"

问题是您没有正确完成 create trigger 语句. update 被视为同一PL/SQL块的一部分；无效的部分，但仍包含在内.

The problem is that you aren't completing the create trigger statement properly. The update is being seen as part of the same PL/SQL block; an invalid part, but still included.

当您拥有PL/SQL块时，必须使用斜杠将其终止，，如它在SQL * Plus文档中所解释的(大多数情况下也适用于SQL Developer):

When you have a PL/SQL block you have to terminate it with a slash, as it explains in the SQL*Plus documentation (which mostly applies to SQL Developer too):

SQL * Plus以与SQL命令相同的方式处理PL/SQL子程序，除了分号(;)或空白行不会终止并执行块之外.通过在新行上单独输入一个句点(.)来终止PL/SQL子程序.您还可以通过在新行上单独输入一个斜杠(/)来终止并执行PL/SQL子程序.

SQL*Plus treats PL/SQL subprograms in the same manner as SQL commands, except that a semicolon (;) or a blank line does not terminate and execute a block. Terminate PL/SQL subprograms by entering a period (.) by itself on a new line. You can also terminate and execute a PL/SQL subprogram by entering a slash (/) by itself on a new line.

但是，如果脚本中的最后一个块没有斜杠，SQL Developer不会抱怨，因此您的原始脚本(没有更新)可以工作；在SQL * Plus中，它会在出现提示时显示.从某种意义上讲，它应该在那里-试图有所帮助.添加 update 语句时，它不再是脚本的结尾，因此不再适用.

SQL Developer doesn't complain if the last block in a script doesn't have a terminating slash though, so your original script (without the update) works; in SQL*Plus it would sit at a prompt. It kind of infers that it should be there - trying to be helpful. When you add the update statement it is no longer the end of the script so that doesn't apply.

如果在脚本中的PL/SQL代码和以下SQL语句之间添加斜杠，则所有操作均有效:

If you add a slash to your script between the PL/SQL code and the following SQL statement it all works:

drop table SalUpdates cascade constraints;
create table SalUpdates(
SalSSN char(9), 
newSalary decimal(10,2), 
oldSalary decimal(10,2)
);

create or replace trigger t1
after update of salary on employee
for each row
begin
insert into SalUpdates values (:old.Ssn, :new.salary, :old.salary);  
end;
/

update employee
set salary=4000
where ssn='123456789';

现在您将看到:

Table SALUPDATES dropped.


Table SALUPDATES created.


Trigger T1 compiled


1 row updated.

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