Columns updated history

  • I am looking for the best way to track changes in data.

    Example:

    Table XYZ with 20 columns.

    User can change data in some or all of the columns via app per record

    What I need is a history tbl where I can see which user updated which data.The user

    user and date history is no problem but getting the column data is a problem, because

    it must be only the change data.

    example : "10/10/2002 13:00:00","WORKGROUP\USERNAME","Column:Surname CHANGE TO 'Kraai';Column:Name Change to 'Pompei'";

    Thanks

    Spaski

  • You will need to use a Trigger on the table being updated.

    Inside the trigger you can use the IF UPDATE(columnname) to see if the SQL statement had a SET statement on the column.

    You can use this in conjunction with the INSERTED and DELETED pseudo tables that are available to you in triggers to see the before and after images of the rows updated.

  • I need more isn't there a better way than this because, this trigger must be on alot

    of tables. To make a If Update(columnname) for each column in each table is alot of

    of prgramming.

    I am trying with "IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask ) { comparison_operator } column_bitmask [ ...n ] " function but this one is a bit hard to figure out

    Thanks

  • Here is an example that might help you use the COLUMNS_UPDATED() bitwise function to determine the columns update.

    set nocount on

    create table my_table (a int, b int, c int, d int, e int)

    go

    create TRIGGER my_trig2

    ON my_table

    FOR insert, update

    AS

    if exists(select * from deleted) and

    exists(select * from inserted)

    print 'updating a row'

    else if exists(select * from deleted)

    print 'deleting rows'

    else if exists(select * from inserted)

    print 'inserting rows'

    declare @I int

    declare @nc int -- number of columns

    declare @x int

    set @nc = 5

    set @x = 0

    set @i = 1

    while @x < @nc

    begin

    IF ( COLUMNS_UPDATED() & @i = @i )

    PRINT 'Column ' + cast(@x+1 as char(1)) + ' Modified'

    set @I = @I * 2

    set @x = @x + 1

    end

    GO

    insert into my_table (a) values('1')

    update my_table

    set a = '2'

    update my_table

    set b = '2'

    update my_table

    set c = '2'

    update my_table

    set d = '2'

    update my_table

    set e = '2'

    drop table my_table

    Gregory Larsen, DBA

    If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

    Gregory A. Larsen, MVP

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply