触发器如下:create or replace TRIGGER tr_xxx_ud BEFORE UPDATE OR DELETE ON "XXXX" REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROWDECLARE v_LogId number(15); v_today number; v_baseid number(15); BEGIN v_today := func_today(); v_baseid := v_today * 1000000; select nvl(max(LogId), v_baseid) into v_LogId from his_tb1 where LogId between v_baseid and (v_baseid + 999999); if updating then --备份历史 insert into his_tb1( logid, loglabel, logtime, loghandler, keyid, keyname, aliases, sortfield, enabled) values(v_LogId + 1, null, sysdate, null, :new.keyid, :new.keyname, :new.aliases, :new.sortfield, :new.enabled); end if; if deleting then --备份历史 insert into his_tb1( logid, loglabel, logtime, loghandler, keyid, keyname, aliases, sortfield, enabled) values(v_LogId + 1, null, sysdate, null, :old.keyid, :old.keyname, :old.aliases, :old.sortfield, :old.enabled); end if; END;
你到底是delete还是update啊?
先说new和old
insert是插入数据,所以只会有new,
delete是删除数据,所以只会有old,
update是更新数据,所以new和old都有第二个问题,楼主为什么要用update来测试delete呢?
楼主大概是认为update操作是一个先删除,然后再插入的操作吧.
TRIGGER tr_xxx_ud
BEFORE UPDATE OR DELETE
ON "XXXX"
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROWDECLARE
v_LogId number(15);
v_today number;
v_baseid number(15);
BEGIN v_today := func_today();
v_baseid := v_today * 1000000;
select nvl(max(LogId), v_baseid) into v_LogId
from his_tb1
where LogId between v_baseid and (v_baseid + 999999); if updating then
--备份历史
insert into his_tb1(
logid,
loglabel,
logtime,
loghandler,
keyid,
keyname,
aliases,
sortfield,
enabled)
values(v_LogId + 1,
null,
sysdate,
null,
:new.keyid,
:new.keyname,
:new.aliases,
:new.sortfield,
:new.enabled);
end if; if deleting then
--备份历史
insert into his_tb1(
logid,
loglabel,
logtime,
loghandler,
keyid,
keyname,
aliases,
sortfield,
enabled)
values(v_LogId + 1,
null,
sysdate,
null,
:old.keyid,
:old.keyname,
:old.aliases,
:old.sortfield,
:old.enabled); end if;
END;
delete 触发器没有:new.