Solved

insert trigger fired on replication

Posted on 2004-09-15
5
419 Views
Last Modified: 2008-03-17
Hello

I have a trigger that fires when data is inserted into one table.  The database is published for merge replication and when the data is updated at the subscriber, the trigger fires again. How can I stop the trigger firing when the replication takes place?

Thank you in advance
Debs
0
Comment
Question by:debrafry
  • 3
  • 2
5 Comments
 
LVL 6

Accepted Solution

by:
OlegP earned 500 total points
ID: 12063960
CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
    { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] }
        [ WITH APPEND ]
        [ NOT FOR REPLICATION ]
        AS
        [ { IF UPDATE ( column )
            [ { AND | OR } UPDATE ( column ) ]
                [ ...n ]
        | IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
                { comparison_operator } column_bitmask [ ...n ]
        } ]
        sql_statement [ ...n ]
    }
}



NOT FOR REPLICATION

Indicates that the trigger should not be executed when a replication process modifies the table involved in the trigger.
0
 
LVL 6

Expert Comment

by:OlegP
ID: 12064119
I think that make changes is not problem for you.
( Enterprise Manager ->Right button click on table-> All tasks -> Manage triggers -> select Trigger -> ADD 'NOT FOR REPLICATION' befor 'AS' statement-> Apply)
0
 

Author Comment

by:debrafry
ID: 12064264
Hi

my current trigger now looks like this:

CREATE  trigger trgRegisteredContact
on dbo.tblContactPoint
for Insert NOT FOR REPLICATION
as
    declare @ID as uniqueidentifier
    --This selects pkCPID from the inserted table and assigns it to the variable  @ID
    select @id = pkCPID from inserted  
    insert into tblTopic (pkTPID, fkCPID,strTName, strDepartmentID, strOrigin, dtmCreated, dtmModified)
    values (Newid(), @id, 'Software','CS', 'Administrator', GetDate(),GetDate())
      
      update tblContactpoint
      set dtmCreated = Getdate(), dtmModified = Getdate()
      where pkCPID = @ID


However, unfortunately it has not solved the problem, any idea? Thanks
0
 
LVL 6

Expert Comment

by:OlegP
ID: 12065031
are you sure that trigger startup during replication?
try to add some test statment insert into a table(which at not replication plan) to trigger and check.
0
 

Author Comment

by:debrafry
ID: 12065476
Hi
Problem solved, Thank you for your help, I will award points to your first response.  Although I thought I'd changed the trigger that was on the subscriber, it appears that it had reverted to it's previous state without the NOT FOR REPLICATION statement.  
Having amended it again it now works correctly.

Many Thanks
Debs :-)
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
ssms - object execution statistics 12 37
Sharepoint 3.0 migration 4 40
sql server query? 6 26
Access Migration to Sql Server 2 19
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now