[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 205
  • 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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