create table d_pc_yangya--日志表
(
ID number(8) primary key, --(如果在oracle取Number 8位)
DelInfo blob, --(如果在oracle取二进制Blob,记录删除的信息)
AddInfo blob, --(如果在oracle取二进制Blob,记录添加的信息,更新操作时先记录删除再记录添加信息)
OperateType varchar(10), --三个值:ADD,DELETE,UPDATE
HostName varchar(100), --机器MAC地址(用户最后一次成功登录时,设备的MAC,保存在User表中)
UserID varchar(20), --登陆账号(取W3格式用户,长度与SCT保持一致)
OperateTime number(38) --时间,精确到毫秒
);--触发器
create or replace trigger save_yangya
after insert or update or delete on D_PC_ProductDiscount for each row
declare
del blob;
add blob;
begin
if deleting then
log_yangya('DELETE',del=:OLD.DelInfo,null,'删除','系统管理员',1331793947336);
elsif inserting then
log_yangya('ADD',null,add=:NEW.AddInfo,'添加','系统管理员',1331793947336);
elsif updating then
log_yangya('UPDATE',del=:OLD.DelInfo,add=:NEW.AddInfo,'更新','系统管理员',1331793947336);
end if;
end;--存储过程
create or replace procedure log_yangya
(y_type in varchar2,delinfo in blob,addinfo in blob,y_host in varchar2,y_uid in varchar2,y_time in number)
is
begin
if y_type='UPDATE' then
insert into d_pc_yangya(ID,DelInfo,AddInfo,OperateType,HostName,UserID,OperateTime) values(yangya_seq.nextval,delinfo,addinfo,y_type,y_host,y_uid,y_time);
dbms_output.put_line('操作:'||y_type);
else
insert into d_pc_yangya(ID,DelInfo,AddInfo,OperateType,HostName,UserID,OperateTime) values(yangya_seq.nextval,delinfo,addinfo,y_type,y_host,y_uid,y_time);
dbms_output.put_line('操作:'||y_type);
end if;
end log_yangya;
你要把D_PC_ProductDiscount修改的数据传入到日志表,就是把所有字段的内容拼成一个字符串:
log_yangya('DELETE','column1:'||:OLD.column1||',column2:'||:OLD.column2,null,'删除','系统管理员',1331793947336);
create or replace trigger save_yangya
after insert or update or delete on D_PC_ProductDiscount for each row
begin
if deleting then
log_yangya('DELETE','AUTHUMBER:'||:OLD.AUTHUMBER||',MODULECODE:'||:OLD.MODULECODE,null,'删除','系统管理员',1331793947336);
elsif inserting then
log_yangya('ADD',null,'DELETE','AUTHUMBER:'||:NEW.AUTHUMBER||',MODULECODE:'||:NEW.MODULECODE,'添加','系统管理员',1331793947336);
elsif updating then
log_yangya('UPDATE','DELETE','AUTHUMBER:'||:OLD.AUTHUMBER||',MODULECODE:'||:OLD.MODULECODE,'DELETE','AUTHUMBER:'||:NEW.AUTHUMBER||',MODULECODE:'||:NEW.MODULECODE,'更新','系统管理员',1331793947336);
end if;
end;
after insert or update or delete on D_PC_ProductDiscount for each row
declare
del blob;
add blob;
BEGIN
SELECT :OLD.DelInfo INTO del FROM dual;
SELECT :NEW.AddInfo INTO add FROM dual;
if deleting then
log_yangya('DELETE',del,null,'删除','系统管理员',1331793947336);
elsif inserting then
log_yangya('ADD',null,ADD,'添加','系统管理员',1331793947336);
elsif updating then
log_yangya('UPDATE',del,ADD,'更新','系统管理员',1331793947336);
end if;
end;这样试了一下,可以。。