locked
Is there way to get last updated column name in sql server ? RRS feed

  • Question

  • CREATE TABLE test(
        id char(5) NOT NULL,name nchar(20) NULL, Mphone int NULL,Tel int NULL );

    insert into test values('e001','david',5456789,5456422);

    update test set MPhone =012345
    where id='e001';

    i want to get result as updated column is  Mphone
    Thursday, August 1, 2013 11:35 PM

Answers

  • You can use an OUTPUT clause, see example

    create table t ( i int not null );
    create table t_audit ( old_i int not null, new_i int null );
    insert into t (i) values( 1 );
    insert into t (i) values( 2 );
     
    update t
       set i  = i + 1
    output deleted.i, inserted.i into t_audit
     where i = 1;
     
    delete from t
    output deleted.i, NULL into t_audit
     where i = 2;
     
    select * from t;
    select * from t_audit;
     
    drop table t, t_audit;
    go

    Another option is to have a trigger for UPDATE and examine two virtual table (inserted,deleted)


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance


    • Edited by Uri DimantMVP Friday, August 2, 2013 3:44 AM
    • Proposed as answer by Shanky_621MVP Saturday, August 3, 2013 8:14 AM
    • Marked as answer by Fanny Liu Friday, August 9, 2013 11:19 AM
    Friday, August 2, 2013 3:44 AM
  • Hi ,

    If not for single table , try with Auditing features in SQL Server - SQL Server Database Audit Specifications ,

    Change data capture , Chnage tracking or Audit trigger (with Audit table tracking all changes)


    sathya --------- Mark as answered if my post solved your problem and Vote as helpful if my post was useful.

    • Marked as answer by Fanny Liu Friday, August 9, 2013 11:19 AM
    Monday, August 5, 2013 6:04 PM

All replies

  • You can use an OUTPUT clause, see example

    create table t ( i int not null );
    create table t_audit ( old_i int not null, new_i int null );
    insert into t (i) values( 1 );
    insert into t (i) values( 2 );
     
    update t
       set i  = i + 1
    output deleted.i, inserted.i into t_audit
     where i = 1;
     
    delete from t
    output deleted.i, NULL into t_audit
     where i = 2;
     
    select * from t;
    select * from t_audit;
     
    drop table t, t_audit;
    go

    Another option is to have a trigger for UPDATE and examine two virtual table (inserted,deleted)


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance


    • Edited by Uri DimantMVP Friday, August 2, 2013 3:44 AM
    • Proposed as answer by Shanky_621MVP Saturday, August 3, 2013 8:14 AM
    • Marked as answer by Fanny Liu Friday, August 9, 2013 11:19 AM
    Friday, August 2, 2013 3:44 AM
  • Hi ,

    If not for single table , try with Auditing features in SQL Server - SQL Server Database Audit Specifications ,

    Change data capture , Chnage tracking or Audit trigger (with Audit table tracking all changes)


    sathya --------- Mark as answered if my post solved your problem and Vote as helpful if my post was useful.

    • Marked as answer by Fanny Liu Friday, August 9, 2013 11:19 AM
    Monday, August 5, 2013 6:04 PM