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
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
update_trigger to which I added my Last triggers
insert_trigger has an update:
UPDATE Table1 set c1 = getdate() from inserted where inserted.primkey = Table1.primkey
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.