Triggers and transactions

pwhybrow

New Member
Hi all

We are doing some trigger replication development and a question has been raised about when a trigger fires. Can a trigger fire before a transaction is complete? It doesn't sound like a logical idea to me and I would rather not have to code around looking to see if transactions are done with before trusting that the a trigger fire is valid.

Thanks
 

TomBascom

Curmudgeon
Building your own replication system is a lot more complicated than people think it is.

We've just had a fairly long thread about this topic Question - Copy replication trigger assignments.

Replication triggers are fired within the scope of the relevant transaction. They should really only be used to quickly create a queue record to be processed by someone else later. You're asking for big trouble if you do the actual replication to an external system within a replication trigger.

Replication triggers were useful 20 years ago but:

1) SQL doesn't know about them
2) They don't know about SQL
3) Unfortunately people do actually use SQL

4) Change-data-capture is a much, much better way to manage these things

If you are wanting to act on changes after the fact then you should be looking at auditing or change data capture. CDC is the better option but if you are on an ancient release and refuse to upgrade, auditing can get the job done too.
 
Top