Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSscript_singlerow_trigger

  No additional text.


Syntax
create procedure sys.sp_MSscript_singlerow_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 @colname sysname
                ,@spacer nvarchar(1)
                ,@ccoltype sysname
                ,@this_col int
                ,@rc int
                ,@cmd nvarchar(4000)

    
    -- script update checks
    
    if @op_type in ('ins', 'upd')
        exec sys.sp_MSscript_trigger_update_checks @objid, @identity_col, @ts_col, @op_type, 0
    
    -- continue scripting
    
    select @cmd = N'
    ' + N'
    ' + N'-- Are we doing single or multi row trigger update
    ' + N'
    if (@rc = 1)
    begin
        ' + N'
        ' + N'-- single row trigger update
        ' + N'-- '
    insert into #proctext(procedure_text) values(@cmd)
    
    -- script the trigger assignments
    
    if @op_type in ('ins', 'upd')
    begin
        select @cmd = N'
        select
'
        insert into #proctext(procedure_text) values(@cmd)
        exec sys.sp_MSscript_trigger_assignment @objid, null, 8, @ts_col, @op_type, 1, @primary_key_bitmap
        select @cmd = N'
        from inserted '
        insert into #proctext(procedure_text) values(@cmd)
    end
    if (@cftproc is null)
    begin
        
        -- For immediate updating cases
        -- we need to cache the inserted values for identity operations
        
        if @op_type in ('ins')
        begin
            select @cmd = N'
        select
'
            insert into #proctext(procedure_text) values(@cmd)
            exec sys.sp_MSscript_trigger_assignment @objid, '_old', 8, @ts_col, @op_type, 0, @primary_key_bitmap
            select @cmd = N'
        from inserted '
            insert into #proctext(procedure_text) values(@cmd)
        end
    end
    if @op_type in ('upd', 'del')
    begin
        select @cmd = N'
        select
'
        insert into #proctext(procedure_text) values(@cmd)
        exec sys.sp_MSscript_trigger_assignment @objid, '_old', 8, @ts_col, @op_type, 0, @primary_key_bitmap
        select @cmd = N'
        from deleted '
        insert into #proctext(procedure_text) values(@cmd)
    end
    
    -- script the execution logic
    
    exec sys.sp_MSscript_trigger_exec_rpc @publisher, @publisher_db, @publication, @procname, @proc_owner,
        @cftproc, @objid, @op_type, 12, @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
    
    -- script the end (for single row change)
    
    select @cmd = N'
    end -- end of single 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