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 table D_PC_PARTDISCOUNT--被监察表
(
AUTHNUMBER VARCHAR2(50) not null,
PARTNUMBER VARCHAR2(20) not null,
DISCOUNT NUMBER(10,7),
CREATETIME NUMBER(38) not null,
ONCEPUBLISHED NUMBER(10) not null,
LASTUPDATEDATE NUMBER(38)
)--触发器
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',rawtohex(:old.AUTHNUMBER),null,'删除','系统管理员',1331793947336);
dbms_output.put_line('delinfo:'||:old.AUTHNUMBER);
elsif inserting then
log_yangya('ADD',null,rawtohex(:new.AUTHNUMBER),'添加','系统管理员',1331793947336);
dbms_output.put_line('addinfo:'||:new.AUTHNUMBER);
elsif updating then
log_yangya('UPDATE',rawtohex(:old.AUTHNUMBER),rawtohex(:new.AUTHNUMBER),'更新','系统管理员',1331793947336);
dbms_output.put_line('delinfo:'||:old.AUTHNUMBER);
dbms_output.put_line('addinfo:'||:new.AUTHNUMBER);
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
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 log_yangya;业务要求:对被监察表的添删改操作信息记录到日志表中,日志表d_pc_yangya的addinfo和delinfo存放受影响的数据,目前测试阶段所有先只取被监察表的AUTHNUMBER字段(varchar型),转换为blob型时报错,触发器有错
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货