I have setup transactional replication between 2 computers.
In the publisher, there is a INSTEAD OF DELETE trigger to prevent deletion of any rows in a table. If an attempt to delete rows in that table occurs, this trigger will write the deleting rows to a local file C:\temp\yyyy.mm.dd.hh.mm.ss.tbl_deleted.csv, and then email this file to a list of recipients. This trigger only exists in the publisher.
Yesterday, while I'm working on the publisher DB, I accidentally executed a stored procedure which attempted to delete some rows in the table. The trigger fired, a file C:\2012.09.04.09.01.01.tbl_deleted.csv generated, and the email was sent. But then problem occurs.
When I open replication monitor, I see a bunch of errors in "Distributor To Subscriber History". The error said:
if @@trancount > 0 rollback tran
Attachment file C:\temp\2012.09.05.01.01.43.36.tbl_deleted.csv is invalid.
(Source: MSSQLServer. Error number: 22051)
Similar errors occur periodically, and the filename (C:\temp\yyyy.mm.dd.hh.mm.ss.tbl_deleted.csv) in the error messages reflect the actual time of the error.
So how should I fix this? I have tried to reinitialize the subscription with a new snapshot, but it does not help. After some time, the same errors occur again.
The scope of a trigger should be limited to its own table, or at most its own database.
You should not be using a trigger for generating data files.
Instead, use your trigger to populate a staging table (not replicated) and then use a scheduled job or stored procedure to email any data found in the staging table.
This is a much more robust solution.
If it's not practically useful, then it's practically useless.