The instruction contains an OUTPUT clause without an INTO clause.

Question

I have a trigger that updates one of the inserted fields (RootId) with the value of the primary identification key (MessageId) of the same inserted record. Updating should occur only when the RootId field of the inserted record is 0. The trigger is as follows:

ALTER TRIGGER [dbo].[Trigger_RootIdUpdate] ON [dbo].[Messages] AFTER INSERT AS BEGIN SET NOCOUNT ON; DECLARE @MessageId Int SELECT @MessageId = I.MessageId FROM INSERTED I UPDATE Messages SET RootId = @MessageId WHERE MessageId = @MessageId AND MessageId = 0 END 

I use it in Azure, and when I insert a row, it causes the following error:

The target table 'dbo.Messagess' of the DML statement cannot if the statement contains an OUTPUT clause without an INTO.

The same trigger works on my real MS SQL 2012. I assume that there is a difference in settings, but I don’t understand what it is trying to tell me.

Am I embarrassing something? Thank!

Answer 1

I think this may be due to what Anthony Horn wrote.

It works?

 ALTER TRIGGER [dbo].[Trigger_RootIdUpdate] ON [dbo].[Messages] AFTER INSERT AS BEGIN UPDATE Messages SET Messages.RootId = INSERTED.MessageId FROM Messages Msg JOIN INSERTED ON Msg.MessageId = INSERTED.MessageId WHERE Msg.MessageId = INSERTED.MessageId AND INSERTED.RootId = 0; END