Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSscript_trigger_assignment

  No additional text.


Syntax
create procedure sys.sp_MSscript_trigger_assignment (
    @objid int
    ,@postfix char(4) = null
    ,@indent int = 0
    ,@ts_col sysname
    ,@op_type char(3) -- 'ins, 'upd', 'del'
    ,@is_new    bit
    ,@primary_key_bitmap varbinary(4000) = null
)
as
BEGIN
    set nocount on
    declare @cmd          nvarchar(4000)
            ,@colname      sysname
            ,@spacer       nvarchar(1)
            ,@ccoltype     sysname
            ,@src_cols     int
            ,@this_col     int
            ,@total_col        int
            ,@indkey            int
            ,@fcreatedcolmap    bit
            ,@rc           int
            ,@column          nvarchar(4000)
            ,@thisspname sysname
    declare @colmap table (relativeorder int identity(1,1), colid int)

    -- initialize
    select @spacer = N' '
            ,@indkey = 1
            ,@thisspname = N'sp_MSscript_trigger_assignment'
            ,@fcreatedcolmap = 0
        select @src_cols = max(column_id)
                ,@total_col = count(column_id)
        from sys.columns where object_id = @objid
        exec sys.sp_MSpad_command @cmd output, @indent
        
        -- check if column Id match relative column order
        -- for trigger scripting
        
        if (@total_col < @src_cols)
        begin
            
            -- this table may have altered columns, so when we need to
            -- set a mapping for using the bitmaps properly as the bitmap
            -- always refers relative column order
            
            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, 5, @thisspname, 'populate', '@colmap', @@error)
                return (1)
            end
            select @fcreatedcolmap = 1
        end

        while (@indkey <= @total_col)
        begin
            
            -- set the actual column id for this relative order in the bitmap if necessary
            
            if (@fcreatedcolmap = 1)
            begin
                select @this_col = colid from @colmap
                where relativeorder = @indkey
            end
            else
            begin
                select @this_col = @indkey
            end
            
            -- Get column name
            
            exec @rc = sys.sp_MSget_colinfo @objid, @this_col, null, 0, @colname output, @ccoltype output
            if @rc = 0  and EXISTS (select name from sys.columns where object_id=@objid and column_id=@this_col and is_computed<>1)
            begin
                -- Optimization:
                -- Get null or actual column name
                -- Note: the output is quoted.
                exec sys.sp_MSget_synctran_column
                    @ts_col = @ts_col,
                    @op_type = @op_type, -- 'ins, 'upd', 'del'
                    @is_new = @is_new,
                    @primary_key_bitmap = @primary_key_bitmap,
                    @colname = @colname,
                    @this_col = @this_col,
                    @column = @column output,
                    @from_proc = 0,
                    @coltype = NULL,
                    @type = NULL,
                    @art_col = @indkey

                    select @cmd = @cmd + @spacer + N'@c' +
                        convert(nvarchar(4), @this_col) + isnull(@postfix, N'')
                    
                    -- special processing for xml
                    
                    if (@ccoltype = 'xml')
                    begin
                        select @cmd = @cmd + N' = cast(' + @column + N' as nvarchar(max))'
                    end
                    else
                    begin
                        select @cmd = @cmd + N' = ' + @column
                    end

  select @spacer = N','
            end
            exec sys.sp_MSflush_command @cmd output, 1, @indent
            select @indkey = @indkey + 1
    end
    exec sys.sp_MSflush_command @cmd output, 1, @indent
END

 
Last revision 2008RTM
See also

  sp_MSscript_singlerow_trigger (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