IDS Forum

Trigger ignore all exceptional handling in SP

Posted By: LEY PATRICK
Date: Thursday, 19 January 2017, at 7:10 p.m.

Informix version : 12.10.FC6AEE
Platform : Linux

Requirement:
1) Invoke of the following stored procedure happens on INSERT TRIGGER level and it need to be at trigger level.

I had written a stored procedure with exceptional handling.
Sample Code
===========
create procedure fn_ins_tevoc (

_p_ev_oc_id integer,

_p_user_id varchar(50),

_p_ev_mkt_id integer,

_p_ev_id integer,

_p_hcap_value decimal(12,2),

_p_hcap_score integer,

_p_cashout_avail decimal(12,2),

_p_bir_cashout_avail decimal(12,2),

p_debug smallint default 0
)

define nrows integer;

define l_var_created_tm datetime year to second;
define l_var_created_by varchar(18,10);
define l_var_last_updated datetime year to second;
define l_var_updated_by varchar(18,10);

define l_var_ev_type_id integer ;
define l_var_ev_class_id integer ;
define l_var_class_category varchar(16) ;

define l_tabname varchar(100) ;
define l_change_keys smallint ;

define sql_err int;
define isam_err int;
define error_info char(70);
define l_msg varchar(255) ;

if p_debug = 1 then

set debug file to "/tmp/fn_ins_tevoc.TRC";

trace on ;
end if

begin

on exception set sql_err, isam_err, error_info

let l_msg = "where ev_oc_id=" || trim(to_char(_p_ev_oc_id)) ;

execute procedure log_error(p_op="INSERT", p_tabname=l_tabname, p_cond=l_msg, p_err="isam_err=" || to_char(isam_err) || " sql_err =" || to_char(sql_err), p_aff_module="pop_tevoc_full_details", p_from_mesg="" );

--raise exception sql_err, isam_err, error_info ;

RAISE EXCEPTION -208, 0;

end exception with resume ;

--Calculate the required fields

execute procedure get_ev_class_n_type_id(_p_ev_id,"FROM tevoc where ev_oc_id=" || to_char(_p_ev_oc_id)) into l_var_ev_class_id, l_var_ev_type_id ;

let l_var_class_category = get_category_evtab (_p_ev_id,"FROM tevoc where ev_oc_id=" || to_char(_p_ev_oc_id) ) ;

let l_var_created_tm = current ;

let l_var_created_by = user ;

let l_var_last_updated = l_var_created_tm ;

let l_var_updated_by = l_var_created_by ;

let l_tabname = "tevoc_full_details" ;

--if not exists (select ev_oc_id from tevoc_full_details where ev_oc_id = _p_ev_oc_id) then

insert into tevoc_full_details ( ev_oc_id,user_id,ev_mkt_id,ev_id,hcap_value,hcap_score,cashout_avail,bir_cashout_avail,ev_type_id,ev_class_id,class_category,created_tm,created_by,last_updated,updated_by)

values ( _p_ev_oc_id,_p_user_id,_p_ev_mkt_id,_p_ev_id,_p_hcap_value,_p_hcap_score,_p_cashout_avail,_p_bir_cashout_avail,l_var_ev_type_id,l_var_ev_class_id,l_var_class_category,current,user,null,null);

--end if -- Of not exists

end -- Of begin

if p_debug = 1 then

trace off ;
end if
end procedure ;

Invoke from trigger
===================
create trigger tmp_insert_tevoc insert on

tevoc referencing new as _new

for each row

(

execute procedure "informix".fn_ins_tevoc(_p_ev_oc_id=

_new.ev_oc_id ,_p_user_id= _new.user_id ,_p_ev_mkt_id= _new.ev_mkt_id

,_p_ev_id= _new.ev_id,_p_hcap_value= _new.hcap_value ,_p_hcap_score= _new.hcap_score ,

,_p_cashout_avail= _new.cashout_avail ,_p_bir_cashout_avail= _new.bir_cashout_avail,p_debug= 1 ));

Issue
1) According to IBM official site ( http://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqls.doc/ids_sqs_1351.htm ) it mentioned the following :

No ON EXCEPTION Support in Triggered Actions

The ON EXCEPTION statement has no effect when it is issued from an SPL routine in the following calling contexts:

in a trigger routine,

in the Action clause or the Correlated Action clause of a trigger on a table,

in the Action clause of an INSTEAD OF trigger on a view.

When a UDR includes ON EXCEPTION in any of these contexts, the database server ignores the ON EXCEPTION statement.

Observation
-----------
1) If I insert a new record in into target table(tevoc) it trigger take effects and it failed on "insert into tevoc_full_details".
i) the exceptional is not being trigger
ii) the record is being rejected on tevoc table as well.

Questions
1) Is that a way to get the exceptional handling to take effect even it is being called from trigger?
2) Is that a way to get the record inserted (at tevoc table) in event of observation ii) above happen ?

Appreciate your input.

IDS Forum is maintained by Administrator with WebBBS 5.12.