Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2010
    Location
    Hyderabad
    Posts
    12

    Unanswered: Clarification reqired for Audit table query

    Hi friends,

    I did audit table query using update trigger that is working fine. The query is


    GO
    /****** Object: Trigger [dbo].[TRIGGER_CMSStudentDetails] Script Date: 04/28/2010 09:05:04 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    ALTER TRIGGER [dbo].[TRIGGER_CMSStudentDetails] ON [dbo].[CMSStudentDetails]
    FOR UPDATE
    AS
    DECLARE @bit INT ,
    @field INT ,
    @maxfield INT ,
    @char INT ,
    @fieldname VARCHAR(128) ,
    @TableName VARCHAR(128) ,
    @PKCols VARCHAR(1000) ,
    @sql VARCHAR(2000),
    @Type CHAR(1) ,
    @PKSelect VARCHAR(1000),
    @EntityId VARCHAR(10)


    --change @TableName to match the table to be audited

    SELECT @TableName = 'CMSStudentDetails'
    SET @EntityId = '3'

    -- 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 = @TableName
    and CONSTRAINT_TYPE = 'PRIMARY KEY'
    and c.TABLE_NAME = pk.TABLE_NAME
    and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

    -- Get primary key select for insert
    select @PKSelect = coalesce(@PKSelect+'+','') + '''' + '''+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 = @TableName
    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, @TableName)
    return
    end
    --print @PKCols



    --get field names which are updated and insert

    SELECT
    @field = 0,
    @maxfield = MAX(ORDINAL_POSITION)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = @TableName
    WHILE @field < @maxfield
    BEGIN
    SELECT @field = min(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName 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
    IF ( @fieldname != 'ModifiedDate' )
    BEGIN
    set @sql = 'insert into CMSAuditHistory (ObjectId, ReferenceId, EntityId, FieldName, OldValue, NewValue, ModifiedDate)'
    set @sql = @sql + ' select ''' + @TableName + ''',' + @PKSelect + ',''' + @EntityId +
    ''',''' + @fieldname + ''',' + ' convert(varchar(1000),d.' + @fieldname + '),' + ' convert(varchar(1000),i.' + @fieldname + ')' +
    ',''' + cast(getdate() as varchar(25)) + ''' from #ins i, #del d where i.' + @fieldname + ' <> d.' + @fieldname + ' or (i.' + @fieldname +
    ' is null and d.' + @fieldname + ' is not null ) or (i.' + @fieldname + ' is not null and d.' + @fieldname + ' is null )'
    exec(@sql)
    END
    END
    END


    But in the above query i used dynamic query for inserting (set @sql like)
    but i want to use normal insert query like

    INSERT INTO
    CMSAuditHistory (ObjectId,
    ReferenceId,
    EntityId,
    FieldName,
    OldValue,
    NewValue,
    ModifiedDate)
    SELECT @TableName,
    @PKSelect,
    @EntityId,
    @fieldname,
    'convert(varchar(1000),d.' + @fieldname + ')',
    'convert(varchar(1000),i.' + @fieldname + ')',
    getdate()
    FROM Inserted i ,deleted d
    SELECT * FROM Inserted i ,deleted d


    here is the problem is i am unable to get primary key value, old value, new value please give me ur suggession in the above query.
    These are inserting 'convert(varchar(1000),d.' + @fieldname + ')', 'convert(varchar(1000),i.' + @fieldname + ')', as tease and these values are not reading from inserted, deleted tables.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I suggest you drop ALL dynamic sql from your trigger, eliminate the parameters and temporary tables, stop trying to code a "one trigger fits all" solution, and instead write simple and efficient custom audit triggers for each table.
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Dude - this has been going on and on.

    Please don't keep creating new threads - I will delete any further ones on this topic.

    I thought you used Nigel Rivet's code and were really happy with it?

Posting Permissions

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