• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 206
  • Last Modified:

"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!
0
GEHC
Asked:
GEHC
2 Solutions
 
Guy Hengel [angelIII / a3]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
 
BrandonGalderisiCommented:
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now