Formula field, last edited by and last edit timestamp?

[mveino]

Is there a way to make it so when a field is updated, the user's ID and a timestamp is put into the field?

I tried just using current_timestamp and user in the formula field, but when viewing all the records all it does is return the current time and current user for all the records (which makes sense)

Thanks.

 

[PHV]

Have a look at Trigger

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mveino]

Aha, got it... set up a trigger to update those fields to current_timestamp and user on an insert or an update... thanks!

Didn't know about triggers, they'll come in very handy...

 

[mveino]

A follow up... I'm confused how I can only have the trigger act on the row that was edited...

Basically I need to have a "where key = <however i can refer to the row updated/added>", but i don't know how to tell it to do this.

Here's the current trigger, that updates everything in the table:
CREATE TRIGGER [insertLastEditedInfo] ON dbo.Phone
FOR INSERT, UPDATE
AS

update Phone
set lasteditdate = current_timestamp, lastedituser = user

Thanks.

 

[mrdenny]

Like this.

CREATE TRIGGER [insertLastEditedInfo] ON dbo.Phone 
FOR INSERT, UPDATE
AS

update Phone 
set lasteditdate = current_timestamp, lastedituser = user
where Phone.Key in (select Key from inserted)

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[mveino]

Ah ok, and will the "select Key from inserted" work for when a value is updated as well? Or would I have two separate keys, one that says "select Key from inserted" and one that says "select Key from updated" depending on if I have it as the insert or the update trigger?

Thanks for the quick reply.

 

[mrdenny]

insert and update triggers both use the inserted virtual table. You only need the one trigger for both.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)