x

Issue with Triggers

Hi All,

I have an issue with CLR trigger. Please provide your thoughts on this.

Here is the scenario:

We have an application which is being migrated from SQL 2005 DB to SQL 2008 DB, module by module. Once the first module is migrated to SQL 2008 DB, the data in 2005DB and 2008 DB should be synched immediately for every transactions (two way sync) so that all the modules in both the DBs can have the same data.

We have developed CLR triggers to sync data in both DB’s. Any Insert, Update and Delete operations in one DB table will be reflected in another DB table through triggers.

Trigger created in 2005 DB is perfectly working. That means if new data added to 2005 DB’s table, same data is getting added in 2008 DB table. But the trigger created in 2008 DB is just adding data to new DB table but not to the 2005 DB’s table.

If we remove the triggers from 2005 DB then the triggers on 2008 DB works. If the triggers on both the DBs are enabled, only the 2005->2008 works but 2008->2005 does not work.

Both triggers(NewToOld trigger and OldToNew trigger) have same code, only source and destination tables will change. We have config file to handle this. To summarize both trigger’s has same code with different config file.

Recursive Triggers are turned off and nesting level is set to zero.

Please let me know, what could be the issue and possible fix?

Thanks
more ▼

asked Dec 16, 2011 at 09:55 AM in Default

SaiNarayanan gravatar image

SaiNarayanan
11 1 1 2

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Depends how you have your CLR triggers written. In case your databases are on different SQL Server instances (you have mentioned 2005 and 2008) then it probably be some issue with some circular inserts or something similar.

How you are inserting the values to other tables? If you are opening a connection in the CLR code, then disabling recursive triggers on the instances will not help as you are inserting data to other instance and so no recursive trigger call occurs.

Without detailed info about the inner coder of the CLR trigger it's hard to guess what you have wrong.

Btw, such syncing would be better to implement using some kind of replication. What edition of SQL Server you are using?
more ▼

answered Dec 16, 2011 at 11:01 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

Thanks for the response. Both the databases reside on the same server (Its just the compatibility mode of 2005DB which is 90).

We are not opening new connections. Every insert/update/delete action will trigger and instead of trigger which will update tables in both the versions of the DBs. Same set of triggers are deployed in both the DBs.
Dec 16, 2011 at 06:04 PM SaiNarayanan
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x26
x22

asked: Dec 16, 2011 at 09:55 AM

Seen: 1277 times

Last Updated: Dec 16, 2011 at 09:55 AM