请教：trigger 里得不到blob值是怎么回事儿？

请教：trigger 里得不到blob值是怎么回事儿？# Database - 数据库

c*c2008-09-16 07:09

1 楼

我有一table1，不知为什前面的人把一cloumn设成blob。我有新table叫problem_
history_summary，想得到table1的blob。
我运行
update problem_history_summary set (problem_history, problem_blob，
datestamp，type)= (select UTL_RAW.CAST_TO_VARCHAR2( t.description ), t.
description ，t.datestamp，t.type from table1 t where t.thenumber = '
001A1965');
没有问题。但我设了个trigger，每当table1里insert了new row，我就运行以上的sql
，当然稍微变下
update problem_history_summary set (problem_history, problem_blob，
datestamp，type)= (UTL_RAW.CAST_TO_VARCHAR2( :new.description ), :ne

c*c2008-09-16 07:09

2 楼

google到答案了，原来是不可能的。以前的家伙玩酷，害死我了最近。cloumn的名字净
是，id,type,number,description之类的。
You cannot write to a LOB (:old or :new value) in any kind of trigger.
In regular triggers, you can read the :old value but you cannot read the :
new value. In INSTEAD OF triggers, you can read the :old and the :new values.
You cannot specify LOB type columns in an OF clause, because BFILE types can
be updated without updating the underlying table on which the trigger is
defined.
Using OCI functions or the DBMS_LOB pac

【在 c***c 的大作中提到】

: 我有一table1，不知为什前面的人把一cloumn设成blob。我有新table叫problem_
: history_summary，想得到table1的blob。
: 我运行
: update problem_history_summary set (problem_history, problem_blob，
: datestamp，type)= (select UTL_RAW.CAST_TO_VARCHAR2( t.description ), t.
: description ，t.datestamp，t.type from table1 t where t.thenumber = '
: 001A1965');
: 没有问题。但我设了个trigger，每当table1里insert了new row，我就运行以上的sql
: ，当然稍微变下
: update problem_history_summary set (problem_history, problem_blob，

B*g2008-09-16 07:09

3 楼

source?
just try code in oracle 9i, it works.

values.
can

【在 c***c 的大作中提到】

: google到答案了，原来是不可能的。以前的家伙玩酷，害死我了最近。cloumn的名字净
: 是，id,type,number,description之类的。
: You cannot write to a LOB (:old or :new value) in any kind of trigger.
: In regular triggers, you can read the :old value but you cannot read the :
: new value. In INSTEAD OF triggers, you can read the :old and the :new values.
: You cannot specify LOB type columns in an OF clause, because BFILE types can
: be updated without updating the underlying table on which the trigger is
: defined.
: Using OCI functions or the DBMS_LOB pac

B*g2008-09-16 07:09

4 楼

I guess is because most of insert blob, it is insert an empty_blob() first,
then update the column to real value. so insert trigger fired when the value
is empty_blob(). But when you try
INSERT INTO table
SELECT *
FROM table
the trigger works.

【在 B*****g 的大作中提到】

: source?
: just try code in oracle 9i, it works.
:
: values.
: can

c*c2008-09-16 07:09

5 楼

http://www.acs.ilstu.edu/docs/oracle/appdev.101/b10800/dcilobs.htm
我的oracle 10g。你是说trigger里:new.blob_field不是empty的？我们所有server都
是10g的client。

【在 B*****g 的大作中提到】

: source?
: just try code in oracle 9i, it works.
:
: values.
: can

B*g2008-09-16 07:09

6 楼

not empty.
I will check tonight on 10g

【在 c***c 的大作中提到】

: http://www.acs.ilstu.edu/docs/oracle/appdev.101/b10800/dcilobs.htm
: 我的oracle 10g。你是说trigger里:new.blob_field不是empty的？我们所有server都
: 是10g的client。

B*g2008-09-16 07:09

7 楼

http://www.acs.ilstu.edu/docs/oracle/appdev.101/b10795/adfns_tr.htm
Example: Modifying LOB Columns with a Trigger
You can treat LOB columns the same as other columns, using regular SQL and
PL/SQL functions with CLOB columns, and calls to the DBMS_LOB package with
BLOB columns:
drop table tab1;
create table tab1 (c1 clob);
insert into tab1 values ('

HTML Document Fragment

Some text.');
create or replace trigger trg1
before update on tab1
for each row
begin
dbms_output.put_line('Ol

c*c2008-09-16 07:09

8 楼

It is not working on 10g. I just tried. I used DBMS_LOB functions and get
empty anyway.

【在 B*****g 的大作中提到】

: http://www.acs.ilstu.edu/docs/oracle/appdev.101/b10795/adfns_tr.htm
: Example: Modifying LOB Columns with a Trigger
: You can treat LOB columns the same as other columns, using regular SQL and
: PL/SQL functions with CLOB columns, and calls to the DBMS_LOB package with
: BLOB columns:
: drop table tab1;
: create table tab1 (c1 clob);
: insert into tab1 values ('

HTML Document Fragment

Some text.');
: create or replace trigger trg1
: before update on tab1

B*g2008-09-16 07:09

9 楼

下面这个work，没有用dbms_lob。我估计那个append在trigger之后。
CREATE TABLE PROBLEM_HISTORY_SUMMARY
(
PROBLEM_HISTORY VARCHAR2(4000 BYTE),
TYPE VARCHAR2(10 BYTE),
PROBLEM_BLOB BLOB,
DATESTAMP TIMESTAMP(6)
);
CREATE TABLE TABLE1
(
THENUMBER VARCHAR2(20 BYTE),
TYPE VARCHAR2(10 BYTE),
DESCRIPTION BLOB,
DATESTAMP TIMESTAMP(6)
)
;
CREATE OR REPLACE TRIGGER TRIGGER_TABLE1
BEFORE INSERT
ON TABLE1
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
BEGIN
UPDATE

【在 c***c 的大作中提到】

: It is not working on 10g. I just tried. I used DBMS_LOB functions and get
: empty anyway.

c*c2008-09-16 07:09

10 楼

你是对的。:new.blob_field能得到value。我直接写query insert a row into table1
可以。但通过我们的application就不行。明明每次都看见新row，也得到除
description外所有数值，就是得不到description。哪怕我增加一个blob field 来存
desciption也没用。
多谢了

【在 B*****g 的大作中提到】

: 下面这个work，没有用dbms_lob。我估计那个append在trigger之后。
: CREATE TABLE PROBLEM_HISTORY_SUMMARY
: (
: PROBLEM_HISTORY VARCHAR2(4000 BYTE),
: TYPE VARCHAR2(10 BYTE),
: PROBLEM_BLOB BLOB,
: DATESTAMP TIMESTAMP(6)
: );
: CREATE TABLE TABLE1
: (