May 8, 2012

sp_MScreatedisabledmltrigger (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
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_MScreatedisabledmltrigger(nvarchar @source_object
, nvarchar @source_owner)

MetaData:

 create procedure sys.sp_MScreatedisabledmltrigger  
@source_object sysname,
@source_owner sysname
as
declare @artid uniqueidentifier
declare @cmd nvarchar(1000)
declare @triggername nvarchar(130)
declare @guidstr nchar(32)
declare @retcode int
declare @qualified_tablename nvarchar(517)
declare @qualified_trig_name nvarchar(517)
declare @objid int

--
-- Security Check
--
EXEC @retcode = sys.sp_MSreplcheck_publish
IF @@ERROR <> 0 or @retcode <> 0
return (1)

select @qualified_tablename = quotename(@source_owner) + '.' + quotename(@source_object)

select @objid = object_id(@qualified_tablename)
if @objid is NULL
return 1

select top 1 @artid = artid from dbo.sysmergearticles where objid=@objid
if @artid is NULL
return 1

exec @retcode=sys.sp_MSguidtostr @artid, @guidstr out
if @retcode <> 0 or @@error <> 0
return (1)

-- remove disable dml trigger, if it exists
set @triggername= 'MSmerge_disabledml_' + @guidstr
if @source_owner is not NULL
select @qualified_trig_name = quotename(@source_owner) + '.' + quotename(@triggername)
else
select @qualified_trig_name = quotename(@triggername)

if object_id(@qualified_trig_name, 'TR') is not null
begin
set @cmd= 'drop trigger ' + @qualified_trig_name
exec sys.sp_executesql @cmd
if @@error <> 0
return 1
end

set @cmd='
create trigger '
+ quotename(@triggername) + ' on ' + @qualified_tablename + ' for update, insert, delete
not for replication
as
set nocount on

if @@trancount > 0 rollback tran
raiserror (20092, 16, -1, '
'' + sys.fn_replreplacesinglequote(@qualified_tablename) + ''')

return'


exec sys.sp_executesql @cmd
if @@error <> 0
return 1

-- Only mark the trigger as system object if it is owned by dbo.
if exists (select * from sys.objects where name = @triggername and SCHEMA_NAME(schema_id) = 'dbo')
begin
exec @retcode = sys.sp_MS_marksystemobject @triggername
if @@ERROR<>0 OR @retcode <>0
return 1
end

return 0

No comments:

Post a Comment

Total Pageviews