Timestamp update
-
Could someone help me with a stored procedure to update a timestamp field in my sql 2000 database on update.
Table name: FileUpload
Field name: mytimestamp
-
I am not sure if we can update the timestamp field in a table. Since the timestamp field itself keeps track of changes (whether inserts or updates) made on the table. We can find out the latest timestamp by doing the following
SELECT @@DBTS
If somebody knows how to do it. I would like to know it too
-
If you want to store the current datetime each time a row is updated, you would need to add a datetime column to your table, then add an UPDATE trigger. For example, if you added a column called dateOfLastUpdate like this:
ALTER TABLE FileUpload ADD dateOfLastUpdate datetime NOT NULL DEFAULT GetDate()
You could then create this simple trigger:
CREATE TRIGGER tru_FileUpload ON dbo.FileUpload
FOR UPDATE
AS
UPDATE FileUpload
SET dateOfLastUpdate = GETDATE()
FROM FileUpload
JOIN inserted ON FileUpload.primaryKey = inserted.primaryKey
-
Excellent. Works perfectly.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply