View Issue Details

IDProjectCategoryView StatusLast Update
0004499SymmetricDSBugpublic2022-09-12 19:15
Reporterachintya.de@uk.daiwacm.com Assigned Toelong  
Priorityhigh 
Status closedResolutionno change required 
Product Version3.12.0 
Summary0004499: The target/node 02 database triggers are lost , only the SYMDS triggers are there
DescriptionI'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);

Tagsdialect: sybase, trigger

Activities

elong

2020-08-24 12:27

developer   ~0001749

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

achintya.de@uk.daiwacm.com

2020-08-24 15:12

reporter   ~0001750

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

achintya.de@uk.daiwacm.com

2020-08-24 15:15

reporter   ~0001751

I meant on CUSTOM_ON_INSERT_TEXT column of SYM_TRIGGER table for a particular entry

elong

2020-08-24 15:37

developer   ~0001752

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.

achintya.de@uk.daiwacm.com

2020-08-24 15:42

reporter   ~0001753

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 ?

achintya.de@uk.daiwacm.com

2020-09-01 11:50

reporter   ~0001754

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

elong

2020-09-03 13:04

developer   ~0001755

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

achintya.de@uk.daiwacm.com

2020-09-03 13:29

reporter   ~0001756

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

Issue History

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