View Issue Details
ID | Project | Category | View Status | Date Submitted | Last Update |
---|---|---|---|---|---|
0004499 | SymmetricDS | Bug | public | 2020-08-13 16:27 | 2022-09-12 19:15 |
Reporter | achintya.de@uk.daiwacm.com | Assigned To | elong | ||
Priority | high | ||||
Status | closed | Resolution | no change required | ||
Product Version | 3.12.0 | ||||
Summary | 0004499: The target/node 02 database triggers are lost , only the SYMDS triggers are there | ||||
Description | I've setup a one-way data replication (Push) between a SQL Server node (master node/node 01) and Sybase ASE node (subscriber/node 02). Most of the tables on Sybase side had 3 user defined triggers setup but after setting up Symmetric DS replication between those nodes, the triggers on Sybase/Node 02 side has been dropped, the only triggers I can see are the SYM triggers I need to reinstate the triggers on Sybase side b'cos those triggers populates tables on other databases on the same instance there must be something I've configured wrongly hence this mishap I can redo the whole thing again please let me know asap what's the solution to keep those triggers while setting up SymDS thanks Achintya | ||||
Additional Information | -- An entry for the current node insert into sym_node (node_id, node_group_id, external_id, sync_enabled, created_at_node_id) values ('001', 'SQLServer', '001', 1, '001') go -- Node 002 was created automatically by SymDS while starting up as per the engine file -- Set up 2 groups - each node needs it's own groups: insert into sym_node_group (node_group_id) values ('SQLServer') go insert into sym_node_group (node_group_id) values ('Sybase') go -- Set type of replication between each group [ done on SQL Server side only ] insert into sym_node_group_link (source_node_group_id, target_node_group_id, data_event_action) values ('SQLServer', 'Sybase', 'P'); insert into sym_node_group_link (source_node_group_id, target_node_group_id, data_event_action) values ('Sybase', 'SQLServer', 'P'); insert into sym_router (router_id,source_node_group_id,target_node_group_id,router_type,create_time,last_update_time) values('SQLServer-Sybase', 'SQLServer', 'Sybase', 'default',current_timestamp, current_timestamp); insert into sym_router (router_id,source_node_group_id,target_node_group_id,router_type,create_time,last_update_time) values('Sybase-SQLServer', 'Sybase', 'SQLServer', 'default',current_timestamp, current_timestamp); | ||||
Tags | dialect: sybase, trigger | ||||
|
Sybase ASE 16 introduced the ability to create multiple triggers on the same table. If your Sybase ASE is older than version 16, then it's probably going to drop your triggers. You could move your trigger logic into the SYM triggers. Look at the custom_on_insert/update/delete and custom_before_on_insert/update/delete fields on the sym_trigger table. https://www.symmetricds.org/doc/3.12/html/user-guide.html#_trigger |
|
Thank you Eric for you comment/solution as SymDS is new to me and my company, should i include the whole text of the trigger in "Specify insert trigger text to execute after the SymmetricDS trigger text runs. " or just the SQL part after the "Create trigger ..." part ? ie CREATE TRIGGER <trig name> on <table name> FOR INSERT AS BEGIN ... END So, which part Eric from the above example trigger ? whole thing or just the part starting from "BEGIN.." or just the SQL between "BEGIN" and "END" ? Please let me know so what i can try for a single Channel/Table Thanks Achintya |
|
I meant on CUSTOM_ON_INSERT_TEXT column of SYM_TRIGGER table for a particular entry |
|
What you provide is placed inside of the BEGIN-END of the trigger body, so you don't need a BEGIN or END in your text. |
|
Thanks Eric, it seems it'll be a painful task for me as all of our tables has 3 triggers defined on them. Can you recommend a easy/clever way to populate the relevant columns of SYM_TRIGGER table for each table / entry ? |
|
One more thing Eric. Do i need to update the CUSTOM_ON_INSERT_TEXT etc columns of SYM_TRIGGER table on Master node/database side or in the Subscribed node/database side ? pls let me know thank you |
|
For your triggers that populate data, you would only want those on the master side. On the master side, all changes are captured, including the changes made by triggers, and those changes are replicated to the subscriber node. On Sybase, you can query the text of triggers, so it seems possible to write some clever SQL to either generate a script or even update the sym_trigger table. Here is an example: select c.text from syscomments c, sysobjects o where o.id=c.id and o.name='mytriggername' order by c.colid |
|
Thanks Eric, i got your point about the trigger firing at master side, which makes sense in most cases but in our case, we want the trigger should fire only on subscribed side b'cos our multi-database triggers populate tables on other databases this is part of a Sybase to SQL server migration project and all the dbs wont be migrated to SQL server in a big bang, only the reference database will be migrated to SQL Server for now and the app team wants the trigger will fire only on Sybase/subscribed site hope this gives you a better picture of the setup im trying to set thanks Achintya |
Date Modified | Username | Field | Change |
---|---|---|---|
2020-08-13 16:27 | achintya.de@uk.daiwacm.com | New Issue | |
2020-08-24 12:27 | elong | Note Added: 0001749 | |
2020-08-24 12:27 | elong | Assigned To | => elong |
2020-08-24 12:27 | elong | Status | new => feedback |
2020-08-24 15:12 | achintya.de@uk.daiwacm.com | Note Added: 0001750 | |
2020-08-24 15:12 | achintya.de@uk.daiwacm.com | Status | feedback => assigned |
2020-08-24 15:15 | achintya.de@uk.daiwacm.com | Note Added: 0001751 | |
2020-08-24 15:37 | elong | Note Added: 0001752 | |
2020-08-24 15:42 | achintya.de@uk.daiwacm.com | Note Added: 0001753 | |
2020-09-01 11:50 | achintya.de@uk.daiwacm.com | Note Added: 0001754 | |
2020-09-03 13:04 | elong | Note Added: 0001755 | |
2020-09-03 13:29 | achintya.de@uk.daiwacm.com | Note Added: 0001756 | |
2022-08-02 20:06 | elong | Tag Attached: trigger | |
2022-08-02 20:07 | elong | Tag Attached: dialect: sybase | |
2022-09-12 19:15 | elong | Status | assigned => closed |
2022-09-12 19:15 | elong | Resolution | open => no change required |