View Issue Details

IDProjectCategoryView StatusLast Update
0004316SymmetricDSImprovementpublic2020-03-23 14:00
ReporterpmarzulloAssigned Toelong 
Prioritynormal 
Status resolvedResolutionfixed 
Product Version3.9.0 
Target Version3.9.25Fixed in Version3.9.25 
Summary0004316: 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).
Tagsdialect: sybase, trigger

Relationships

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

Activities

There are no notes attached to this issue.

Related Changesets

SymmetricDS: 3.9 377ff3fe

2020-03-18 08:38:03

admin

Details Diff
0004316: Sybase: Enhance trigger definition to include ordering
specification

0004316
add - symmetric-client/src/main/java/org/jumpmind/symmetric/db/ase/Ase16TriggerTemplate.java Diff File
mod - symmetric-client/src/main/java/org/jumpmind/symmetric/db/ase/AseSymmetricDialect.java Diff File
mod - symmetric-client/src/main/java/org/jumpmind/symmetric/db/ase/AseTriggerTemplate.java Diff File

SymmetricDS: 3.10 ab6a489a

2020-03-18 08:38:03

admin

Details Diff
0004316: Sybase: Enhance trigger definition to include ordering
specification

0004316
add - symmetric-client/src/main/java/org/jumpmind/symmetric/db/ase/Ase16TriggerTemplate.java Diff File
mod - symmetric-client/src/main/java/org/jumpmind/symmetric/db/ase/AseSymmetricDialect.java Diff File
mod - symmetric-client/src/main/java/org/jumpmind/symmetric/db/ase/AseTriggerTemplate.java Diff File

SymmetricDS: 3.11 7758ea3e

2020-03-18 08:38:03

admin

Details Diff
0004316: Sybase: Enhance trigger definition to include ordering
specification
# Conflicts:
# symmetric-client/src/main/java/org/jumpmind/symmetric/db/ase/AseTriggerTemplate.java

0004316
add - symmetric-client/src/main/java/org/jumpmind/symmetric/db/ase/Ase16TriggerTemplate.java Diff File
mod - symmetric-client/src/main/java/org/jumpmind/symmetric/db/ase/AseSymmetricDialect.java Diff File
mod - symmetric-client/src/main/java/org/jumpmind/symmetric/db/ase/AseTriggerTemplate.java Diff File

Issue History

Date Modified Username Field Change
2020-03-16 07:58 pmarzullo New Issue
2020-03-16 07:58 pmarzullo Tag Attached: dialect: sybase
2020-03-16 07:58 pmarzullo Tag Attached: trigger
2020-03-18 08:52 elong Assigned To => elong
2020-03-18 08:52 elong Status new => resolved
2020-03-18 08:52 elong Resolution open => fixed
2020-03-18 08:52 elong Product Version 3.9.13 => 3.9.0
2020-03-18 08:52 elong Fixed in Version => 3.9.25
2020-03-18 08:52 elong Target Version => 3.9.25
2020-03-18 09:00 admin Changeset attached => SymmetricDS 3.9 377ff3fe
2020-03-23 13:43 elong Issue cloned: 0004326
2020-03-23 13:43 elong Relationship added related to 0004326
2020-03-23 14:00 admin Changeset attached => SymmetricDS 3.10 ab6a489a
2020-03-23 14:00 admin Changeset attached => SymmetricDS 3.11 7758ea3e