Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSscript_begintrig1

  No additional text.


Syntax
create procedure sys.sp_MSscript_begintrig1
(
    @publisher   sysname
    ,@publisher_db sysname
    ,@publication sysname
    ,@trigname      sysname
    ,@objid         int
    ,@procname      sysname
    ,@filter_clause nvarchar(4000)
    ,@op_type       char(3) = 'ins' -- ins, upd, del
    ,@fisqueued      bit = 0 -- 1 = Queued subscription
    ,@falter    bit = 0 -- if 1 script alter, otherwise script create
    ,@agent_id	int = NULL  	-- null @agent_id means this is executed at pub side,
    										-- pub queries sysarticlecolumns for article column partition,
    										-- sub uses MSsubscription_articlecolumns
)
as
BEGIN
    declare @cmd       nvarchar(4000)
                ,@start     int
                ,@sub_len   int
                ,@subtableowner sysname
                ,@qualname  nvarchar(517)
                ,@thisspname sysname
                ,@pubsecuritymode int
                ,@updatemode int
                ,@contextuser sysname

    select @thisspname = N'sp_MSscript_begintrig1'
            ,@subtableowner = schema_name(objectproperty(@objid, N'SchemaId'))
    exec sys.sp_MSget_qualified_name @objid, @qualname OUTPUT
    
    -- check for exisitng security mode
    
    select @contextuser = case when (sp.publisher_security_mode in (0,1) and sp.publisherlinkuser = N'xxxx')
                                            then N'repllinkproxy' else sp.publisherlinkuser end
            ,@pubsecuritymode = sp.publisher_security_mode
            ,@updatemode = sa.update_mode
    from MSsubscription_properties as sp join MSsubscription_agents as sa
        on upper(sp.publisher) = upper(sa.publisher)
            and sp.publisher_db = sa.publisher_db
            and sp.publication = sa.publication
    where upper(sa.publisher) = upper(@publisher)
        and sa.publisher_db =  @publisher_db
        and sa.publication = @publication
    
    -- construct qualified trigger name
    
    select @cmd = case when @falter = 0 then N'create' else N'alter' end
    select @cmd = @cmd + N' trigger ' + QUOTENAME(@subtableowner) + N'.' + QUOTENAME(@trigname) + N' on ' + @qualname
    
    -- if we have immediate updating, publisher link security mode 0,
    -- we might have to execute in a particular context
    
    if (@updatemode in (1,3,5)) and @pubsecuritymode = 0 and @contextuser is not null
    begin
        
        -- the context user should not be a sysadmin
        
        if exists (select * from sys.database_principals as dp join master.dbo.syslogins as s on dp.sid = s.sid
                        where dp.name = @contextuser and (ISNULL(IS_SRVROLEMEMBER('sysadmin',s.name),0) != 1))
        begin
            select @cmd = @cmd + N' with execute as N''' + sys.fn_replreplacesinglequote(@contextuser) + N''' for '
        end
        else
        begin
            select @cmd = @cmd + N' for '
        end
    end
    else
    begin
        select @cmd = @cmd + N' for '
    end
    
    -- continue scripting
    
    select @cmd = case
        when (@op_type = 'ins') then @cmd + N'insert '
        when (@op_type = 'upd') then @cmd + N'update '
        when (@op_type = 'del') then @cmd + N'delete '
    end
    select @cmd = @cmd + N'not for replication
as
'
    insert into #proctext(procedure_text) values (@cmd)

    
    -- declare common local variables
    
    --        ,@update_mode char(40)
    --        ,@failover_mode char(10)
    
    insert into #proctext(procedure_text) values (N'
    declare @rc int
                ,@retcode int
                ,@connect_string nvarchar(300)
                ,@islocalpublisher bit
                ,@rpc_proc nvarchar(500)
                ,@update_mode_id int
                ,@bitmap varbinary(4000)
                ,@version_guid uniqueidentifier
                ,@trigger_op char(10) ')

    
    -- queued specific declarations
  
    insert into #proctext(procedure_text) values (N'
                ,@failover_mode_id int
                ,@queue_server sysname
                ,@queue_id sysname
                ,@tran_id varchar(255)
                ,@subscriber sysname
                ,@subscriber_db sysname
                ,@partial_cmd bit
                ,@start_offset int
                ,@end_offset int
                ,@vb_buffer varbinary(8000)
                ,@vb_bufferlen int
            ')

    -- script variables used to retrieve data from inserted table
    if @op_type in ('ins', 'upd')
    begin
        insert into #proctext(procedure_text) values(N'
    declare ')
        exec sys.sp_MSscript_params @objid, null, null, 0, null, @agent_id
    end
    
    -- declare a second set of vars for immediate updating
    -- to handle legacy identity and timestamp behavior
    
    if (@op_type = 'ins' and @fisqueued = 0) or @op_type in ('del', 'upd')
    begin
        insert into #proctext(procedure_text) values(N'
    declare ')
        exec sys.sp_MSscript_params @objid, null, '_old',  0, null, @agent_id
    end
    -- Set @rc, @subscriber and @subscriber_db
    -- Optimization. Return immediately if no row changed
    select @cmd = N'

    select @rc = @@rowcount
            ,@subscriber = @@servername
            ,@subscriber_db = db_name()
            ,@version_guid = newid()
    if @rc = 0
        return
    set nocount on '
    insert into #proctext(procedure_text) values(@cmd)
    
    -- set column update bitmap for update trigger
    
    if (@op_type = 'upd')
    begin
        declare     @num_bytes        int
                    ,@i_byte            int
                    ,@i_bit            tinyint
                    ,@len_before nvarchar(10)
                    ,@len_after nvarchar(10)
                    ,@index1 nvarchar(10)
                    ,@index2 nvarchar(10)
                    ,@this_col         int
                    ,@max_col            int
                    ,@total_col        int

        
        -- check if the subscriber table was altered for column changes
        
        select @max_col = max(column_id)
                ,@total_col = count(column_id)
        from sys.columns where object_id = @objid
        if (@total_col = @max_col)
        begin
            
            -- actual colid and relative column order are same
            
            insert into #proctext(procedure_text) values(N'
    select @bitmap = columns_updated()
    ')
        end
        else
        begin
            
            -- we need to convert the columns_updated bitmap to
            -- a bitmap that contains relative column information
            
            select @cmd = N'
    select @bitmap = sys.fn_repladjustcolumnmap( ' + cast(@objid as sysname) + N', ' +
                cast(@total_col as sysname) + N', columns_updated())'
            insert into #proctext(procedure_text) values(@cmd)
            select @cmd = N'
    if (@bitmap is null)
    begin
        raiserror(21499, 16, 2, ''fn_repladjustcolumnmap'', ''create'', ''column mapping'', @@error)
        goto FAILURE
    end
    '
            insert into #proctext(procedure_text) values(@cmd)
        end
        
        -- Mark the version bit in the bitmap as updated.
        
        select @cmd = N'
    ' + '-- set the bit for msrepl_tran_version'
        insert into #proctext(procedure_text) values(@cmd)
        
        -- get actual column id
        
        exec sys.sp_MSget_col_position @objid, null, 'msrepl_tran_version', @this_col = @this_col output, @agent_id = @agent_id
        if (@total_col < @max_col)
        begin
            
            -- this table has altered columns, create a mapping between
            -- relative column position and actual column id
            
 declare @colmap table (relativeorder int identity(1,1), colid int)
            insert into @colmap (colid)
            select column_id from sys.columns where object_id = @objid order by column_id
            if (@@error != 0)
            begin
                raiserror(21499, 16, 3, @thisspname, 'populate', '@colmap', @@error)
                return (1)
            end
            
            -- get the relative column position for msrepl_tran_version
            
            select @this_col = relativeorder from @colmap where colid = @this_col
        end
        
        -- script the bitmap computation
        
        select @num_bytes = @total_col / 8 + 1
                ,@i_byte = 1 + (@this_col-1) / 8
                ,@i_bit  = power(2, (@this_col-1) % 8 )

        select @len_before = convert(nvarchar(10), @i_byte -1)
                ,@index1 = convert(nvarchar(10), @i_byte)
                ,@index2 = convert(nvarchar(10), @i_byte + 1)
                ,@len_after = convert(nvarchar(10), @num_bytes - @i_byte)

        select @cmd = N'
    select @bitmap = substring(@bitmap, 1, ' + @len_before +
            ') + (convert(binary(1), substring(@bitmap, ' + @index1 +
            ', 1) | convert(tinyint,' + convert(nvarchar(10), @i_bit) +
            '))) + substring(@bitmap, ' + @index2 +
            ', ' + @len_after +
            ') '
        exec sys.sp_MSflush_command @cmd, 1
    end -- @op_type = 'upd'
    
    -- Partition check statement
    
    if @filter_clause IS NOT NULL
    begin
        declare @retcode int
        exec @retcode = sys.sp_MSsubst_filter_names NULL, N'inserted', @filter_clause output
        if @retcode <> 0 or @@error <> 0
            return 1

        select @cmd = N'
    if exists (select * from inserted where not ('
        insert into #proctext(procedure_text) values(@cmd)
        
        -- break filter_clause into chunks of 255
        
        select @start = 1
        while @start < len(@filter_clause)
        begin
            select @sub_len = case when ( len(@filter_clause) < 255) then len(@filter_clause) else 255 end
            select @cmd = substring(@filter_clause, @start, @sub_len)
            exec sys.sp_MSflush_command @cmd output, 1
            select @start = @start + @sub_len
        end
        select @cmd = N'))
    begin
        exec sys.sp_MSreplraiserror 21034
        goto FAILURE
    end '
        insert into #proctext(procedure_text) values(@cmd)
    end
    
    -- trigger nesting checks
    
    if (@op_type = 'upd')
    begin
        select @cmd = N'
    ' + '-- trigger nesting check
    ' + '-- error = -1, nested call = 1, not a nested call = 0
    exec @retcode = sys.sp_check_sync_trigger @@procid, @trigger_op OUTPUT, N'''
    + sys.fn_replreplacesinglequote(@subtableowner) collate database_default + '''
    if (@retcode = -1)
        goto FAILURE
    if (@retcode = 1)
        return '
        insert into #proctext(procedure_text) values(@cmd)
    end
END

 
Last revision 2008RTM
See also

  sp_MSscript_sync_del_trig (Procedure)
sp_MSscript_sync_ins_trig (Procedure)
sp_MSscript_sync_upd_trig (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash