Trigger for creation Date, Time & last update Date , Time 1

[DaveRolph]

Hi

I am using Server 7

I am looking for what you guys may think is a very simple trigger, (I am at the bottom of a big hill and need help climbing!)

I want to be able to record the Date / Time a record was created and the user who created it.

I also want to record the last update Date / Time, and User.

Thanks in advance

 

[vechalapu]

Hi,

For Create Date and Create User use defaults i.e getdate() and suser_name() functions in the table creation.

For recording Update Date and Update User, I would prefer to include in each update statement on the table with getdate() and the suser_name() or process name.

 

[fluteplr]

This is how I do it.

4 fields

added datetime
addedby char(20)
modified datetime
modifiedby char(20)

defaults

added getdate()
addedby suser_name()
modified getdate()
modifiedby suser_name()

update trigger

update tablename
set modified = getdate(),
modifiedby = suser_name()
from tablename, inserted
where tablename.keyfield = inserted.keyfield

I use the default for the modified fields also since it makes the query of when was it last modified or added easier.

 

[cclabaugh]

I have done this method, however, it updates every row in my table. How can I write the trigger so that it only updates the LastModified column for the row that was modified, regardless of what column was changed?

 

[cclabaugh]

Found the problem:
For SQL 2000 use the following example:

CREATE TRIGGER trgUpdateLastModified ON [dbo].[tblVehicles]
FOR UPDATE
AS
UPDATE tblVehicles
SET LastModified = GetDate()
From tblVehicles, inserted
WHERE tblVehicles.VehicleID = Inserted.VehicleID