Attempting to grab a date and time when a specific field in one record is updated
I had to add Comment and DateTime comment fields to the table. The user will leave comments, and when this is complete, the Comments DataTime should record the date and time that the Comments field was updated. Many examples I've seen keep track of when the entire record updates the datetime field, however I am looking to capture the datetime only when the specific comment field is updated for that record. How should I do it? Any advice is appreciated.
edit Getting incorrect syntax near the 'FROM' keyword
CREATE TRIGGER dbo.updateComments
ON dbo.tbl_location_history
/*Check whether column comments has been updated. If column comments has
been changed, update column comments_datetime with getdate().*/
FOR INSERT,UPDATE AS
/*Use IF (COLUMNS_UPDATED() &7) = 7 to see whether column 7 was updated.*/
/*Checking out IF UPDATE(comments) for proper time stamp */
IF UPDATE(comments)
BEGIN
SET NOCOUNT ON;
UPDATE dbo.tbl_location_history
SET dbo.tbl_location_history.comments_datetime = GETDATE();
FROM table dbo.tbl_location_history INNER JOIN
inserted i ON dbo.tbl_location_history.location_id = i.id
END
source to share
If you want to keep track of when ONLY the comment field is updated, you can use the COLUMNS_UPDATED () function in the update trigger. It can be used to track if columns 1, 4, and 6 have been updated, or it can track if only column 10 has been updated. The code that will be placed in your trigger will look something like this:
IF CAST(SUBSTRING(COLUMNS_UPDATED(),1,1) AS INT) = 0)
BEGIN
--do something here
END
This is a bit confusing, but it basically checks which columns were updated and uses binary (I think) to denote which columns were actually changed. Read this MSDN article and let me know if you have specific questions.
source to share
An alternative to tracking these changes in your own schema is to configure the database to execute it. Starting in SQL Server 2008, enable change tracking at the database level . Change tracking is similar to an index in that it is maintained transparently by SQL Server. Once included in the spreadsheet, various change tracking functions are available that allow you to check what has changed and when.
source to share
GET IT! First, thanks to YOU for being posted here. You have been an amazing help in guiding me in the right direction. Looked at the code and saw my mistakes (restful sleep works wonders). Here's the solution:
CREATE TRIGGER dbo.updateComments
ON dbo.tbl_location_history
/*Check whether column comments has been updated. If column comments has
been changed, update column comments_datetime with getdate().*/
FOR INSERT,UPDATE AS
/*Use IF (COLUMNS_UPDATED() &7) = 7 to see whether column 7 was updated.*/
/*Checking out IF UPDATE(comments) for proper time stamp */
IF UPDATE(comments)
BEGIN
SET NOCOUNT ON;
UPDATE dbo.tbl_location_history
SET dbo.tbl_location_history.comments_datetime = GETDATE()
FROM dbo.tbl_location_history INNER JOIN inserted i ON dbo.tbl_location_history.location_id = i.location_id
END
source to share