2

I am trying to have a trigger fire when the Merge agent inserts or deletes data into / from a table. It is supposed to insert data into a report table for use with SSRS.

I DO NOT have the "NOT FOR REPLICATION" set in the trigger. Here is the trigger code:

ALTER TRIGGER [dbo].[MyTable_INSERT] 
ON  [dbo].[MyTable] 
FOR INSERT
AS 
BEGIN
    SET NOCOUNT ON;
    INSERT INTO...
END

I have tried FOR INSERT and AFTER INSERT, neither seem to work.

The table structure is usual parent / child, I have the trigger set on the child to make sure the parent is already created. The relationships are secured with FK's.

I'm sure am overlooking something simple. Any ideas ?

  • From BOL - if you define an insert trigger with the NOT FOR REPLICATION option set, all user inserts fire the trigger, but inserts from replication agents do not. Consider a trigger that inserts data into a tracking table: when the user inserts the row originally, it is appropriate for the trigger to fire and enter a row into the tracking table, but the trigger should not fire when that data is replicated to the Subscriber, because it would result in an unnecessary row being inserted in the tracking table. – Kin Shah Jul 21 '15 at 13:47
  • Sorry, I think you are missing the point, the trigger should fire when the merge agent inserts the data. I have not defined NOT FOR REPLICATION and would expect that behaviour, but that does not seem to be the case. – henda79 Jul 21 '15 at 15:09
  • First you have to test that your trigger actually fires for an insert into the table AFTER INSERT. Check this msdn blog for more info. – Kin Shah Jul 21 '15 at 15:17
  • Thanks I have already read through that. It explains when and why to use NFR and not how to check if the trigger is firing at all. – henda79 Jul 21 '15 at 15:50
  • Just for the record, FOR INSERT = AFTER INSERT in Transact-SQL. – Andriy M Jan 28 '16 at 7:54
1

The problem was an error in my trigger insert code. It was using INNER JOIN's and one was not correct so was not returning any data.

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.