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

    Unanswered: Need Update Trigger dynamically

    Hi,

    I have a table (CMSAddress) which contains some n number of fields, i am updating my details but when i am updating i want to maintain and insert filed names like column name,oldvalue,new value into another table which fields are updated.

    I used trigger for updating but it is working fine
    My Query is

    CREATE TRIGGER TRIGGER_CMSAddress
    ON CMSAddress
    AFTER UPDATE
    AS
    IF ( UPDATE (AddressLine1) )
    BEGIN
    INSERT INTO CMSHistory
    (ObjectId,ReferenceId,EntityId,ColumnName,OldValue ,NewValue,ModifiedDate)
    SELECT
    'CMSAddress',ins.ReferenceId,ins.EntityId,'Address Line1',(select AddressLine1 from deleted),ins.AddressLine1,getdate()
    FROM inserted ins
    END
    IF ( UPDATE (AddressLine2) )
    BEGIN
    INSERT INTO CMSHistory
    (ObjectId,ReferenceId,EntityId,ColumnName,OldValue ,NewValue,ModifiedDate)
    SELECT
    'CMSAddress',ins.ReferenceId,ins.EntityId,'Address Line2',(select AddressLine2 from deleted),ins.AddressLine2,getdate()
    FROM inserted ins
    END
    IF ( UPDATE (CityName) )
    BEGIN
    INSERT INTO CMSHistory
    (ObjectId,ReferenceId,EntityId,ColumnName,OldValue ,NewValue,ModifiedDate)
    SELECT
    'CMSAddress',ins.ReferenceId,ins.EntityId,'CityNam e',(select CityName from deleted),ins.CityName,getdate()
    FROM inserted ins
    END
    IF ( UPDATE (StateName) )
    BEGIN
    INSERT INTO CMSHistory
    (ObjectId,ReferenceId,EntityId,ColumnName,OldValue ,NewValue,ModifiedDate)
    SELECT
    'CMSAddress',ins.ReferenceId,ins.EntityId,'StateNa me',(select StateName from deleted),ins.StateName,getdate()
    FROM inserted ins
    END

    GO



    But i dont want to check each column like "IF ( UPDATE (each column name)
    ) " i required the query without mentioning each column name means column name to be checked dynamically.
    Edit/Delete Message

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Rethink your audit design.
    You can pretty much copy the DDL of your existing table, just remove any constraints and keys and add a datetime stamp and what not.
    George
    Home | Blog

  3. #3
    Join Date
    Mar 2010
    Location
    Hyderabad
    Posts
    12
    home


    Generic audit trail trigger
    Author Nigel Rivett

    /*
    This trigger audit trails all changes made to a table.
    It will place in the table Audit all inserted, deleted, changed columns in the table on which it is placed.
    It will put out an error message if there is no primary key on the table
    You will need to change @TableName to match the table to be audit trailed
    */

    --Set up the tables
    if exists (select * from sysobjects where id = object_id(N'[dbo].[Audit]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[Audit]
    go
    create table Audit (Type char(1), TableName varchar(128), PK varchar(1000), FieldName varchar(128), OldValue varchar(1000), NewValue varchar(1000), UpdateDate datetime, UserName varchar(128))
    go
    if exists (select * from sysobjects where id = object_id(N'[dbo].[trigtest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[trigtest]
    go
    create table trigtest (i int not null, j int not null, s varchar(10), t varchar(10))
    go
    alter table trigtest add constraint pk primary key (i, j)
    go

    create trigger tr_trigtest on trigtest 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) ,
    @PKSelect varchar(1000)

    select @TableName = 'trigtest'

    -- 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 = @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+'+','') + '''<' + COLUMN_NAME + '=''+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

    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
    select @sql = 'insert Audit (Type, TableName, PK, FieldName, OldValue, NewValue, UpdateDate, UserName)'
    select @sql = @sql + ' select ''' + @Type + ''''
    select @sql = @sql + ',''' + @TableName + ''''
    select @sql = @sql + ',' + @PKSelect
    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
    go


    insert trigtest select 1,1,'hi', 'bye'
    insert trigtest select 2,2,'hi', 'bye'
    insert trigtest select 3,3,'hi', 'bye'
    select * from Audit
    select * from trigtest
    update trigtest set s = 'hibye' where i <> 1
    update trigtest set s = 'bye' where i = 1
    update trigtest set s = 'bye' where i = 1
    update trigtest set t = 'hi' where i = 1
    select * from Audit
    select * from trigtest
    delete trigtest
    select * from Audit
    select * from trigtest

    go
    drop table Audit
    go
    drop table trigtest
    go




    home

  4. #4
    Join Date
    Mar 2010
    Location
    Hyderabad
    Posts
    12

    AuditTrailTrigger

    use the following link for AuditTrailTrigger i worked on this i got result

    Sql Server. Generic audit trail trigger - Author Nigel Rivett

Posting Permissions

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