oracle 触发器，blob clob字段不成功解决方案

创建临时表，保存数据

create or replace trigger TRI_pass_area
after update or delete or insert on pass_area
for each row
declare
integrity_error exception;
errno integer;
errmsg char(200);
begin
if updating then
update pass_area@ks_forbid_link
set area_name = :NEW.area_name,
class = :NEW.class,
type = :NEW.type,
facility_list = :NEW.facility_list,
points = :NEW.points,
description = :NEW.description,
auto = :NEW.auto,
enterprise_id = :NEW.enterprise_id,
status = :NEW.status
where area_id = :OLD.area_id;
elsif deleting then
delete from pass_area@zjg_forbid_link where area_id = :OLD.area_id;
elsif inserting then
insert into pass_area_temp
(area_id,
area_name,
class,
type,
facility_list,
points,
description,
auto,
enterprise_id,
status)
values
(:NEW.area_id,
:NEW.area_name,
:NEW.class,
:NEW.type,
:NEW.facility_list,
:NEW.points,
:NEW.description,
:NEW.auto,
:NEW.enterprise_id,
:NEW.status);
insert into pass_area@ks_forbid_link select * from pass_area_temp where area_id = :NEW.area_id;
end if;
exception
when integrity_error then
raise_application_error(errno, errmsg);
end;

 

测试：

insert into pass_area(area_id,area_name,class,type,points) values(12123222,'aaa',1,1,c2b(to_clob('test')));

说明：c2b是一个函数，clob转成blob的函数，博客里面有https://blog.csdn.net/qjm201000/article/details/100118685