Post reply

Query to track record

  • March 14, 2014 at 5:24 am

    #286717

    Hi I am having table which is having 5 columns say A,B,C,D and E. There are chances to change in C,D,E columns. I want to identify if any change happened in the above column and show to user prev value + new value.

    i.e. i want to prepare query by using calculated extra 6 column where i want to put change happned in C,D,E columns.

    how to do that?

    Thanks

    Abhas.

  • March 14, 2014 at 5:58 am

    #1697104

    There are ways to accomplish this task.

    1. Enable the auditing feature in SQL SERVER 2008.

    http://technet.microsoft.com/en-us/library/dd392015%28v=sql.100%29.aspx

    2. Use Output Clause. http://technet.microsoft.com/en-us/library/ms177564%28v=sql.100%29.aspx

    3. Classic Trigger way.

    you have to review which process you should implement.

  • March 14, 2014 at 6:34 am

    #1697115

    Thanks Twinn,

    But is it possible using query?

    -- Create the test table

    create table testTable (

    field1 integer,

    field2 varchar(30)

    field3 varchar(30)

    field4 varchar(30)

    );

    -- Populate the test table

    insert into testTable values (1, 'Jane','abc','efg');

    insert into testTable values (2, 'John','mnn','opr');

    insert into testTable values (3, 'Joe','xyz','lmn');

    Now after inserting this record

    Case 1)

    for field2 if sombody changes value of field4 from 'opr' to 'abhas'

    then my query should display output as

    field1 field2 changedrecord

    2 John Value changed from 'opr' to 'Abhas'

    Case two)

    for field3 if sombody changes value of field3 from 'xyz' to 'kgn' and value of field4 from 'lmn' to 'pqr'

    then my query should display output as

    field1 field2 changedrecord

    2 John Value changed from 'opr' to 'Abhas'

    3 Joe Value changed from 'xyz' to 'kgn'

    Value changed from 'lmn' to 'pqr'

    I need output exact as above.

    Could somebody please help me.

  • March 14, 2014 at 6:46 am

    #1697118

    when DML operation is just work as an event which needed to be captured. you need to store old new field values somewhere and then select from that table to get the desired result set. you cannot just get that information using a query.

    hope it helps

  • March 14, 2014 at 8:09 am

    #1697156

    Thanks twin,

    All changes are aviliable in a table itself and we can idenitfy that changes by using EffectivestartDate column.

    By using this column we can find out latest two records. I am stucked and not able to implement logic how to fetch latest two records.

    Thanks

    Abhas

  • March 14, 2014 at 9:12 am

    #1697176

    abhas (3/14/2014)

    Thanks twin,

    All changes are aviliable in a table itself and we can idenitfy that changes by using EffectivestartDate column.

    By using this column we can find out latest two records. I am stucked and not able to implement logic how to fetch latest two records.

    Thanks

    Abhas

    Like this?

    select top 2 [Columns]

    from YourTable

    order by EffectivestartDate desc

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 6 posts - 1 through 5 (of 5 total)

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