The meta data is from an SQL 2012 Server.
I have posted alot more, find the whole list here.
Goto Definition or MetaData
Definition:
sys.sp_MSscript_pub_upd_trig(nvarchar @publication, nvarchar @article
, nvarchar @procname
, bit @alter)
MetaData:
create procedure sys.sp_MSscript_pub_upd_trig ( @publication sysname, @article sysname, @procname sysname, @alter bit = 0 -- if 1 script alter, otherwise script create ) as begin declare @cmd nvarchar(4000) declare @qualname nvarchar(517) declare @objid int declare @artid int ,@retcode int set nocount on -- -- security check -- dbo+ to script create or alter -- exec @retcode = sys.sp_MSreplcheck_publish if @@error <> 0 or @retcode <> 0 begin return (1) end -- Create temp table create table #proctext ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null) -- Retrieve underlying table name and replicated columns select @objid = objid, @artid = a.artid from sysarticles a join syspublications p on a.pubid = p.pubid where a.name = @article and p.name = @publication exec sys.sp_MSget_qualified_name @objid, @qualname OUTPUT -- Trigger should be invoked for repl processes as well. select @cmd = case @alter when 0 then N'create' else N'alter' end + ' trigger ' + QUOTENAME(@procname) + N' on ' + @qualname + N' ' select @cmd = @cmd + N'for update not for replication as ' exec sys.sp_MSflush_command @cmd output, 1 insert into #proctext(procedure_text) values(N' ') -- declare common local variables insert into #proctext(procedure_text) values (N'declare @rc int ') insert into #proctext(procedure_text) values(N'select @rc = @@ROWCOUNT ') -- Optimization. Return immediately if no row changed -- This must be at the beginning of the trigger to @@rowcount be overwritten. insert into #proctext(procedure_text) values(N'if @rc = 0 return ') insert into #proctext(procedure_text) values(N'if update (msrepl_tran_version) return ') -- update the version column of all the updated rows all at once. select @cmd = N'update ' + @qualname + N' set msrepl_tran_version = newid() from ' + @qualname + ', inserted ' exec sys.sp_MSflush_command @cmd output, 1 insert into #proctext(procedure_text) values(N' ') exec sys.sp_MSscript_where_clause @objid, @artid, 'version pk', null, 4 insert into #proctext(procedure_text) values(N' ') -- send fragments to client select procedure_text from #proctext order by c1 asc end
No comments:
Post a Comment