View Issue Details

Jump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0004327SymmetricDSImprovementpublic2020-03-23 17:532020-04-21 19:02
Reporterelong Assigned Toelong  
Prioritynormal 
Status closedResolutionfixed 
Product Version3.9.0 
Target Version3.11.8Fixed in Version3.11.8 
Summary0004327: Sybase: Enhance trigger definition to include ordering specification
DescriptionA Sybase table is used as a source table. This table has existing insert and update triggers on it that does various things. One of them leads to updating the same row on the very same table.

 In Sybase, unfortunately, when there are multiple triggers and the order is not specified, triggers do not have a defined order to run (pretty idiotic if you ask me…).

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc36272.1600/doc/html/san1393050956210.html

 To make matters worse, if a trigger has an order number defined and another trigger does not, the one w/out the defined order will always run after the one with a defined order.

 All that to say that it leads to an update generated by a trigger to be inserted in sym_data before the original statement.

 For example, this insert leads to this data in sym_data. The string “01-01-000” is broken down to its component, 01, 01 & 000 in the trigger and populates the three columns next to it. But once both transaction are applied, they are blank as the insert was processed after the update.

INSERT INTO cord1_asset_cur..asset_breakdown (SPIN_asset_id,bkdn_typ_cd,dan,corp_cd,cli_no,SPIN_org_id,unit_no,invy_stat_cd,drvr_id,cli_asset_id,bkdn,bkdn_level1,bkdn_level2,bkdn_level3,bkdn_level4,bkdn_level5,bkdn_level6,bkdn_level7,bkdn_level8,bkdn_level9,bkdn_level10,bkdn_level11,bkdn_level12,SPIN_audit_insert_dt,SPIN_audit_update_dt,src_extract_dt,cli_del_from_src_ind,ast_del_from_src_ind)
VALUES(14596668,'BLG ','CH762','CA','5183',12708,'C0031','H',' ','PC13-68 ','01-01-000',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ','2020-03-12 12:31:47.543','2020-03-12 12:31:48.57','2020-03-12 12:32:00.0','N','N')

 data_id table_name event_type row_data pk_data old_data trigger_hist_id channel_id transaction_id source_node_id external_data node_list create_time

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

4000016707672 asset_breakdown U "14596668","BLG ","CH762","CA","5183","12708","C0031","H"," ","PC13-68 ","01-01-000","01","01","000"," "," "," "," "," "," "," "," "," ","2020-03-12 12:31:47.543","2020-03-12 15:31:37.376","2020-03-12 12:32:00.000","N","N" "14596668","BLG " "14596668","BLG ","CH762","CA","5183","12708","C0031","H"," ","PC13-68 ","01-01-000"," "," "," "," "," "," "," "," "," "," "," "," ","2020-03-12 12:31:47.543","2020-03-12 12:31:48.570","2020-03-12 12:32:00.000","N","N" 584 SPIN_AST_SYB ef1e28000f000000fffec7b77b02 (null) (null) (null) 2020-03-12 15:31:37.400

4000016707673 asset_breakdown I "14596668","BLG ","CH762","CA","5183","12708","C0031","H"," ","PC13-68 ","01-01-000"," "," "," "," "," "," "," "," "," "," "," "," ","2020-03-12 12:31:47.543","2020-03-12 12:31:48.570","2020-03-12 12:32:00.000","N","N" (null) (null) 584 SPIN_AST_SYB ef1e28000f000000fffec7b77b02 (null) (null) (null) 2020-03-12 15:31:37.410

 

Long term, could SDS add an ORDER <num> clause to the trigger for Sybase 16. Ideally, it would be nice if it could be definable in the UI for each type but I would be happy if you hard code it to something like ORDER 10 for the insert, ORDER 20 for the update and ORDER 30 for the delete (having gaps in the number will let the user add their own triggers anywhere they want).
TagsNo tags attached.

Relationships

related to 0004326 closedelong Sybase: Enhance trigger definition to include ordering specification 

Activities

There are no notes attached to this issue.

Issue History

Date Modified Username Field Change
2020-03-23 17:53 elong New Issue
2020-03-23 17:53 elong Status new => assigned
2020-03-23 17:53 elong Assigned To => elong
2020-03-23 17:53 elong Issue generated from: 0004326
2020-03-23 17:53 elong Relationship added related to 0004326
2020-03-23 17:53 elong Status assigned => resolved
2020-03-23 17:53 elong Resolution open => fixed
2020-03-23 17:53 elong Fixed in Version => 3.11.8
2020-04-21 19:02 admin Status resolved => closed