Post reply

How to write a query to find out I Inserted records in Sql servertable

  • December 16, 2011 at 11:21 pm

    #391151

    Scenario is

    I Have one table in server database example "employee" in that first column is identity column i.e empid ,

    empname ,sal like that

    All users inserting records into table How to write query find out i inserted records in that table

    Pls help me

  • December 17, 2011 at 12:32 am

    #1422792

    You can use CDC feature if you are using SQL server 2008. it will provide the data changes but it won't give , who did the change.

    refer below link

    http://www.mssqltips.com/sqlservertip/1474/using-change-data-capture-cdc-in-sql-server-2008/

    java[/url]

  • December 17, 2011 at 1:01 am

    #1422793

    SQL Server Audit feature can help you that you want.

    http://www.mssqltips.com/sqlservertip/1954/sql-server-2008-auditing-feature/

    java[/url]

  • December 17, 2011 at 1:08 am

    #1422794

    For this simple scenario CDC is not required. Add two audit columns in Employee table ‘Modified_By’ & ‘Modification_Date’ (optionally add 2 more columns ‘Created_By’ & ‘Creation_Date’). It should satisfy your requirements.

  • December 17, 2011 at 2:18 am

    #1422802

    That's not automatically tracked, SQL doesn't store that info for you anywhere, so for older records, unless you had some form of auditing there's no way you'll be able to tell that.

    Going forward there are many ways to do this, especially on SQL 2008. You can use SQL Audit, Change Tracking, Change Data Capture, triggers with an audit table or simply a column with a default value of Original_Login()

    I suggest you read up on the various options and see which will work better for you, they all have advantages and disadvantages.

    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

  • December 20, 2011 at 3:23 am

    #1423521

    As other repondants have evidently mentioned, you can have a column such which will store the value of the SQL function ORIGINAL_LOGIN() and later use the filter cluse to retrieve the records who inserted what data

    Raunak J

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

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