How to Identify the last modified row in a Data Table ,Is there any T-SQL Commands?

  • raja_saminathan

    SSCommitted

    Points: 1607

    Hi,

    How to Identify the last modified row in a Data Table ,Is there any T-SQL Commands?

    My scenario is i want to write a constraint which should check the values in a particular field for not null values and the constraint only allow null values to the last modified field in the data Table

    Thanks

    rajesh

  • SQLZ

    SSChampion

    Points: 12872

    Unfortunately there aren't any T-SQL commands for this.

    You'd have to track data modifications yourself by implementing your own solution. For example, you could add a Last_Modified column and ensure that the column is updated with getdate() every time the table is updated. If you're using stored procedures exclusively to do data modifications then it's just a question of updating the necessary stored procedures to deal with this extra column. Otherwise, you could write a trigger to do this for you.

  • raja_saminathan

    SSCommitted

    Points: 1607

    Hi Karl,

    Thanks a lot ,Updating the variable using GetDate() is very useful to me to sort out the problem.

    BestRegards,

    Rajesh

  • Kurt W. Zimmerman

    SSCrazy Eights

    Points: 8674

    I worked on an application where by all tables in the database had audit information. The audit information contained Created By, Created Date, Updated By, Updated Date. All of the stored procedures that added or updated any table posted the audit information. It was quite effective and had a number of very useful benefits. Now not all applications can carry the extra burden of this type of audit information so consider some sort of journal to log important or critical updates.

    The above mentioned application also had an audit feature that identified old/new information that was being modified in certain tables. Again, a bit of overhead but it fit the business needs.

    Kurt

    Kurt W. Zimmerman
    SR DBA
    Lefrak Organization
    New York, NY

    http://www.linkedin.com/in/kurtwzimmerman

Viewing 4 posts - 1 through 4 (of 4 total)

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