The Books Online has a pretty good discussion of triggers. You should definitely review that information.

Basically, a trigger fires once no matter how many row are affected by the query. So you're trigger must be written to handle the modification of multiple rows. SQL Server has two helper tables that exist only within the scope of the trigger: Inserted and Deleted. The Deleted table contains a copy of the affected rows before the modifications have been made. The Inserted table contains a copy of the affected rows after the modifications have been made.

For your specific situation, you could use something like this:

UPDATE mytable
SET UserModified = user_name(), DatetimeModified = getdate()
WHERE mytable.pk IN (
SELECT pk FROM Inserted)

-Mike


>Hi friends, I have a very a question: How can I create a trigger that updates other fields in the same table? I mean, I know (but I'm not sure ;)) that I must create an update-trigger (a trigger that executes whenever a tables has been updated, sorry for my english) but I never created a trigger so I would like any of you to help me create one. My problem is that I need to record on two fields (cUserModified and cDatetimeModified) the user and datetime that the data on the record was last modified. My VFP app uses a DSN connection with Windows NT autentication (trust connection) so SQL Server knows already who is accessing data.
>
>Any help would be useful.
>
>TIA
>
>Luis