Issue with instead of trigger on a view

303576

Gurus,

I have an issue with an instead of trigger on a view. The trigger is listed below. The insert and update seem to be working fine but the delete section is not.

From the application, we have a screen on which we attach images. We trigger of an insert and update when we attach images. We are using hibernate as our object relational mapping tool.

We have added some logging into the delete section but that portion of the trigger does not seem to be executing at all.

Please advise.

Thanks
Hari

------------------------------

CREATE OR REPLACE TRIGGER trg_vw_result_image_uid
INSTEAD OF
INSERT OR DELETE OR UPDATE
ON vw_result_image
REFERENCING NEW AS NEW OLD AS OLD
DECLARE
v_cnt number(38);
v_cnt_old number(38);
v_err_msg VARCHAR2 (250);
BEGIN

-- v_rslt_id number(38);
-- v_cnt number(38);

select count(1) into v_cnt from result_image_master
where RSLT_IMAGE_ID = :new.RSLT_IMAGE_ID;

--select count(1) into v_cnt from result_image_master
-- where ACC_BLKBR_ID = :new.ACC_BLKBR_ID
-- and upper(RSLT_IMAGE_NM) = upper(:new.RSLT_IMAGE_NM);

select count(1) into v_cnt_old from result_image_master
where RSLT_IMAGE_ID = :old.RSLT_IMAGE_ID;

insert into t2( TEXT_VAL, DT1, seq1)
values (' before v_cnt', sysdate, t6.NEXTVAL);
--if v_cnt = 0

--****INSERTING

IF INSERTING
THEN
insert into t2( TEXT_VAL, DT1, seq1)
values (' v_cnt is 0 and inserting into result_image_master', sysdate, t6.NEXTVAL);
insert into t2( TEXT_VAL, DT1, seq1)
values (' inserted bb id :'||:new.ACC_BLKBR_ID, sysdate, t6.NEXTVAL);
insert into result_image_master (
RSLT_IMAGE_ID
,RSLT_IMAGE_HBR_VER
,RSLT_IMAGE_TYPE_ID
,RSLT_IMAGE_NM
,RSLT_IMAGE_LABEL
,RSLT_IMAGE_SEQ
,RSLT_SHOW_ON_RPT
,RSLT_SLIDE_NO
,RSLT_CELL_NO
,RSLT_X_COORD
,RSLT_Y_COORD
,ACC_BLKBR_ID
,CREATED_BY
,DATE_CREATED
,MODIFIED_BY
,DATE_MODIFIED
)
values (
:new.RSLT_IMAGE_ID
,:new.RSLT_IMAGE_HBR_VER
,:new.RSLT_IMAGE_TYPE_ID
,:new.RSLT_IMAGE_NM
,:new.RSLT_IMAGE_LABEL
,:new.RSLT_IMAGE_SEQ
,:new.RSLT_SHOW_ON_RPT
,:new.RSLT_SLIDE_NO
,:new.RSLT_CELL_NO
,:new.RSLT_X_COORD
,:new.RSLT_Y_COORD
,:new.ACC_BLKBR_ID
,:new.CREATED_BY
,:new.DATE_CREATED
,:new.MODIFIED_BY
,:new.DATE_MODIFIED
);

insert into result_image_blob (
RSLT_IMAGE_ID
,rslt_image_blob
)
values (
:new.RSLT_IMAGE_ID
,:new.rslt_image_blob
);

--****UPDATING
ELSIF UPDATING
-- v_cnt > 0 --
THEN
insert into t2( TEXT_VAL, DT1, seq1)
values (' updating result_image_master', sysdate, t6.nextval);
insert into t2( TEXT_VAL, DT1, seq1)
values (' updating bb id :'||:new.ACC_BLKBR_ID, sysdate, t6.nextval);
update result_image_master
set RSLT_IMAGE_HBR_VER = RSLT_IMAGE_HBR_VER + 1
,RSLT_IMAGE_TYPE_ID = :new.RSLT_IMAGE_TYPE_ID
,RSLT_IMAGE_NM = :new.RSLT_IMAGE_NM
,RSLT_IMAGE_LABEL = :new.RSLT_IMAGE_LABEL
,RSLT_IMAGE_SEQ = :new.RSLT_IMAGE_SEQ
,RSLT_SHOW_ON_RPT = :new.RSLT_SHOW_ON_RPT
,RSLT_SLIDE_NO = :new.RSLT_SLIDE_NO
,RSLT_CELL_NO = :new.RSLT_CELL_NO
,RSLT_X_COORD = :new.RSLT_X_COORD
,RSLT_Y_COORD = :new.RSLT_Y_COORD
,ACC_BLKBR_ID = :new.ACC_BLKBR_ID
,MODIFIED_BY = :new.MODIFIED_BY
,DATE_MODIFIED = :new.DATE_MODIFIED
where RSLT_IMAGE_ID = :new.RSLT_IMAGE_ID;

update result_image_blob
set rslt_image_blob = :new.rslt_image_blob
where RSLT_IMAGE_ID = :new.RSLT_IMAGE_ID;
END IF;

IF DELETING OR v_cnt_old > 0
THEN
insert into t2( TEXT_VAL, DT1, seq1) values (' deleting rows ...', sysdate, t6.NEXTVAL);
DELETE from result_image_blob where RSLT_IMAGE_ID = :old.RSLT_IMAGE_ID;
insert into t2( TEXT_VAL, DT1, seq1) values ('deleting result_image_blob : '||:old.RSLT_IMAGE_ID , sysdate, t6.NEXTVAL);
DELETE from result_image_master where RSLT_IMAGE_ID = :old.RSLT_IMAGE_ID;
insert into t2( TEXT_VAL, DT1, seq1) values ('deleting result_image_master : '||:old.RSLT_IMAGE_ID , sysdate, t6.NEXTVAL);


END IF;


EXCEPTION
WHEN OTHERS THEN

v_err_msg := SQLERRM;
insert into t2( TEXT_VAL, DT1, seq1) values (v_err_msg, sysdate, t6.nextval);
END;
/

Edited by: bhanujh on Sep 13, 2010 7:55 AM

303576

The error msg that we are getting is

09/08/2010 4:02:09 PM: Unable to save the results :: Could not execute JDBC batch update

LKBrwn_DBA

bhanujh wrote:
The error msg that we are getting is

09/08/2010 4:02:09 PM: Unable to save the results :: Could not execute JDBC batch update

Sorry, we don't recognize this message as any valid Oracle error.