"Date Modified" column

On SQL Server 2005, looking for the easiest way to add a "date modified" column for all table rows. If adding a trigger is the only way please provide trigger definition/t-sql.
thanks!
GEHCAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
yes, a trigger is the way to go:
CREATE TRIGGER trg_update_yourtable
 ON yourtable
 FOR UPDATE
AS
  UPDATE t
    SET date_modified = GETDATE()
   FROM yourtable t
   JOIN INSERTED i
     ON t.primary_key_field = i.primary_key_field
    AND t.date_modified < GETDATE()

Open in new window

0
 
BrandonGalderisiConnect With a Mentor Commented:
Adding the Date modified column is the easy part.


alter table YourTable
add last_modified_Dttm datetime null

I use null and no default because I want to differentiate between a record that was and was not modified.

I then leave the update of the last_modified_dttm column to the update stored procedure on the table as ALL of my data access goes through SPs.
0
 
adlink_laCommented:
If all you want is to have the date_modified column updated, I would go with Brandons approach and set the value in your update procedure.  I would stay away from triggers unless you absolutely need them as they can cause performance problems.  
0
 
BrandonGalderisiCommented:
Triggers are necessary if you don't have absolute control over EVERY (which should be 1) method of updating the data though.  Such as applications with embedded SQL.
0
 
GEHCAuthor Commented:
Yes, I am  developer not a DBA and it IS an app I am having to rewrite. But its mission and task specific not user heavy. I can possible try both approaches. I was hoping though that SQL 2005 had something built -in/update row version.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.