Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSscript_multirow_trigger

  No additional text.


Syntax
create procedure sys.sp_MSscript_multirow_trigger
(
    @objid        int,
    @publisher    sysname,
    @publisher_db sysname,
    @publication  sysname,
    @procname     sysname,
    @proc_owner      sysname,
    @cftproc      sysname,
    @identity_col  sysname,
    @ts_col        sysname,
    @op_type char(3) = 'ins', -- 'ins, 'upd', 'del'
    @primary_key_bitmap varbinary(4000) = NULL,
    @pubversion int
)
as
BEGIN
    set nocount on
    declare @cmd  nvarchar(4000)
                ,@fisqueued bit

    select @fisqueued = case when (@cftproc is null) then 0 else 1 end
    
    -- start scripting
    
    select @cmd = N'
    else
    begin
        ' + N'
        ' + N'-- start of multirow row trigger update
        ' + N'-- '
    insert into #proctext(procedure_text) values(@cmd)
    
    -- setup cursor over inserted table for ins & upd triggers
    
    if @op_type in ('ins', 'upd')
    begin
        select @cmd = N'
        declare rpl_ins_cursor CURSOR LOCAL FAST_FORWARD FOR select
'
        insert into #proctext(procedure_text) values(@cmd)

        exec sys.sp_MSscript_trigger_variables @objid, null, null, 8, ' ',
            0,@identity_col, @ts_col,0, 1, @op_type, 1, @primary_key_bitmap

        select @cmd = N'
            from inserted for read only
        open rpl_ins_cursor
'
        insert into #proctext(procedure_text) values(@cmd)
    end
    if (@fisqueued = 0)
    begin
        
        -- Immediate insert trigger - cache the inserted values for identity operation
        
        if @op_type in ('ins')
        begin
            select @cmd = N'
        declare rpl_ins2_cursor CURSOR LOCAL FAST_FORWARD FOR select
'
            insert into #proctext(procedure_text) values(@cmd)
            exec sys.sp_MSscript_trigger_variables @objid, null, null, 8, ' '
            select @cmd = N'
            from inserted for read only
        open rpl_ins2_cursor
'
            insert into #proctext(procedure_text) values(@cmd)
        end
    end
    
    -- setup cursor over deleted table for  upd & del triggers
    
    if @op_type in ('upd', 'del')
    begin
        select @cmd = N'
        declare rpl_del_cursor CURSOR LOCAL FAST_FORWARD FOR select
'
        insert into #proctext(procedure_text) values(@cmd)

        exec sys.sp_MSscript_trigger_variables @objid, null, null, 8, ' ',
            0,@identity_col, @ts_col,0, 1,  @op_type, 0, @primary_key_bitmap

        select @cmd = N'
            from deleted for read only
        open rpl_del_cursor
'
        insert into #proctext(procedure_text) values(@cmd)
    end
    
    -- script fetch statement
    
    exec sys.sp_MSscript_trigger_fetch_statement @objid, @op_type, 8, @fisqueued
    
    -- start the fetch loop
    
    select @cmd = N'
        while (@@fetch_status != -1)
        begin '
    insert into #proctext(procedure_text) values(@cmd)
    
    -- script the execution logic
    
    exec sys.sp_MSscript_trigger_exec_rpc @publisher, @publisher_db, @publication, @procname, @proc_owner,
        @cftproc, @objid, @op_type, 16, @identity_col, @ts_col, @primary_key_bitmap, @pubversion
    
    -- script any updates to be done
    
    if @op_type in ('ins', 'upd')
        exec sys.sp_MSscript_trigger_updates @identity_col, @ts_col, @op_type, @objid, 8, @primary_key_bitmap
    
    -- do the next fetch
    
    insert into #proctext(procedure_text) values(N'
')
    exec sys.sp_MSscript_trigger_fetch_statement @objid, @op_type, 12, @fisqueued
    
    -- release the cursors
    
    select @cmd = N'
        end  -- cursor while loop'
    insert into #proctext(procedure_text) values(@cmd)

    if @op_type in ('ins', 'upd')
    begin
        select @cmd = N'
        close rpl_ins_cursor
        deallocate rpl_ins_cursor '
        insert into #proctext(procedure_text) values(@cmd)
    end
    if (@fisqueued = 0)
    begin
        if @op_type = 'ins'
        begin
            select @cmd = N'
        close rpl_ins2_cursor
        deallocate rpl_ins2_cursor '
            insert into #proctext(procedure_text) values(@cmd)
        end
    end
    if @op_type in ('upd', 'del')
    begin
        select @cmd = N'
        close rpl_del_cursor
        deallocate rpl_del_cursor '
        insert into #proctext(procedure_text) values(@cmd)
    end
    
    -- script the end (for single row change)
    
    select @cmd = N'
    end -- end of multi row trigger update '
    insert into #proctext(procedure_text) values(@cmd)
    
    -- all done
    
    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