Trigger After Update to Update the Updated Table

Question

Trigger After Update to Update the Updated Table

Dennis Jensen

SSChasing Mays

Points: 606
More actions
December 8, 2024 at 7:04 am

#4494892
Okay I can easily see how to update a table field after an Insert but do I use the system Inserted when creating a trigger to update a field in the table that was just updated and do I use AFTER UPDATE or FOR UPDATE
```
CREATE TRIGGER [dbo].[TG_ResolvedDate_I]
    ON [dbo].[Issues]
 AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE [dbo].[Issues]
       SET [ResolvedDate] = GETDATE()
     WHERE 1 IN ( SELECT [Resolved]
                    FROM Inserted
                );
END
```
Again I am not exactly sure how to write this as I have not actually done one of these before but basically what I am doing is saying that if someone has updated the Resolved from 0 to a 1 meaning it has been resolved I want to update the resolved date to the current datetime. Better it would be nice to set ResolvedDate to NULL if the value of Resolved = 0.
Further is this a quality way to handle this or is there perhaps a better way to do this.
Thanks in advance.
- This topic was modified 3 days, 16 hours ago by Dennis Jensen.
- This topic was modified 3 days, 16 hours ago by Dennis Jensen.

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

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

Steve Collins SSCrazy Points: 2167 More actions · Answer 1

Dennis Jensen wrote:

Okay I can easily see how to update a table field after an Insert but do I use the system Inserted when creating a trigger to update a field in the table that was just updated and do I use AFTER UPDATE or FOR UPDATE

Hi Dennis. From the Docs the 2 types of triggers to choose from are AFTER/FOR and INSTEAD OF. In this case AFTER/FOR seems appropriate. Afaik and imo INSTEAD OF triggers are for when there are foreign key prerequisites to an INSERT/DELETE. There are probably other uses for it as well...

Dennis Jensen wrote:

Again I am not exactly sure how to write this as I have not actually done one of these before but basically what I am doing is saying that if someone has updated the Resolved from 0 to a 1 meaning it has been resolved I want to update the resolved date to the current datetime. Better it would be nice to set ResolvedDate to NULL if the value of Resolved = 0.

Presumably there's an "issue id" which could be JOIN'ed to the INSERTED/DELETED tables? One issue with triggers is they run whether or not the situation you're interested in handling specifically applies. Said another way, if the Resolved value in the table for a particular "issue id" is UPDATED from 1 to 1 (same value) the trigger will still execute. To make sure your code only runs when an actual change has been made to the data, imo the TRIGGER only needs to reference the DELETED table. This can sometimes seem a little convoluted. The TRIGGER is executed AFTER the UPDATE to the table has already occurred, as a result the value in the table is the updated "new" value and if it's different then the DELETED value, a change in Resolved status actually happened

drop table if exists dbo.test_t;
go
create table dbo.test_t (
    IssueId         int primary key not null,
    Resolved        int null,
    ResolvedDate    datetime null);

insert dbo.test_t(IssueId, Resolved) values(1, 0);
go

create trigger [dbo].[TG_ResolvedDate_I] on dbo.test_t
after update
as
set nocount on;

update tt
set [ResolvedDate] = iif(d.Resolved=0, getdate(), null)
from dbo.test_t tt
    join deleted d on tt.IssueId=d.IssueId
                       and tt.Resolved<>d.Resolved;
go

select * from dbo.test_t;
update dbo.test_t set Resolved=1 where IssueId=1;
select * from dbo.test_t;
update dbo.test_t set Resolved=0 where IssueId=1;
select * from dbo.test_t;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

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

EDIT: Looks like Steve posted while I was working on replies. Leaving this reply here even though it overlaps with Steve's pose.

FOR and AFTER are identical in meaning.

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql#arguments

You're trigger is incorrect though. It appears to me that it will update ALL rows in the dbo.Issues table if the "Resolved" column of ANY row in the inserted table is updated to "1".

You need to do a JOINed UPDATE between dbo.Issues and the INSERTED table using the Primary Key columns and the correct criteria to update only those rows that actually have a "1" in the Resolved Column of the INSERTED table. You might also want to add a criteria where the related Resolved Column in the dbo.Issues table contains a "0" so that you don't update previously updated rows when the Resolved Column wasn't actually updated.

--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)

Steve Collins SSCrazy Points: 2167 More actions · Answer 3

Jeff Moden wrote:

<b>...</b> the correct criteria to update only those rows that actually have a "1" in the Resolved Column of the INSERTED table.

Ah ok, this is more correct. My code relied on implication but maybe not proper affirmation. To make the code more strictly correct the UPDATE conditional could reference the existing row value of the 'Resolved' column

update tt
set [ResolvedDate] = iif(tt.Resolved=1, getdate(), null)
from dbo.test_t tt
    join deleted d on tt.IssueId=d.IssueId
                       and tt.Resolved<>d.Resolved;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

ScottPletcher SSC Guru Points: 101115 More actions · Answer 4

AFTER UPDATE is the more current syntax, so I would go with that.

I would also (1) take advantage of the UPDATE() function and (2) allow the trigger to properly set the value for an INSERT as well as a later UPDATE, like below:

CREATE TRIGGER dbo.TG_ResolvedDate_I
ON dbo.Issues
AFTER UPDATE
AS
SET NOCOUNT ON;

IF UPDATE(Resolved)
BEGIN
    UPDATE I1
    SET ResolvedDate = CASE WHEN Resolved = 1 THEN GETDATE() ELSE NULL END
    FROM dbo.Issues I1
    INNER JOIN inserted i2 ON i2.IssueId = I1.Issued_Id 
    LEFT OUTER JOIN deleted d ON d.IssueId = i.IssueId AND (d.Resolved IS NULL OR d.Resolved <> i2.Resolved)
END /*IF*/
/*end of trigger*/
GO

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".