SQL Trigger after insert update another table

Question

I want to update an entity in a table after a insert is done in another table. Here my current trigger which doesn't work.

ALTER TRIGGER [dbo].[UpdateLastValues]  
ON [dbo].[MeasureValues] FOR INSERT   
AS BEGIN

SET NOCOUNT ON;

DECLARE @MyValue AS nvarchar;
DECLARE @MyTimestamp AS datetimeoffset;
DECLARE @MyId AS nvarchar;

SELECT @MyValue = Value FROM INSERTED;
SELECT @MyTimestamp = Timestamp FROM INSERTED;
SELECT @MyId = MeasurePointId FROM INSERTED;

UPDATE [dbo].[MeasurePoints] SET  [dbo].[MeasurePoints].[LastValue] = @MyValue, [dbo].[MeasurePoints].[LastEdit] = @MyTimestamp WHERE [dbo].[MeasurePoints].[Id] = @MyId
END

Measure Values receive new values. The latest values should be saved in MeasurePoints LastValue Column.

"doesn't work" is not an error message or a useful problem statement. Please tell us exactly what happens - e.g. do you get an error, or does it not produce the expected result? It might help to give us an example of the input data, and then what the desired outcome should be. — ADyson
– ADyson, Commented Dec 21, 2018 at 9:07
Your trigger has a major flaw here, it assumes that your table will only ever have 1 row inserted at a time. — Thom A
– Thom A ♦, Commented Dec 21, 2018 at 9:09

Jayasurya Satheesh · Accepted Answer · 2018-12-21 09:11:04Z

4

Change the Trigger from FOR INSERT to AFTER INSERT.

Also, I have made some changes in the Code to remove the Unnecessary codes

ALTER TRIGGER [dbo].[UpdateLastValues]  
ON [dbo].[MeasureValues] AFTER INSERT   
AS BEGIN

SET NOCOUNT ON;

UPDATE MP
    SET
        LastValue = INSERTED.[Value],
        LastEdit = INSERTED.[Timestamp]
    FROM [dbo].[MeasurePoints] MP
        INNER JOIN Inserted
            ON MP.id = Inserted.MeasurePointId

END

answered Dec 21, 2018 at 9:11

Jayasurya Satheesh

8,0733 gold badges25 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thom A Over a year ago

FOR and AFTER are the same in SQL Server, so changing FOR to AFTER makes no difference (See FOR | AFTER in CREATE TRIGGER (Transact-SQL)). Changing the trigger to a dataset approach though, now that makes a big difference. :)

Collectives™ on Stack Overflow

SQL Trigger after insert update another table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related