Post reply

Who is changing the record... ??

  • May 28, 2012 at 4:11 am

    #261833

    Hi all,

    I have a record in a table which gets updated every now and then. We have got 3-4 applications which talk to that table and add/modify the table values. I want to know which application is updating the record. I cannot do this at application level because we bought it from market and don't have the code. So I have no other choice but to monitor it at db level. I was thinking of creating a trigger to check when and what value gets changed.

    Can you please suggest a better way of doing it (if not trigger), also if I create a trigger, is there any way I can find out which application did the changes. ??

    I hope I made myself clear.. If you need more clarification please do write back.

    Thanks,

    Deepak

  • May 28, 2012 at 4:21 am

    #1493706

    Trigger or SQLTrace are about the only options on SQL 2005.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 29, 2012 at 10:27 am

    #1494279

    Deepak.Sharma507 (5/28/2012)

    ...if I create a trigger, is there any way I can find out which application did the changes. ??

    In your delete trigger this will retrieve the name of the app specified in the connection string for the connection making the change:

    SELECT [program_name]

    FROM sys.dm_exec_sessions

    WHERE session_id = @@SPID;

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

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

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