asked
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
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?
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.
