Conditional trigger on column update

Question

Conditional trigger on column update

joe-584802

SSC Eights!

Points: 949
More actions
June 30, 2019 at 1:24 pm

#3658725
Hi Guys,
I need a trigger to update a datetime field when an update has happened to a specific column.
I have the following bit of code that does it for any column.
```
Create TRIGGER [dbo].[updateDateTime]
ON [dbo].[tblFuelRejection]
AFTER UPDATE 
AS UPDATE tblFuelRejection SET DateTimeStamp = GetDate()
      FROM tblFuelRejection t
    INNER JOIN inserted i
  ON t.FuelFlowID = i.FuelFlowID
```
I only want the trigger to work if a specific column has been updated.
The column in question is called Rejected and it must have a value greater than 0.
Can someone point me in the right direction with this please?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

joe-584802 SSC Eights! Points: 949 More actions · Answer 1

I've been having a play again and have come up with the following trigger which seems to work.

CRAETE TRIGGER [dbo].[updateDateTime]
ON [dbo].[tblFuelRejection]
AFTER UPDATE 
AS UPDATE tblFuelRejection 
SET DateTimeStamp = GetDate()
WHERE FuelFlowID IN (SELECT FuelFlowID FROM Inserted) AND Rejected>0

Is this the best way to do this kind of thing?

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 2

I believe that the UPDATE() function can help you a lot here. For example, if the UPDATE() function is NOT true, you can shortcut the trigger with an early exit. Please see the following documentation on the function...

https://docs.microsoft.com/en-us/sql/t-sql/functions/update-trigger-functions-transact-sql?view=sql-server-2017

No matter which method you use, you also need to remember that a column will register as being "updated" even if it's updated to all of the same values that currently exist. If you only want to update the DateTimeStamp if there was an actual data change in the "specific column", you'll need to compare the values for that column in the INSERTED and DELETED logical tables of the trigger.

Personally, I dislike this type of "tracking" or "simple auditing" especially if there's an associated column that keeps track of WHO made the change, which is almost always a guaranteed "ExpAnsive" UPDATE that can and will cause more fragmentation than you can shake a stick at even if the Clustered Index is otherwise perfectly "Ever Increasing" (which should be fragmentation proof).

If you're going to audit, do it right.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

joe-584802 SSC Eights! Points: 949 More actions · Answer 3

Thanks Jeff for your time and help in answering, I will experiment a bit with this technique!

This reply was modified 5 years, 6 months ago by joe-584802.