Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSscript_begintrig2

  No additional text.


Syntax
create procedure sys.sp_MSscript_begintrig2
(
    @publisher   sysname
    ,@publisher_db sysname
    ,@publication sysname
    ,@objid       int
    ,@op_type     char(3) = 'ins' -- ins, upd, del
    ,@agent_id    int
    ,@fisqueued      bit = 0 -- 1 = Queued subscription
)
as
BEGIN
    set nocount on
    declare @cmd nvarchar(4000)
                ,@queue_server sysname

    if @op_type = 'ins'
    begin
        insert into #proctext(procedure_text) values(N'
    if Objectproperty(@@procid,''TriggerInsertOrder'') != 1
    begin
        declare @trigname sysname
        select @trigname = object_name(@@procid)

        raiserror (21128, 16, 1, @trigname)
        goto FAILURE
    end ')
    end
    else if @op_type = 'upd'
    begin
        insert into #proctext(procedure_text) values(N'
    if Objectproperty(@@procid,''TriggerUpdateOrder'') != 1
    begin
        declare @trigname sysname
        select @trigname = object_name(@@procid)

        raiserror (21129, 16, 1, @trigname)
        goto FAILURE
    end ')
    end
    else if @op_type = 'del'
    begin
        insert into #proctext(procedure_text) values(N'
    if Objectproperty(@@procid,''TriggerDeleteOrder'') != 1
    begin
        declare @trigname sysname
        select @trigname = object_name(@@procid)

        raiserror (21130, 16, 1, @trigname)
        goto FAILURE
    end ')
    end
    
    -- scripting for getting values from MSsubscription_agents
    
    select @cmd = N'
    select @update_mode_id = update_mode
            ,@queue_server = queue_server
            ,@queue_id = queue_id
            ,@failover_mode_id = failover_mode
    from dbo.MSsubscription_agents where id = ' + convert(nvarchar(10), @agent_id) + N' '
    insert into #proctext(procedure_text) values (@cmd)
    
    -- continue with scripting
    
    select @cmd = N'
    ' + N'
    ' + N'-- initialize and validate based on update mode
    ' + N'
    if (@update_mode_id = 1 or (@update_mode_id in (3,5) and @failover_mode_id = 0))
    begin
        exec @retcode = sys.sp_getpublisherlink @@procid, @connect_string output, @islocalpublisher output
        if @retcode <>0 or @@error <> 0
            goto FAILURE
    end'
    insert into #proctext(procedure_text) values (@cmd)
    
    -- continue with scripting
    
    if (@fisqueued = 1)
    begin
        
        -- Queued specific scripting
        
        select @cmd = N'
    else if (@update_mode_id in (2,4) or (@update_mode_id in (3,5) and @failover_mode_id = 1))
    begin
        if (@queue_id is NULL)
            goto FAILURE
    end'
        insert into #proctext(procedure_text) values (@cmd)
    end
    
    -- continue with scripting
    
    select @cmd = N'
    else if @update_mode_id = 0
    begin
        raiserror (21757, 16, 1)
        goto FAILURE
    end
    else
    begin
        raiserror(21561, 16, 1, ''update_mode'', @update_mode_id)
        goto FAILURE
    end '
    insert into #proctext(procedure_text) values (@cmd)
    
    -- continue with queued specific scripting
    
    if (@fisqueued = 1)
    begin
        select @cmd = N'
    ' + N'
    ' + N'-- set queue prefix for MSMQ cases
    ' + N'
    if (@update_mode_id in (2,3))
    begin
        select @queue_id = N''DIRECT=OS:'' + @queue_server + N''\PRIVATE$\'' + @queue_id
    end'
        insert into #proctext(procedure_text) values (@cmd)
    end
    
    -- Script initiation of distributed transaction for queued/remote immediate updating
    
    select @cmd = N'
    ' + N'
    ' + N'-- Decide if we should start a distributed transaction or local transaction
    ' + N'-- based on update mode, failover state and islocalpublisher
    ' + N'-- '
    insert into #proctext(procedure_text) values (@cmd)
    if (@fisqueued = 1)
    begin
        select @cmd = N'
if (@update_mode_id = 1 and @islocalpublisher = 1)
        or (@update_mode_id = 3 and @failover_mode_id = 0 and @islocalpublisher = 1)
        or (@update_mode_id = 4)
        or (@update_mode_id = 5 and @failover_mode_id = 0 and @islocalpublisher = 1)
        or (@update_mode_id = 5 and @failover_mode_id = 1) '
        insert into #proctext(procedure_text) values (@cmd)
    end
    else
    begin
        select @cmd = N'
    if (@update_mode_id = 1 and @islocalpublisher = 1) '
        insert into #proctext(procedure_text) values (@cmd)
    end
    
    -- continue scripting
    
    select @cmd = N'
    begin
        BEGIN TRAN
    end
    else
    begin
        SET XACT_ABORT ON
        BEGIN DISTRIBUTED TRAN
    end '
    insert into #proctext(procedure_text) values (@cmd)
    
    -- continue scripting
    
    if (@fisqueued = 1)
    begin
        select @cmd = N'
    ' + N'
    ' + N'-- save the transaction token for later use
    ' + N'
    exec sys.sp_getbindtoken @out_token = @tran_id OUTPUT , @for_xp_flag = 1 '
        insert into #proctext(procedure_text) values (@cmd)
    end
    
    -- all done with scripting
    
    return 0
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