修改ID应该是记录一条删除操作和一条插入操作吧.create or replace trigger tr_iud_test after insert or update or delete on test for each row declare begin if inserting then delete from log_test where test_id = :new.test_id; insert into log_test values(:new.test_id,sysdate,'inst'); elsif updating then delete from log_test where test_id = :new.test_id; insert into log_test values(:new.test_id,sysdate,'inst'); delete from log_test where test_id = :old.test_id; insert into log_test values(:new.test_id,sysdate,'del'); else delete from log_test where test_id = :old.test_id; insert into log_test values(:new.test_id,sysdate,'del'); end if; end; /
to jxc(GameHeart):我觉得你的不完全符合楼主的意思。要判断log_test是否含有:new.test_id再作操作的,deleting没有:new.test_id,要用:old.test_id,可是updating用old还是new呢?在jxc(GameHeart)的加上些判断,整理一下就行了,楼住自己整理吧
create or replace trigger tr_iud_test after insert or update or delete on test for each row declare begin if inserting then insert into log_test values(:new.test_id,sysdate,'inst'); elsif updating then update log_test set in_date = sysdate, chg_type = 'Upd' where test_id = :new.test_id; if sql%notfound then insert into log_test values(:new.test_id,sysdate,'Inst'); end if else update log_test set in_date = sysdate, chg_type = 'Del' where test_id = :old.test_id; end if; end;
to 楼上各位: 我是楼主,我的需求:当对test有了插入/更新/删除等操作后,如果在log_test中已有了,不是要把新的chg_type简单地修改,而是要结合之前的操作再改写chg_type。我原意是想提取对test中一个test_id的多个操作,并在log_test中纪录成一条。 例如: 1)我在test中对test_id=0001的记录作了插入,更新,更新共计三个操作,那我最后在log_test中纪录的是('0001',sysdate,'inse') 2)我在test中对test_id=0002的记录作了更新,更新,删除共计三个操作,那我最后在log_test中纪录的是('0002',sysdate,'dele')我想当对test有操作后,首先要判断test_id在log_test中是不是有,如没有就直接插入,如有的话就要取对应test_id的chg_type,然后根据chg_type和本次操作来判断最终要修改的chg_type
after insert or update or delete on test
for each row
declare
begin
if inserting then
delete from log_test where test_id = :new.test_id;
insert into log_test values(:new.test_id,sysdate,'inst');
elsif updating then
delete from log_test where test_id = :new.test_id;
insert into log_test values(:new.test_id,sysdate,'inst'); delete from log_test where test_id = :old.test_id;
insert into log_test values(:new.test_id,sysdate,'del');
else
delete from log_test where test_id = :old.test_id;
insert into log_test values(:new.test_id,sysdate,'del');
end if;
end;
/
after insert or update or delete on test
for each row
declare
begin
if inserting then
insert into log_test values(:new.test_id,sysdate,'inst');
elsif updating then
update log_test
set in_date = sysdate,
chg_type = 'Upd'
where test_id = :new.test_id;
if sql%notfound then
insert into log_test values(:new.test_id,sysdate,'Inst');
end if
else
update log_test
set in_date = sysdate,
chg_type = 'Del'
where test_id = :old.test_id;
end if;
end;
我是楼主,我的需求:当对test有了插入/更新/删除等操作后,如果在log_test中已有了,不是要把新的chg_type简单地修改,而是要结合之前的操作再改写chg_type。我原意是想提取对test中一个test_id的多个操作,并在log_test中纪录成一条。
例如:
1)我在test中对test_id=0001的记录作了插入,更新,更新共计三个操作,那我最后在log_test中纪录的是('0001',sysdate,'inse')
2)我在test中对test_id=0002的记录作了更新,更新,删除共计三个操作,那我最后在log_test中纪录的是('0002',sysdate,'dele')我想当对test有操作后,首先要判断test_id在log_test中是不是有,如没有就直接插入,如有的话就要取对应test_id的chg_type,然后根据chg_type和本次操作来判断最终要修改的chg_type