Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2011
    Posts
    5

    Unanswered: Audit Trigger in stored procedure

    Hi all-
    I have a question for you sql gurus...
    I have a requirement to audit tables in a SQL Server database. The tables are dynamically created when the application creates a form and the table holds the form data. So my plan is this, I have worked out the audit table (static) and the trigger. What i'm having issues with is getting the trigger to create from within the stored procedure. So just to recap: the user creates a form in the app, this creates a table and should call this stored procedure. The stored procedure creates the trigger on that table (which begins auditing that table, inserting to the static audit table based on the table name being passed into the stored procedure).
    Where im at: I can create the stored procedure. When i go to run the stored procedure, I get the errors after passing the table as a value.
    In my opinion it's an error with the correct number of single ticks, but not sure.

    The Code:
    USE [AdventureWorks]
    GO

    /****** Object: StoredProcedure [dbo].[spReplaceAuditTrigger] Script Date: 12/06/2011 15:28:50 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO



    ALTER PROC spReplaceAuditTrigger( @PassedTableName as NVarchar(255) ) AS
    BEGIN
    DECLARE @ActualTableName AS NVarchar(255)

    SELECT @ActualTableName = QUOTENAME( TABLE_NAME )
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME = @PassedTableName

    DECLARE @sql AS NVARCHAR(MAX)
    SELECT @sql = 'create trigger [' + @PassedTableName + '_ChangeTracking] on [' + @PassedTableName + '] for insert, update, delete
    as

    declare @bit int ,
    @field int ,
    @maxfield int ,
    @char int ,
    @fieldname varchar(128) ,
    @TableName varchar(128) ,
    @PKCols varchar(1000) ,
    @sql varchar(2000),
    @UpdateDate varchar(21) ,
    @UserName varchar(128) ,
    @Type char(1) ,
    @PKFieldSelect varchar(1000),
    @PKValueSelect varchar(1000)

    select @TableName = ' + @PassedTableName + '

    -- date and user
    select @UserName = system_user ,
    @UpdateDate = convert(varchar(8), getdate(), 112) + '' + convert(varchar(12), getdate(), 114)

    -- Action
    if exists (select * from inserted)
    if exists (select * from deleted)
    select @Type = ''U''
    else
    select @Type = ''I''
    else
    select @Type = ''D''

    -- get list of columns
    select * into #ins from inserted
    select * into #del from deleted

    -- Get primary key columns for full outer join
    select @PKCols = coalesce(@PKCols + ''and'', '' on'') + '' i.'' + c.COLUMN_NAME + '' = d.'' + c.COLUMN_NAME
    from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
    where pk.TABLE_NAME = ' + @PassedTableName + '
    and CONSTRAINT_TYPE = ''PRIMARY KEY''
    and c.TABLE_NAME = pk.TABLE_NAME
    and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

    -- Get primary key fields select for insert
    select @PKFieldSelect = coalesce(@PKFieldSelect+''+'','''') + '''' + COLUMN_NAME + ''''
    from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
    where pk.TABLE_NAME = ' + @PassedTableName + '
    and CONSTRAINT_TYPE = ''PRIMARY KEY''
    and c.TABLE_NAME = pk.TABLE_NAME
    and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

    select @PKValueSelect = coalesce(@PKValueSelect+''+'','''') + ''convert(varchar(100), coalesce(i.'' + COLUMN_NAME + '',d.'' + COLUMN_NAME + ''))''
    from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
    where pk.TABLE_NAME = ' + @PassedTableName + '
    and CONSTRAINT_TYPE = ''PRIMARY KEY''
    and c.TABLE_NAME = pk.TABLE_NAME
    and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

    if @PKCols is null
    begin
    raiserror(''no PK on table %s'', 16, -1, ' + @PassedTableName + ')
    return
    end

    select @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = ' + @PassedTableName + '
    while @field < @maxfield
    begin
    select @field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = ' + @PassedTableName + ' and ORDINAL_POSITION > @field
    select @bit = (@field - 1 )% 8 + 1
    select @bit = power(2,@bit - 1)
    select @char = ((@field - 1) / 8) + 1
    if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in (''I'',''D'')
    begin
    select @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = @field
    select @sql = ''insert Audit (Type, TableName, PrimaryKeyField, PrimaryKeyValue, FieldName, OldValue, NewValue, UpdateDate, UserName)''
    select @sql = @sql + '' select '''' + @Type + ''''''
    select @sql = @sql + '',''' + @PassedTableName + '''''
    select @sql = @sql + '','' + @PKFieldSelect
    select @sql = @sql + '','' + @PKValueSelect
    select @sql = @sql + '','''' + @fieldname + ''''
    select @sql = @sql + '',convert(varchar(1000),d.'' + @fieldname + '')''
    select @sql = @sql + '',convert(varchar(1000),i.'' + @fieldname + '')''
    select @sql = @sql + '','''' + @UpdateDate + ''''''
    select @sql = @sql + '','''' + @UserName + ''''''
    select @sql = @sql + '' from #ins i full outer join #del d''
    select @sql = @sql + @PKCols
    select @sql = @sql + '' where i.'' + @fieldname + '' <> d.'' + @fieldname
    select @sql = @sql + '' or (i.'' + @fieldname + '' is null and d.'' + @fieldname + '' is not null)''
    select @sql = @sql + '' or (i.'' + @fieldname + '' is not null and d.'' + @fieldname + ''is null)''
    exec (@sql)
    end
    end'

    EXEC(@SQL)
    END
    GO


    I appreciate everyone's input.

    Dan

  2. #2
    Join Date
    Dec 2011
    Posts
    5

    Follow up...

    Hi all -
    This is on MSSQL 2008r2, with a home grown app.

    Dan

    Thanks all..

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by danimaltex View Post
    The tables are dynamically created when the application creates a form and the table holds the form data.
    This is just a bad idea all around. What is the purpose of this database?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    When you do things like these, it is always a good idea to see what SQL code your program is generating. With a simple
    Code:
    -- EXEC (@SQL)
    PRINT @SQL
    at the end of your code, you can debug a large part of it.
    To find the harder to catch bugs, run the generated code, and see what errors it produces.
    Last step is debugging the functionality of the trigger itself.

    Am I getting this correct?
    - Your users will dynamically create tables in your database.
    - And you want to extend each and every user created table with an audit trail.
    - I guess they can manage the contents of their tables too from within your application.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •