SQL Server: Option to specify if update operation was associated with a trigger?
First message please be careful ...
I need to update one column when the table is updated or row (s) are inserted, so I created a trigger ( AFTER INSERT, UPDATE
). The problem is that it is recursive due to the fact that the insert includes an update statement, thus firing the trigger again.
I also tried to split INSERT
and UPDATE
into two different triggers, but I ran into a problem with sp_settriggerorder()
and trigger_nestlevel()
because there is another trigger in place, because of the default.
My question is, is there a way to use an IF clause to indicate if the update came from the application itself or from my trigger? Case if this is my trigger than I could easily SELECT IF it returned and it won't recursive anymore.
CREATE TRIGGER [dbo].[JobCardMetlInsertUpdateItemDesc]
ON [dbo].[JobCardMetl] AFTER INSERT
AS
BEGIN TRANSACTION [Description]
UPDATE JobCardMetl
SET JobCardMetl.Description = item.Description
FROM JobCardMetl
INNER JOIN item ON JobCardMetl.Item = item.item
WHERE JobCardMetl.RecordDate = (SELECT MAX(JobCardMetl.RecordDate)
FROM JobCardMetl)
COMMIT TRANSACTION [Description]
source to share
Your trigger is very suspicious: it does not refer to an alias INSERTED
. This means your trigger is updating INSERT-independent records, always a huge code smell.
The usual solution to the problem of recursive triggers is to be careful about which columns are updated, i.e. use UPDATED()
, and what lines, and the boolean logic should stop the recursion (i.e. the nested trigger shouldn't update anything because security checks aren't qualified).
Ultimately, you can use the logical sledgehammer: SET CONTEXT_INFO
and CONTEXT_INFO()
. You test it, set it, and clear it in your trigger. If already set, you know you are nested from a trigger. The cleaning part is critical. You also pray that no other / dev application does the same, as there is only one context information per session (SQL 2016 improves this).
source to share
You can check to see if the description is different from what you want to update. If the same, you don't update. This way you avoid endless recursion.
Also, assuming WHERE
you seem to want to restrict the update to the currently inserted record, but for that you can use a virtual table INSERTED
that has records that have been inserted.
Finally, it seems too difficult to start a new transaction for an atomic operator. Note that the trigger will be executed in the transaction in which the start statement is executed INSERT
.
So, taking it all together, you can make your trigger like this (I assume it RecordDate
uniquely identifies the record - change it to whatever primary key is):
CREATE TRIGGER [dbo].[JobCardMetlInsertUpdateItemDesc]
ON [dbo].[JobCardMetl] AFTER INSERT
AS
UPDATE JobCardMetl
SET j.Description = item.Description
FROM JobCardMetl j
INNER JOIN item ON j.Item = item.item
INNER JOIN INSERTED i ON i.RecordDate = j.RecordDate
WHERE j.Description IS NULL OR j.Description <> item.Description
source to share