Sybase Database - Newbie: raiserror error code not caught on Sybase ASE 11.9.2.4

Author

Newbie: raiserror error code not caught on Sybase ASE 11.9.2.4

jose_luis_fdez_diaz_news@yahoo.es

2006-03-23, 3:23 am

Hi,

Given the sql script below:

------------------------------------------------------------------------------------------------
INSERT into BMF (
account_no, bill_ref_no, bill_ref_resets,
orig_bill_ref_no, orig_bill_ref_resets
, bmf_trans_type,
trans_date, post_date, currency_code, trans_amount,
tracking_id, tracking_id_serv, orig_tracking_id,
orig_tracking_id_ser
v, trans_source, batch_id,
batch_id_serv, trans_submitter, chg_date,
no_bill, action_code, ccard_id, ccard_id_serv,
micr_bank_id, micr_dda_num, micr_check_num,
arch_flag, manual_ccauth_code, manual_ccauth_date,
distributed_amount, file_id, gl_amount, distributed_gl_amoun
t,
response_code,
open_item_id, external_amount, external_currency,
cr_note_bill_ref_no,
cr_note_bill_ref_res
ets,
source_type, source_id, source_id_serv, bill_order_number)
VALUES (5361049, 0, 0, 808089338, 0, -99, 'Mar 21 2006 12:00AM', 'Mar
22 2006 4:20PM', 1, 1096, 4656430, 13, 0 , 13, 1, '' , /*batch_id*/ 2,
'Prueba RRA', 'Mar 22 2006 4:20PM', 0, 'APP' , 0 , 0 , '',
/*micr_bank_id*/ '', '', 0, '', '', /*manual_ccauth_date ECC/ NOR1/
NOR2/ OEPV/ backup00_02/ backup00_03/ backup00_04/ backup01_00/
backup01_01/ backup01_02/ backup01_04/ backup01_06/ backup07_08/
backup07_09/ backup07_11/ backup07_12/ backup07_14/ backup08_00/
backup08_02/ backup08_03/ backupUMBRALES/ catalog/ d1/ d2/ datatools/
etc/ fuentes_santi/ iwpipe/ javier_pruebas/ l/ lib/ lost+found/ u08/
v7.0.1/ 0, 0 , 0 , 0, '' , 0 , 1096, 1, 0, 0 , 1, 4656430, 13, '')

if (@@error!=0)
print "error"
go
--------------------------------------------------------------------------------------------------------------------------------------

In the BMF table there is a trigger:

--------------------------------------------------------------------
create trigger bmf_insert_trig on BMF
for INSERT as
.. . .
/* Ensure that trans type exists in BMF trans descr */
if (select count(*)
from BMF_TRANS_DESCR,
inserted
where BMF_TRANS_DESCR.bmf_trans_type =
inserted.bmf_trans_type) !=
@row
begin
print "bmf_insert_trig"
raiserror 20001 "131001, TRIG: Insert Failed:
Transaction type does
not exist"
rollback transaction
return
end
......
end
-----------------------------------------------------------------------

When I run the script with isql the output below is shown:

------------------------------------------------------------------------------------
isql -SDESA102_DS -DCUSTOMER401 -Udbo_billing -Pdbo_billing
-i/tmp/in.sql
bmf_insert_trig
Msg 20001, Level 16, State 1:
Server 'DESA102_DS', Procedure 'bmf_insert_trig', Line 39:
131001, TRIG: Insert Failed: Transaction type does not exist
(1 row affected)
-----------------------------------------------------------------------------------------------

Why is not printed the "error" string ?

Thanks in advance,
Jose Luis

jose_luis_fdez_diaz_news@yahoo.es

2006-03-23, 3:23 am

I have found some information about this issue on Intenet.

It seems that there is a bug in Sybase ASE 11. If I replace:

----------------------------------------------------
raiserror 20001 "131001, TRIG: Insert Failed:
Transaction type does
not exist"
rollback transaction
------------------------------------------------------

with:

--------------------------------------------------------
rollback trigger with raiserror 20001 "131001, TRIG:
Insert Failed:
Transaction type does not exist"
----------------------------------------------------------

the script runs fine. But I don't want to modify the trigger.

Any hint?

Regards,
Jose Luis.

Sponsored Links