June 14, 2003 at 5:23 am
Hi,
I have a scenario where i have triggers on tables that are on merge replication.
server1
ins_Trigger on Table 1 updates Table2
server2
ins_Trigger on Table 1 updates Table2
Table1 and Table2 on Server 1 & 2 are on merge replication.
In this scenario, all the data is getting duplicated b'cos of trigger getting fired for replicated data also. is there any way to set the trigger so that it doesn't fire for replication.
Thanks
June 14, 2003 at 5:37 am
There is an option NOT FOR REPLICATION which you can use when you create the trigger.
Never used it myself, hence best to read BOL
see the t-sql reference for 'CREATE TRIGGER'
Regards
Steven
Steven
June 15, 2003 at 10:28 pm
thanks steven,
what is the easiest way to change all my 100+ triggers and add the "not for replication option"?
June 16, 2003 at 4:15 am
Probably DMO. You can loop through each trigger, process triggers that match your pattern, do the alter.
Andy
June 19, 2003 at 3:38 pm
Ouch... Well here is the method I use to alter triggers in bulk...
-- Add the triggers to a working table
IF NOT object_id('tempdb..#DisabledTriggers') IS NULL
DROP TABLE #DisabledTriggers
SELECT so.name TriggerName, sot.name TableName, sc.text TriggerSql INTO #DisabledTriggers
FROM syscomments sc
JOIN sysobjects so on sc.id = so.id
JOIN sysobjects sot on so.parent_obj = sot.id
WHERE so.xtype = 'TR' and so.status >=0
ORDER BY so.name, sc.number, sc.colid
-- Create a cursor to loop through the scripts
DECLARE @cTrigs cursor
, @sql varchar(4000)
SET @cTrigs = cursor for
SELECT TriggerSql
FROM #DisabledTriggers
FOR READ ONLY
OPEN @cTrigs
FETCH @cTrigs INTO @sql
WHILE @@FETCH_STATUS = 0
BEGIN
-- replace the create trigger with alter trigger. NOTE: This may need to be adjusted for
-- how many spaces are between create and trigger.
SET @sql = REPLACE(@SQL,'CREATE TRIGGER', 'ALTER TRIGGER')
-- add in the NOT FOR REPLICATION string
SET @sql = REPLACE(@SQL, ' AS ', ' NOT FOR REPLICATION AS ')
-- Print out this line of the trigger
PRINT @sql
PRINT 'GO'
-- If you are brave and you know your trigger code is less than 8000 characters you can go ahead and execute it.
-- EXEC sp_executesql @sql
FETCH @cTrigs INTO @sql
END
CLOSE @cTrigs
DEALLOCATE @cTrigs
Hope this helps! 🙂
Gary Johnson
Gary Johnson
DBA
Sr. DB Engineer
Edited by - gljjr on 06/19/2003 3:42:21 PM
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply