Trigger + Update field with the current date on Update in SQL server

[inAwww]

Hi All,
I was wondering if anyone of you has a solution to be able to update 1 single row on a table with a trigger. I am trying to keep track of the modification dates on certain tables. So when someone updates a record it will stamp the current date on the modifydate field. So far, I have only been able to update ALL the records on a table when the trigger fires.
I used something like this:

Create Trigger UpdateDate
on tablename
FOR UPDATE
AS

UPDATE tablename
SET modifydate = getdate()
WHERE....... Here is where I get stopped. without a where clause I obviously get to update all the records in the table but.. I just dont know how to tell SQL in that where clause to update JUST the record that was updated.

I think that this is a common thing that gets used quite often (that I cant get right ) so I am sure many of you might have an Idea of how to come about this. Any kind of help would be really appreciated though.
Thank you,

 

[RobS23]

You dont need to write a trigger just add a column with the timestamp data type. This will stamp the time each time the row is updated

 

[JohnDTampaBay]

Two virtual tables exist during an UPDATE, but they disappear as soon as the UPDATE completes: Inserted and Deleted. The Deleted table contains the row(s) of data prior to UPDATE and the Inserted table contains the row(s) of data after the UPDATE. The tables have the exact same structure as the table being updated.

In your trigger, JOIN your table to either of these tables to identify only the rows actually changed. For instance for a table defined as UserID, UserName and ModifyDate, the trigger would read something like...

CREATE TRIGGER UpdateDate
ON tablename FOR UPDATE AS
 UPDATE tablename
 SET modifydate = getdate()
 FROM tablename a
   JOIN Inserted b ON a.UserID = b.UserID

Good luck! --Angel
-----------------------------------
SELECT * FROM users WHERE clue > 0
0 row(s) selected

 

[JohnDTampaBay]

Sorry, Rob, but not quite true. From BOL...

The Transact-SQL timestamp data type is not the same as the timestamp data type defined in the SQL-92 standard. The SQL-92 timestamp data type is equivalent to the Transact-SQL datetime data type.

Timestamp columns contain binary data which doesn't mean much to most of us. --Angel
-----------------------------------
SELECT * FROM users WHERE clue > 0
0 row(s) selected

 

[inAwww]

Yeah, Thanks Rob, I actually found the timestamp binary deal the hard way, after I had created all the tables with a timestamp field in them.

Angel's code though worked great. thanks so much. I will have to create a different trigger for each table as they obviously have different keys etc. But that works great. Thanks Angel,

Inawww