create or replace trigger put_log
after insert or update or delete on t_user
for each row
declare
--val_tablename varchar2(20);
--val_processing varchar2(20);
val_processingtime date;
val_deletedata varchar2(20);
val_insertdata varchar2(20);
val_olddata varchar2(20);
val_newdata varchar2(20);
--val_tablename:='t_user';
-- local variables here
begin
--修改触发
if(updating) then
dbms_output.put_line('updating!');
val_processingtime:=sysdate;
val_olddata:=:old.uname;
val_newdata:=:new.uname;
begin
if(:old.uname<>:new.uname) then
insert into t_log(tablename,processing,olddata,newdata,processingtime)
values('t_user','updating',val_olddata,val_newdata,val_processingtime);
end if;
end;
dbms_output.put_line(
'编号: '||:old.uno||
'姓名: '||:old.uname||
'old字段: '||val_olddata||
'new字段 '||val_newdata||
'time is: '||val_processingtime);
--删除触发
else if(deleting) then
dbms_output.put_line('delete');
val_processingtime:=sysdate;
val_deletedata:=:old.uname;--delete old data
begin
insert into t_log(tablename,processing,processingtime,deletedata)
values('t_user','deleting',val_processingtime,val_deletedata);
end;
dbms_output.put_line(
'编号: '||:old.uno||
'姓名: '||:old.uname||
'time is: '||val_processingtime||
'删除字段: '||val_deletedata);
--
--插入触发
else if(inserting) then
dbms_output.put_line('insert');
val_processingtime:=sysdate;
val_insertdata:=:new.uname;---
begin
insert into t_log(tablename,processing,processingtime,insertdata)
values('t_user','insert',val_processingtime,val_insertdata);
end;
dbms_output.put_line(
'插入编号: '||:new.uno||
'插入姓名: '||:new.uname||
' time is: '||val_processingtime);
end if;
end if;
end if ;
end put_log;