Trigger ignore all exceptional handling in SP
Posted in 2017
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_cash
out_avail,_p_bir_cashout_avail,l_var_ev_type_id,l_var_ev_class_id,l_var_class_ca
tegory,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.