Please use FireFox to view this page

CREATE PROCEDURE sp_MSaddmergetriggers @source_table nvarchar(270), /* was type varchar(92), table name */ @table_owner sysname = NULL, @column_tracking int = NULL /* Is column tracking on - default is FALSE */ AS set nocount on declare @command nvarchar(4000) declare @command2 nvarchar(4000) declare @inscommand nvarchar(2000) declare @ifcoltracking nvarchar(255) declare @tablenick int declare @nickname int declare @artid uniqueidentifier declare @guidstr nvarchar(32) declare @owner sysname declare @site sysname declare @db sysname declare @object sysname declare @updtrigname sysname declare @instrigname sysname declare @deltrigname sysname declare @ext nvarchar(10) declare @ext2 nvarchar(10) declare @tablenickchar nvarchar(11) declare @missing_col_count int declare @ccols int declare @ccolchar nvarchar(5) declare @retcode int declare @objid int declare @bitmap varbinary(40) declare @missing_count int declare @viewname sysname declare @tsview sysname declare @sync_objid int declare @command3 nvarchar(4000) declare @notforrepl_str nvarchar(200) declare @notforrepl_bit bit declare @owner_is_admin bit set @notforrepl_bit = 1 set @owner_is_admin = 0 set @bitmap = 0x0 set @missing_count = 0 /* Security check */ EXEC @retcode = dbo.sp_MSreplcheck_subscribe if @@ERROR <> 0 or @retcode <> 0 return(1) -- PARSENAME VARS declare @UnqualName sysname --rightmost name node declare @QualName1 sysname -- END PARSENAME VARS execute @retcode=sp_MSgetreplnick @nickname = @nickname output if @retcode<>0 or @@ERROR<>0 return (1) select @ext = 'ins_' select @ext2 = 'del_' if @table_owner is not NULL select @source_table=QUOTENAME(@table_owner) + '.' + QUOTENAME(@source_table) set @objid = OBJECT_ID(@source_table) -- set up the before image table if one is desired exec dbo.sp_MScreatebeforetable @objid select @ccols = count(*) from syscolumns where id = @objid and iscomputed <> 1 and type_name(xtype) <> 'timestamp' select @missing_col_count=missing_col_count, @sync_objid=sync_objid from sysmergearticles where objid=@objid /* ** increase the # of columns at subscriber side by adding the missing columns ** Note that publisher side also needs to increase this value due to the possible column holes. */ select @ccols = @ccols + @missing_col_count select @ccolchar = convert(nchar, @ccols) select @UnqualName = PARSENAME(@source_table, 1) select @QualName1 = PARSENAME(@source_table, 2) if @UnqualName IS NULL return 1 if @QualName1 is NULL select @QualName1 = user_name(uid) from sysobjects where id = object_id(@UnqualName) -- fixup for variable length differences. remove when vars expanded -- to new SQL SERVER 7.0 lengths select @owner = @QualName1 select @object = @UnqualName --this is to find out if table owner is a system user select @owner_is_admin=sysadmin from master..syslogins l, sysusers u where l.sid=u.sid and u.name=@owner collate database_default execute @retcode=sp_MStablenickname @owner, @object, @tablenick output if @retcode<>0 or @@ERROR<>0 return (1) select @artid = artid from sysmergearticles where objid = @objid /* If column tracking wasn't passed in, just figure it out */ if @column_tracking is null select @column_tracking = column_tracking from sysmergearticles where artid = @artid select @tablenickchar = convert(nchar, @tablenick) exec @retcode=sp_MSguidtostr @artid, @guidstr out if @retcode<>0 or @@ERROR<>0 return (1) -- Check if the triggers can be made NOT FOR REPLICATION if exists (select * from sysmergearticles where nickname = @tablenick and (before_image_objid is not null or before_view_objid is not null or datalength (subset_filterclause) > 1 )) begin select @notforrepl_bit = 0 end else if exists (select * from sysmergesubsetfilters where art_nickname = @tablenick or join_nickname = @tablenick) begin select @notforrepl_bit = 0 end else begin select @notforrepl_bit = 1 end if exists (select * from dbo.sysmergearticles where nickname = @tablenick and published_in_tran_pub = 1) begin select @notforrepl_str = ' if sessionproperty(''replication_agent'') = 1 and (select trigger_nestlevel()) = 1 and master.dbo.fn_isreplmergeagent() = 1 return ' end else begin select @notforrepl_str = ' if sessionproperty(''replication_agent'') = 1 and (select trigger_nestlevel()) = 1 return ' end /* Drop the article's replication triggers if they preexist */ exec dbo.sp_MSdroparticletriggers @source_table, @table_owner if @@ERROR <> 0 return 1 -- owner name removed set @instrigname = @ext + @guidstr set @deltrigname = @ext2 + @guidstr set @updtrigname = 'upd_' + @guidstr set @viewname = 'ctsv_' + @guidstr set @tsview = 'tsvw_' + @guidstr /* Make sure trigger name is unique */ exec @retcode=sp_MSuniqueobjectname @instrigname, @instrigname output if @retcode<>0 or @@ERROR<>0 return (1) exec @retcode=sp_MSuniqueobjectname @deltrigname, @deltrigname output if @retcode<>0 or @@ERROR<>0 return (1) exec @retcode=sp_MSuniqueobjectname @updtrigname, @updtrigname output if @retcode<>0 or @@ERROR<>0 return (1) -- check if there is a before image table. If so create a view from which public can select declare @before_name sysname declare @beforeview sysname select @before_name = OBJECT_NAME(before_image_objid) from sysmergearticles where objid = @objid set @beforeview = 'MSbivw_' + @guidstr if (@before_name is not null) begin /* Create the a view on the before image table if it doesn't already exist. */ /* this view is needed for security purposes since we don't want to grant rights on the before image table to all users */ if not exists (select * from sysobjects where type = 'V' and name = @beforeview) begin exec @retcode=sp_MSuniqueobjectname @beforeview, @beforeview output if @retcode<>0 or @@ERROR<>0 return (1) set @command = 'create view dbo.' + @beforeview + ' as select * from dbo.' + @before_name + ' where trigger_nestlevel(OBJECT_ID(''' + QUOTENAME(REPLACE(@owner, '''', '''''')) + '.' + QUOTENAME(@instrigname) + ''')) > 0 or trigger_nestlevel(OBJECT_ID(''' + QUOTENAME(REPLACE(@owner, '''', '''''')) + '.' + QUOTENAME(@updtrigname) + ''')) > 0 or trigger_nestlevel(OBJECT_ID(''' + QUOTENAME(REPLACE(@owner, '''', '''''')) + '.' + QUOTENAME(@deltrigname) + ''')) > 0 with check option' execute (@command) if @@ERROR<>0 return (1) set @command = 'grant update, insert, select, delete on ' + @beforeview + ' to public' execute (@command) if @@ERROR<>0 return (1) exec sp_MS_marksystemobject @beforeview if @@ERROR<>0 return (1) end end -- Initialize string for inserting to before_image table -- moved this here because it will use the beforeimage table ciew created above. exec sp_MSgetbeforetableinsert @objid, @inscommand output /* Create the view if it doesn't already exist. */ if not exists (select * from sysobjects where type = 'V' and name = @viewname) begin exec @retcode=sp_MSuniqueobjectname @viewname, @viewname output if @retcode<>0 or @@ERROR<>0 return (1) set @command = 'create view dbo.' + @viewname + ' as select * from dbo.MSmerge_contents where trigger_nestlevel(OBJECT_ID(''' + QUOTENAME(REPLACE(@owner, '''', '''''')) + '.' + QUOTENAME(@instrigname) + ''')) > 0 or trigger_nestlevel(OBJECT_ID(''' + QUOTENAME(REPLACE(@owner, '''', '''''')) + '.' + QUOTENAME(@updtrigname) + ''')) > 0 or trigger_nestlevel(OBJECT_ID(''' + QUOTENAME(REPLACE(@owner, '''', '''''')) + '.' + QUOTENAME(@deltrigname) + ''')) > 0 with check option' execute (@command) if @@ERROR<>0 return (1) set @command = 'grant update, insert, select, delete on ' + @viewname + ' to public' execute (@command) if @@ERROR<>0 return (1) exec sp_MS_marksystemobject @viewname if @@ERROR<>0 return (1) end /* Create the view if it doesn't already exist. */ if not exists (select * from sysobjects where type = 'V' and name = @tsview) begin exec @retcode=sp_MSuniqueobjectname @tsview, @tsview output if @retcode<>0 or @@ERROR<>0 return (1) set @command = 'create view dbo. ' + @tsview + ' as select * from dbo.MSmerge_tombstone where trigger_nestlevel(OBJECT_ID(''' + QUOTENAME(REPLACE(@owner, '''', '''''')) + '.' + QUOTENAME(@instrigname) + ''')) > 0 or trigger_nestlevel(OBJECT_ID(''' + QUOTENAME(REPLACE(@owner, '''', '''''')) + '.' + QUOTENAME(@updtrigname) + ''')) > 0 or trigger_nestlevel(OBJECT_ID(''' + QUOTENAME(REPLACE(@owner, '''', '''''')) + '.' + QUOTENAME(@deltrigname) + ''')) > 0 with check option' execute (@command) if @@ERROR<>0 return (1) set @command = 'grant update, insert, select, delete on ' + @tsview + ' to public' execute (@command) if @@ERROR<>0 return (1) exec sp_MS_marksystemobject @tsview if @@ERROR<>0 return (1) end /* If column tracking is on, construct the string to initialize colv's */ if (@column_tracking <> 0) select @ifcoltracking = ' set @colv1 = { fn INITCOLVS(@ccols, @nickname) }' else select @ifcoltracking = ' set @colv1 = NULL' /* UNDONE maybe remove null guid checks in SQL SERVER 7.0 */ select @command = 'create trigger ' + @instrigname + ' on ' + @source_table + ' for insert as ' + @notforrepl_str + ' /* Declare variables */ declare @article_rows_inserted int select @article_rows_inserted = count(*) from inserted declare @tablenick int, @nickname int declare @lineage varbinary(255), @colv1 varbinary(2048) declare @ccols int, @retcode smallint, @version int, @curversion int, @oldmaxversion int set nocount on set @tablenick = ' + @tablenickchar + ' select @ccols = ' + @ccolchar + ' set @lineage = 0x0 set @retcode = 0 select @oldmaxversion= maxversion_at_cleanup from dbo.sysmergearticles where nickname = @tablenick execute dbo.sp_MSgetreplnick @nickname = @nickname output if (@@error <> 0) begin goto FAILURE end set @lineage = { fn UPDATELINEAGE (0x0, @nickname, 1) } ' + @ifcoltracking + ' if (@@error <> 0) begin goto FAILURE end if exists (select ts.rowguid from ' + @tsview + ' ts, inserted i where ts.tablenick = @tablenick and ts.rowguid = i.rowguidcol) begin select @version = max({fn GETMAXVERSION(lineage)}) from ' + @tsview + ' where tablenick = @tablenick and rowguid in (select rowguidcol from inserted) if @version is not null begin -- reset lineage and colv to higher version... set @curversion = 0 while (@curversion <= @version) begin set @lineage = { fn UPDATELINEAGE (@lineage, @nickname, @oldmaxversion+1) } set @curversion = @curversion + 1 end if (@colv1 IS NOT NULL) set @colv1 = { fn UPDATECOLVBM(@colv1, @nickname, 0x01, 0x00, { fn GETMAXVERSION(@lineage) }) } delete from ' + @tsview + ' where tablenick = @tablenick and rowguid in (select rowguidcol from inserted) end end if (@article_rows_inserted = 1) begin if not exists (select ct.rowguid from ' + @viewname + ' ct, inserted i where ct.tablenick = @tablenick and ct.rowguid = i.rowguidcol) begin insert into ' + @viewname + ' (tablenick, rowguid, lineage, colv1, generation, joinchangegen) select @tablenick, rowguidcol, @lineage, @colv1, A.gen_cur, A.gen_cur from inserted, (select top 1 nickname, gen_cur = isnull(gen_cur, 0) from dbo.sysmergearticles where nickname = @tablenick) as A end end else begin insert into ' + @viewname + ' (tablenick, rowguid, lineage, colv1, generation, joinchangegen) select @tablenick, rowguidcol, @lineage, @colv1, A.gen_cur, A.gen_cur from inserted, (select top 1 nickname, gen_cur = isnull(gen_cur, 0) from dbo.sysmergearticles where nickname = @tablenick) as A where rowguidcol not in (select rowguid from ' + @viewname + ' where tablenick = @tablenick) end if @@error <> 0 goto FAILURE return FAILURE: if @@trancount > 0 rollback tran raiserror (20041, 16, -1) return ' execute (@command) if @@ERROR <> 0 begin raiserror(21304, 16, -1, @source_table) return (1) end select @command3 = @owner + '.' + @instrigname if @owner_is_admin = 1 begin exec sp_MS_marksystemobject @command3 if @@ERROR <> 0 begin raiserror(21304, 16, -1, @source_table) return (1) end end /* Call separate routine to add update trigger */ exec @retcode=sp_MSaddupdatetrigger @source_table, @owner, @object, @artid, @column_tracking, @viewname if @retcode<>0 or @@ERROR<>0 return (1) /* Now make the delete trigger */ -- NOTE: owner name removed /* Make sure trigger name is unique */ exec @retcode = dbo.sp_MSuniqueobjectname @deltrigname, @deltrigname output if @retcode<>0 or @@ERROR<>0 return (1) set @command = 'create trigger ' + @deltrigname + ' on ' + @source_table + ' FOR DELETE AS ' if (@notforrepl_bit = 1) select @command = @command + @notforrepl_str select @command = @command + ' /* Declare variables */ declare @tablenick int, @retcode smallint, @reason nvarchar(255), @nickname int, @lineage varbinary(255), @oldmaxversion int set nocount on select @tablenick = ' + @tablenickchar + ' select @oldmaxversion= maxversion_at_cleanup from dbo.sysmergearticles where nickname = @tablenick select @reason = ''user delete'' execute dbo.sp_MSgetreplnick @nickname = @nickname output if (@@error <> 0) begin goto FAILURE end ' set @command2 = ' set @lineage = { fn UPDATELINEAGE(0x0, @nickname, @oldmaxversion) } insert into ' + @tsview + ' (rowguid, tablenick, type, lineage, generation, reason) select rowguidcol, @tablenick, 1, { fn UPDATELINEAGE(COALESCE(c.lineage, @lineage), @nickname, @oldmaxversion+1) }, a.gen_cur, @reason from deleted d left outer join ' + @viewname + ' c on c.tablenick = @tablenick and c.rowguid = d.rowguidcol join (select top 1 nickname, gen_cur = isnull(gen_cur, 0) from dbo.sysmergearticles where nickname = @tablenick) as a on a.nickname = @tablenick if @@error <> 0 GOTO FAILURE delete from ' + @viewname + ' where tablenick = @tablenick and rowguid in (select rowguidcol from deleted) if @@error <> 0 GOTO FAILURE return FAILURE: if @@trancount > 0 rollback tran raiserror (20041, 16, -1) return ' execute (@command + @inscommand + @command2) if @@ERROR <> 0 begin raiserror(21304, 16, -1, @source_table) return (1) end select @command3 = @owner + '.' + @deltrigname if @owner_is_admin = 1 begin exec sp_MS_marksystemobject @command3 if @@ERROR <> 0 begin raiserror(21304, 16, -1, @source_table) return (1) end end return (0)