Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2012
    Posts
    2

    Unanswered: Replication Error due to Trigger

    Situation:
    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.

    Problem:
    When I open replication monitor, I see a bunch of errors in "Distributor To Subscriber History". The error said:
    Command attempted:
    if @@trancount > 0 rollback tran
    Error messages:
    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.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    An alternative would be to place the deleted records into a Service Broker queue, then you can do whatever processing is needed on them at your leisure.

  4. #4
    Join Date
    Sep 2012
    Posts
    2
    Apart from modifying the trigger, is there anyway to prevent the trigger from fired due to replication?

    I have added NOT FOR REPLICATION, and

    DECLARE @TableID int = OBJECT_ID(N'my_table');
    DECLARE @RetCode int;
    EXEC @RetCode = sp_check_for_sync_trigger @tabid = @TableID, @trigger_op = NULL, @fonpublisher = 1
    IF @RetCode = 1
    RETURN;

    at the top of the trigger, but it doesn't help.

    I recreated the publication & subscription, but the error (file not found) still exists, indicating the trigger is fired.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •