We have a legacy application running on SQL Server 2008 R2.

On DML operations on some of these legacy tables, I need to write data to a set of tables in another database under the same instance.

These legacy tables already have their own INSERT/UPDATE triggers and I cannot modify these.

The company is unwilling/unable to risk using Change Tracking/Replication technologies.

I settled on using additional INSERT & UPDATE triggers on these tables and marked my triggers as Last using sp_settriggerorder. However, the legacy INSERT trigger updates data in the same table which fires the legacy UPDATE triggers and then my Last Update trigger.

E.g Table1 has insert_trigger and update_trigger to which I added my Last triggers myLast_insert_trigger and myLast_update_trigger. insert_trigger has an update:

UPDATE Table1 set c1 = getdate() 
from inserted 
where inserted.primkey = Table1.primkey

This fires update_trigger, myLast_update_trigger and finally myLast_insert_trigger in that sequence. This is expected but I do not want myLast_update_trigger to be fired here. Is there an easy way to achieve this?

I was thinking of querying the system tables and trigger_NestLevel within the last triggers to bypass the last trigger logic but that makes my implementation even more convoluted.

I would really appreciate if there is a mechanism other than triggers (but not change tracking/replication :)) that anyone can suggest.

  • Are you able to simply modify the existing triggers, instead of adding new ones? – Max Vernon Apr 17 at 22:09
  • Unfortunately, I am not allowed to modify the existing triggers – QFirstLast Apr 20 at 6:25

the company is unwilling/unable to risk using Change Tracking/Replication technologies

But willing/able to have you add Triggers? That doesn't make much sense.

Anyway, consider adding your own INSTEAD OF triggers to "front-end" the DML to the target tables. Applying the changes both to the target tables, and the tables in the other database.

That way you can leave the AFTER triggers as-is.

  • Regarding the INSTEAD of triggers... The legacy triggers have business logic e.g the INSERT trigger updates the inserted record with some values and it is these final values that need to end up in the other database. So I need my triggers to fire after everything has run its course. – QFirstLast Apr 17 at 21:23

Triggers are not really the correct way to do this, from a performance perspective and from a data integrity perspective. If the company is worried about the risk of replication and/or change tracking then it likely hasn't been explained correctly to them that there is probably more risk to your data with triggers, especially given you're already seeing issues like this. What happens with a bulk insert, what happens if someone deletes data from the legacy DB, or from the secondary DB, how do you reseed data?

One option: Instead of creating new triggers, create a stored procedure that accepts a Table-Valued Parameter to perform the INSERT and UPDATE logic of your custom triggers, then modify the existing trigger to call this stored procedure as appropriate so that rather than multiple triggers firing, your extra logic is called as part of the original trigger logic.

This has some of the pitfalls of the trigger solution as well but negates the firing order problem.

Another option: determine the data latency restrictions - how far behind can the data in the second database be? If it's acceptable for it to be a little behind then write a stored procedure to snapshot the legacy database and use a MERGE statement to UPSERT the data into the second database (assuming the data volume is not too large to make this process too slow). Run this as an Agent job every N minutes, depending on how long it takes and your latency requirements.

This solution isn't real-time updates but at least provides a reseed solution and minimises the impact on performance.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.