test11为源表,test12为更新备份表。
结构如下:
create table TEST12
(
ID INTEGER,
L2 INTEGER,
L3 INTEGER,
TIME TIMESTAMP(6)
)我要写一个触发器,从11表更新到12表,更新的可能字段是 l2 l3 .我写的脚本如下:create or replace trigger tr_1112
after update on test11
for each row
begin
if update(l2) then
update test12 set l2=:new.l2 where id=:new.id;
end if;
if update(l3) then
update test13 set l3=:new.l3 where id=:new.id;
end if;
commit;
end;报错如下:Compilation errors for TRIGGER SYSTEM.TR_1112Error: PLS-00103: Encountered the symbol "UPDATE" when expecting one of the following:
( - + case mod new not null <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute forall merge time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string> pipe
<an alternatively-quoted string literal with character set specification>
<an alternatively-quo
Line: 5
Text: if update(l2) thenError: PLS-00103: Encountered the symbol "UPDATE" when expecting one of the following:
( - + case mod new not null <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute forall merge time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string> pipe
<an alternatively-quoted string literal with character set specification>
<an alternatively-quo
Line: 9
Text: if update(l3) then
结构如下:
create table TEST12
(
ID INTEGER,
L2 INTEGER,
L3 INTEGER,
TIME TIMESTAMP(6)
)我要写一个触发器,从11表更新到12表,更新的可能字段是 l2 l3 .我写的脚本如下:create or replace trigger tr_1112
after update on test11
for each row
begin
if update(l2) then
update test12 set l2=:new.l2 where id=:new.id;
end if;
if update(l3) then
update test13 set l3=:new.l3 where id=:new.id;
end if;
commit;
end;报错如下:Compilation errors for TRIGGER SYSTEM.TR_1112Error: PLS-00103: Encountered the symbol "UPDATE" when expecting one of the following:
( - + case mod new not null <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute forall merge time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string> pipe
<an alternatively-quoted string literal with character set specification>
<an alternatively-quo
Line: 5
Text: if update(l2) thenError: PLS-00103: Encountered the symbol "UPDATE" when expecting one of the following:
( - + case mod new not null <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute forall merge time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string> pipe
<an alternatively-quoted string literal with character set specification>
<an alternatively-quo
Line: 9
Text: if update(l3) then
create or replace trigger tr_1112
after update on test11
for each row
begin
if :NEW.l2 != :OLD.l2 then --用new和old来比较是否有更新相应字段,再做判断 处理
update test12 set l2=:new.l2 where id=:old.id;
end if; if :NEW.l3 != :OLD.l3 then
update test13 set l3=:new.l3 where id=:old.id;
end if; -- commit; 触发器中不要commit
end;