Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSscript_trigger_variables

  No additional text.


Syntax
create procedure sys.sp_MSscript_trigger_variables
(
    @objid int,
    @prefix char(1) = null, -- null or '@'
    @postfix varchar(4) = null,
    @indent int = 0,
    @spacer nvarchar(1) = N' ',
    @bOutput_params tinyint = 0,  -- declare output params if necessary
    @identity_col sysname = null,
    @ts_col sysname = null,
    @include_type bit = 0,
    @set_nulls    bit = 0,
    @op_type char(3) = 'ins', -- 'ins, 'upd', 'del'
    @is_new        bit = 0,
    @primary_key_bitmap varbinary(4000) = NULL,
    @no_output bit = 0
)
as
begin
    set nocount on
    declare @cmd          nvarchar(4000)
        ,@colname      sysname
        ,@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)

    -- script cursor select variables
    select @cmd = N''
            ,@thisspname = N'sp_MSscript_trigger_variables'
            ,@indkey = 1
            ,@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, 4, @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
        -- Don't include timestamp or computed columns
        
        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 and system_type_id <> 189)
        begin
            if @prefix is null
            begin
                if (@set_nulls = 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 = @ccoltype,
                        @type = NULL,
                        @art_col = @indkey
                    select @column = @column + isnull(@postfix, N'')
                end
                else
                begin
                    -- set null is false
                    select @column = quotename(@colname + isnull(@postfix, N''))
                end
                
                -- special processing for xml data in trigger multirow processing
                -- when we use this SP for scripting the cursor definition
                
                if (@ccoltype = 'xml' and @prefix is null and @postfix is null)
                    select @column = 'cast(' + @column + 'as nvarchar(max)) '
                
                -- append to the command
                
                select @cmd = @cmd + @spacer + @column
            end -- if @prefix is null
            else
            begin
                -- prefix was specified
                select @cmd = @cmd + @spacer + isnull(@prefix, N'')
                + N'c' + RTRIM(convert(nvarchar(4), @this_col)) + isnull(@postfix, N'')
            end

            if (@include_type = 1)
            begin
                declare @typestring nvarchar(100)
                exec sys.sp_MSget_type @objid, @this_col, @colname output, @typestring OUTPUT
                select @cmd = @cmd +  N' ' + @typestring
            end
            -- new vars of type timestamp and identity are declared as output params
            if (@bOutput_params = 1 and (@ccoltype = N'timestamp' or ColumnProperty(@objid, @colname, 'IsIdentity') = 1))
            or (@colname = @identity_col or @colname = @ts_col)
            begin
                -- YWU: Do this to avoid output in cursor declaration statement.
                -- The right thing seems to be set output only when bOutput_params is set
                -- but it seems not the way this sp is called.
                if @set_nulls = 0 and @no_output = 0
                select @cmd = @cmd + N' output'
            end
            select @spacer = N','
        end -- if rc=0 and exists ...

        exec sys.sp_MSflush_command @cmd output, 0, @indent
        select @indkey = @indkey + 1
    end -- while () loop

    exec sys.sp_MSflush_command @cmd output, 1, @indent
    insert into #proctext(procedure_text) values(N'
')

end

 
Last revision 2008RTM
See also

  sp_MSscript_multirow_trigger (Procedure)
sp_MSscript_trigger_exec_rpc (Procedure)
sp_MSscript_trigger_fetch_statement (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