Triggers and Replication

Question

Triggers and Replication

Svetyha

SSC Enthusiast

Points: 139
More actions
October 30, 2002 at 8:54 am

#80051

Hi!
I have setup transactional replication between two SQL Server 2000 servers. I have configured a push subscription from the publisher db.
Replication works correctly. But I have a problem with firing triggers on subscriber's table (triggers don't fire).
Does anybody know a way to fire triggers during replication process?
I appreciate your help.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Congruent Influence Default port Points: 1416 More actions · Answer 1

I am interpreting your post as you need a trigger to fire that doesn't exist on the publisher table, but only on the subscriber table to perform some action that is only relative to the subscriber. I have used triggers on subscriber tables in the traditional way that you do on a table. Have you checked to see if you are looking for the insert or update or delete accurately for the trigger to fire?

Remember this when a developer tells you it will just be temporary. Temporary = Permanent.

jmadren SSCrazy Points: 2055 More actions · Answer 2

Check if you have the "NOT FOR REPLICATION" option set for the trigger. If this is set, then the trigger will not fire when the replication agent inserts/updates/deletes rows.

Jay Madren

Svetyha SSC Enthusiast Points: 139 More actions · Answer 3

I don't have the "NOT FOR REPLICATION" option set for the trigger. Here is the code of the trigger that I have on the subscribe's table:

---begin code

CREATE TRIGGER [TR_INSERT] ON [PF].[RetailOffice_Ref]

FOR INSERT

AS

if exists (select * from dbo.sysobjects where id = object_id(N'[AC].[Objects]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

begin

declare @parentGUID [uniqueidentifier]

select @parentGUID = ObjectGUID from [AC].[Objects] where O_Name = 'Ñáûòîâûå îðãàíèçàöèè' and ParentGUID = '{00000000-0000-0000-0000-000000000000}'

if @parentGUID is null

begin

set @parentGUID = newid()

insert into [AC].[Objects] ( ObjectGUID, O_Name, O_Description, ParentGUID )

values (@parentGUID, 'Ñáûòîâûå îðãàíèçàöèè', 'Ñáûòîâûå îðãàíèçàöèè', '{00000000-0000-0000-0000-000000000000}')

end

insert into [AC].[Objects] ( ObjectGUID, O_Name, O_Description, ParentGUID )

select ins.RetailOfficeGUID, ins.RO_Name, ins.RO_Name, @parentGUID

from inserted ins

end

---end code

This trigger doesn't fire during the replication process. Do you have any idea?

Thanks.

Antares686 SSC Guru Points: 125444 More actions · Answer 4

Have you verified wih POrofiler on the Subscriber that the trigger is not firing but not meeting the qualifications you need to see or that you aren't reaching the maximum trgger nesting level of 32 inadvertently?

Has the data changed and if so then was it possibly an update instead of an INSERT which you are only programmed for?

Svetyha SSC Enthusiast Points: 139 More actions · Answer 5

Hi!

I don't have the NOT FOR REPLICATION option set in my trigger.

I am so sorry but it was my fault in problem's discription. In actual fact the trigger wasn't been fired during applying the initial snapshot only. And I found out from the documentation that foreing key constraints? check constraints, and triggers will be disabled during the concurrent snapshot generation and will be enabled after the snapshot is generated.

Thanks for your time.