Blobs and triggers - How to insert information into a Blob field inside a trigger.

[Itamar Lev]

hi All,

I have a problem with writing to BLOB's in triggers.

The program I created builds triggers dynamically,
there's a table that holds the names of the tables that needs a
trigger
and the type of the trigger.
when the program is going over the fields of the table that needs a
trigger
it checks for BLOB/CLOB's and uses a different insert to write them to
another
table.

create trigger AI_INITIAL
AFTER insert on INITIALIZE
referencing NEW as new
for each row mode DB2SQL
BEGIN ATOMIC
declare mevent VARCHAR(1000);
declare delimiter VARCHAR(1);
declare seq INTEGER ;
set delimiter = CHR(255) ;
set seq = NEXTVAL FOR SQ_LOG_RECORD;
set mevent = COALESCE(RTRIM(CHAR(new.C_INIT) ), ' ') || delimiter ||
COALESCE(RTRIM(CHAR(new.FLAG_VALUE) ), ' ') || delimiter ||
COALESCE(LEFT(CHAR(new.D_INIT2),19),' ') || delimiter ||
COALESCE(RTRIM(CHAR(new.C_STAFF) ), ' ') || delimiter ||
COALESCE(LEFT(CHAR(new.D_INSERT),19),' ') || delimiter ||
COALESCE(new.C_CODE, ' ') || delimiter ||
COALESCE(RTRIM(CHAR(new.B_FLAG2) ), ' ') || delimiter ||
COALESCE(new.DESCRIPTION, ' ') || delimiter || RTRIM(CHAR( seq ) ) ;

here is the insert for a BLOB:

insert into BLOB_DATA(N_SEQ, FIELD_NAME, C_MIME_TYPE, BLOB_DATA)
values ( seq ,'M_JPG', 21 , new.M_JPG);

that's the regular insert:

insert into LOG_RECORD(N_SEQ, C_LOG_TYPE, C_INSERT, D_INSERT,
C_APPLICATION, C_MODULE, C_KEY1 ,KEY_FIELD1, C_KEY2, KEY_FIELD2,
C_LOG_ACTION, TABLE_NAME, SCHEMA_NAME, C_MESSAGE, M_EVENT) values (seq
,4,new.C_STAFF , CURRENT TIMESTAMP ,0,0,new.C_INIT, 'C_INIT'
,null,null,1,'INITIALIZE','USER',60004,mevent );
END

the problems are that the BLOB insert has not been proved to work
and the mevent which may include several fields that sum up to a BLOB
size that
makes the insert illegal for the string is too long !!!
it's clear to see that LOG_RECORD last field is of LONG VARCHAR.
so does the last field of BLOB_DATA.


How do I use a Blob field instead
and how do I insure that the select won't fail because of it being too
long ?

Thanks,
Itamar