Date Time Stamp update
-
The trigger below works perfectly on update of (any field) in the table. Is it possible to restrict the update of the dateOfLastUpdate to only after the update of a particular field Day1 (which is a checkbox)?
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
-
You can use the COLUMNS_UPDATED() function to determine if a column has been updated however this simply tells you that a SET operation has been carried out against the column, not whether the update causes the column to have a new value.
To limit the trigger to specific fields you would have to have a WHERE clause to your trigger or exotic join to your trigger.
WHERE inserted.mycolumn FileUpload.mycolumn
-
Please provide full sql trigger code.
I am trying this for the last line
WHERE inserted ON FileUpload.Day1<>FileUpload.Day1
but getting the error message:'Incorrect syntax near the keyword 'ON'
-
I think something is missing from previous suggestions. In an update trigger, there are three tables available to you:
1) the table itself
2) the 'inserted' table and
3) the 'deleted' table.
The inserted table holds the new values of rows affected by the update and the deleted table holds the old values of the rows affected. Therefore there is no point in using the inserted table to see if a value has changed as this is exactly the same as the value in the actual table.
You will need to use the deleted table - holding the old values - rather than the inserted table that tells you nothing you need to know.
E.g.
UPDATE FileUpload
SET dateOfLastUpdate = GETDATE()
FROM FileUpload
JOIN deleted ON FileUpload.primaryKey = deleted.primaryKey
AND FileUpload.Day1 <> deleted.Day1
This only updates the date for rows where the Day1 value (i.e. your checkbox) has been altered.
-
X
-
The following should work:
(In this case, the BEGIN..END is not required, but I've used it here because I think it makes the code more readable):
-----------------------------------------------------
CREATE TRIGGER tru_FileUpload ON dbo.FileUpload
FOR UPDATE
AS
IF UPDATE(Day1)
BEGIN
UPDATE FileUpload
SET dateOfLastUpdate = GETDATE()
FROM FileUpload
JOIN inserted ON FileUpload.primaryKey = inserted.primaryKey
END
GO
-
The "IF UPDATE" syntax will not help in this case as the value may not have changed but the column may have been included in the update statement.
For example, if you code
UPDATE table SET col1 = col1
then the "IF UPDATE col1" in the trigger will be true even though the value hasn't changed.
You must use the deleted table to determine what the value was before the update took place. It's the only way
-
"there are three tables available to you":
Actually, all tables are available, plus the inserted and updated tables.
"You must use the deleted table to determine what the value was before the update took place. It's the only way"
Word...
And not be be picky, you need to decide how to include/exclude null updates, since "<>" is false if either are null.
For example, to include all updates (including "to and from null" but not "null to null"):
UPDATE FileUpload
SET dateOfLastUpdate = GETDATE()
FROM FileUpload
JOIN deleted ON FileUpload.primaryKey = deleted.primaryKey
WHERE
(deleted.Day1 is null and FileUpload.Day1 is not null)
or (FileUpload.Day1 is null and deleted.Day1 is not null)
or FileUpload.Day1 <> deleted.Day1
Signature is NULL
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply