View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0004316 | SymmetricDS | Improvement | public | 2020-03-16 11:58 | 2020-05-26 16:36 |
| Reporter | pmarzullo | Assigned To | elong | ||
| Priority | normal | ||||
| Status | closed | Resolution | fixed | ||
| Product Version | 3.9.0 | ||||
| Target Version | 3.9.25 | Fixed in Version | 3.9.25 | ||
| Summary | 0004316: Sybase: Enhance trigger definition to include ordering specification | ||||
| Description | A 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). | ||||
| Tags | dialect: sybase, trigger | ||||
|
SymmetricDS: 3.9 377ff3fe 2020-03-18 08:38:03 Details Diff |
0004316: Sybase: Enhance trigger definition to include ordering specification |
Affected Issues 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 Details Diff |
0004316: Sybase: Enhance trigger definition to include ordering specification |
Affected Issues 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 Details Diff |
0004316: Sybase: Enhance trigger definition to include ordering specification # Conflicts: # symmetric-client/src/main/java/org/jumpmind/symmetric/db/ase/AseTriggerTemplate.java |
Affected Issues 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 | ||
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2020-03-16 11:58 | pmarzullo | New Issue | |
| 2020-03-16 11:58 | pmarzullo | Tag Attached: dialect: sybase | |
| 2020-03-16 11:58 | pmarzullo | Tag Attached: trigger | |
| 2020-03-18 12:52 | elong | Assigned To | => elong |
| 2020-03-18 12:52 | elong | Status | new => resolved |
| 2020-03-18 12:52 | elong | Resolution | open => fixed |
| 2020-03-18 12:52 | elong | Product Version | 3.9.13 => 3.9.0 |
| 2020-03-18 12:52 | elong | Fixed in Version | => 3.9.25 |
| 2020-03-18 12:52 | elong | Target Version | => 3.9.25 |
| 2020-03-18 13:00 | admin | Changeset attached | => SymmetricDS 3.9 377ff3fe |
| 2020-03-23 17:43 | elong | Issue cloned: 0004326 | |
| 2020-03-23 17:43 | elong | Relationship added | related to 0004326 |
| 2020-03-23 18:00 | admin | Changeset attached | => SymmetricDS 3.10 ab6a489a |
| 2020-03-23 18:00 | admin | Changeset attached | => SymmetricDS 3.11 7758ea3e |
| 2020-05-26 16:36 | elong | Status | resolved => closed |