您已按块显示了代码。但似乎您正在运行作为脚本一起显示的内容,最初没有更新:
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 中作为脚本运行时,脚本输出窗口显示:
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
如果随后将更新语句添加到脚本中:
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';
you get:
Table SALUPDATES dropped.
Table SALUPDATES created.
Trigger T1 compiled
Errors: check compiler log
如果您随后尝试自行运行更新(作为语句而不是脚本;或者通过选择该测试并作为脚本运行),您确实会得到:
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
, 你会看到的:
PLS-00103: Encountered the symbol "UPDATE"
问题是你没有完成create trigger
正确陈述。这update
被视为同一个 PL/SQL 块的一部分;无效部分,但仍然包含在内。
当你有一个 PL/SQL 块时,你必须用斜杠终止它,正如 SQL*Plus 文档中所解释的 https://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_four.htm#i1039663(这也主要适用于 SQL Developer):
SQL*Plus 以与 SQL 命令相同的方式处理 PL/SQL 子程序,只不过分号 (;) 或空行不会终止并执行块。通过在新行中单独输入句点 (.) 来终止 PL/SQL 子程序。您还可以通过在新行中单独输入斜杠 (/) 来终止并执行 PL/SQL 子程序。
如果脚本中的最后一个块没有终止斜杠,SQL Developer 不会抱怨,因此您的原始脚本(没有更新)可以工作;在 SQL*Plus 中,它将出现提示 https://stackoverflow.com/q/20971731/266304。它有点暗示它应该在那里——试图提供帮助。当您添加update
声明它不再是脚本的结尾,因此不适用。
如果您在 PL/SQL 代码和以下 SQL 语句之间的脚本中添加斜杠,则一切正常:
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.