具体情况如下:表A是存放电文的表,其中表A中的有个字段body是存放电文的内容,body的类型是BLOB,我在表A上写了一个触发器,即当表A新增一条数据以后,立马将这条数据新增当表B(表A和表B的结构是一样的),这个时候发现新增到表B的数据的body字段没有值;创建表B的语句如下:
create table B as select * from A where 1=2;
触发器如下:
create or replace trigger messageMove
after insert on A
for each row
begin
insert into B(FD_MESSAGE_ID,fd_message_code,fd_message_body,fd_message_state,fd_message_state_info,fd_message_send_time,fd_message_group_name,fd_message_operate_time,fd_message_operate_version,fd_message_operate_info)
values(:new.fd_message_id,:new.fd_message_code,:new.fd_message_body,:new.fd_message_state,:new.fd_message_state_info,:new.fd_message_send_time,:new.fd_message_group_name,:new.fd_message_operate_time,:new.fd_message_operate_version,:new.fd_message_operate_info);
end messageMove;
请各位大鸟多多指教。
create table B as select * from A where 1=2;
触发器如下:
create or replace trigger messageMove
after insert on A
for each row
begin
insert into B(FD_MESSAGE_ID,fd_message_code,fd_message_body,fd_message_state,fd_message_state_info,fd_message_send_time,fd_message_group_name,fd_message_operate_time,fd_message_operate_version,fd_message_operate_info)
values(:new.fd_message_id,:new.fd_message_code,:new.fd_message_body,:new.fd_message_state,:new.fd_message_state_info,:new.fd_message_send_time,:new.fd_message_group_name,:new.fd_message_operate_time,:new.fd_message_operate_version,:new.fd_message_operate_info);
end messageMove;
请各位大鸟多多指教。
--参考:
--添加blob数据
create table demo
( id int primary key,
theBlob blob
)
/
create or replace directory my_files as '/export/home/tkyte/public_html';
declare
l_blob blob;
l_bfile bfile;
begin
insert into demo values ( 1, empty_blob() )
returning theBlob into l_blob;
l_bfile := bfilename( 'MY_FILES', 'aria.gif' );
dbms_lob.fileopen( l_bfile );
dbms_lob.loadfromfile( l_blob, l_bfile,
dbms_lob.getlength( l_bfile ) );
dbms_lob.fileclose( l_bfile );
end;
/
--11g中使用dbms_lob.append:
CREATE OR REPLACE PROCEDURE Example_1a IS
dest_lob BLOB;
src_lob BLOB;
BEGIN
-- get the LOB locators
-- note that the FOR UPDATE clause locks the row
SELECT b_lob INTO dest_lob
FROM lob_table
WHERE key_value = 12
FOR UPDATE; SELECT b_lob INTO src_lob
FROM lob_table
WHERE key_value = 21; dbms_lob.append(dest_lob, src_lob);
COMMIT;
END;
--取用:new值插入另一表是没有问题的
SQL> create table lob_a(id number,content blob);
Table created
SQL> create table lob_b as select * from lob_a;
Table created
SQL>
SQL> CREATE OR REPLACE TRIGGER tr_lob_a
2 AFTER INSERT ON lob_a
3 FOR EACH ROW
4 BEGIN
5 INSERT INTO lob_b (id, content) VALUES (:new.id, :new.content);
6 END;
7 /
Trigger created
SQL> insert into lob_a values('1','aa');
1 row inserted
SQL> commit;
Commit complete
SQL> select utl_raw.length(content) from lob_b;
UTL_RAW.LENGTH(CONTENT)
-----------------------
1
SQL>