create or replace trigger test_trig
after insert or update or delete on test
for each row
BEGIN
case
when inserting then
insert into test_log values(:NEW.id,:NEW.name,:NEW.sex,:NEW.birthDate,'I');
when updating then
insert into test_log values(:OLD.id,:NEW.name,:NEW.sex,:NEW.birthDate,'U');
when deleting then
insert into test_log values(:OLD.id,:OLD.name,:OLD.sex,:OLD.birthDate,'D');
end case;
End test_trig;上面这段触发器代码是在向Test表做插入,更新,删除操作时,对应的日志表test_log(与Test表结构相同)中插入操作记录,
如果表的字段有几十上百个,那触发器代码:
insert into test_log values(:NEW.id,:NEW.name,:NEW.sex,:NEW.birthDate,'I');
岂不是要写N长,有没有第二种好的方法?
after insert or update or delete on test
for each row
BEGIN
case
when inserting then
insert into test_log values(:NEW.id,:NEW.name,:NEW.sex,:NEW.birthDate,'I');
when updating then
insert into test_log values(:OLD.id,:NEW.name,:NEW.sex,:NEW.birthDate,'U');
when deleting then
insert into test_log values(:OLD.id,:OLD.name,:OLD.sex,:OLD.birthDate,'D');
end case;
End test_trig;上面这段触发器代码是在向Test表做插入,更新,删除操作时,对应的日志表test_log(与Test表结构相同)中插入操作记录,
如果表的字段有几十上百个,那触发器代码:
insert into test_log values(:NEW.id,:NEW.name,:NEW.sex,:NEW.birthDate,'I');
岂不是要写N长,有没有第二种好的方法?
insert into test_log values :new returning rowid into v_rowid;
update test_log set flag='I' where rowid = v_rowid;
insert into log
select * from test where...
after delete or insert or update
on test
for each row
Declare
v_rowid test.id%type;
BEGIN
case when inserting then
insert into test_log values :new returning rowid into v_rowid;
update test_log set action='I' where rowid = v_rowid;
when updating then
insert into test_log values :old returning rowid into v_rowid;
update test_log set action='U' where rowid = v_rowid;
when deleting then
insert into test_log values :old returning rowid into v_rowid;
update test_log set action='D' where rowid = v_rowid;
end case;
end TEST_TRIGGER;改成上面的,运行报:
警告: 创建的触发器带有编译错误。7/45 PLS-00049: bad bind variable 'NEW'