Post reply

Track table row deletion

  • December 10, 2010 at 4:44 am

    #388411

    What is the best way to track the user who deletes any records in a specified table?

    User information, such as machine name, login name etc, needs to be logged.

  • December 10, 2010 at 5:04 am

    #1260978

    You can write a DB trigger For delete statements and Stored the username,currentdate,Servername in another table ...Just look the Example

    create trigger tr_delete_log on dbo.TableA --(Table where delete occurs)

    for delete

    as

    insert into dbo.DeleteLog--New table containing username,currentdate,Servername

    select SUSER_NAME(),getdate(),HOST_NAME()

    GO

  • December 10, 2010 at 5:21 am

    #1260980

    Another option is

    1) Don't give delete permission to normal users

    2) Allow delete only from the stored procedure. In the stored procedure, save the required information.

  • December 10, 2010 at 5:23 am

    #1260981

    One another option is CDC (Change Data Capture).

  • December 10, 2010 at 7:16 am

    #1261031

    Can CDC capture specifically Delete statements?

  • December 10, 2010 at 7:22 am

    #1261035

    That question is too large for a forum thread... read on all you need here :

    http://msdn.microsoft.com/en-us/library/bb522489.aspx

  • December 10, 2010 at 7:25 am

    #1261037

    Thanks, ninja.

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

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