Statement contains an OUTPUT clause without INTO clause error

Question

I have a trigger that updates one of the inserted fields (RootId) with the value of the identity primary key (MessageId) of the same inserted record. The update should only happen when the RootId field of the inserted record is 0. The trigger looks like this:

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 gives me the following error:

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

The same trigger works on my real MS SQL 2012. I assume there is a difference in the settings but I do not get what it tries to tell me.

Am I overcomplicating something? Thanks!

Please show the statement that contains the `OUTPUT` clause. I've seen that error before on SQL Server 2008 and had to resort to `OUTPUT INTO @tablevar` then selecting from the table variable. — Martin Smith, Sep 21 '12 at 20:42
I use `OUTPUT INTO @tablevar` in 2008 R2 for defensive purposes as well (in the event someone creates a trigger) because of this error. I only OUTPUT primary keys, though, and then join on @tablevar when selecting because it didn't work with all datatypes, IIRC. — Tim Lehner, Sep 21 '12 at 20:43
The error is generated by the Azure/Silverlight table editor. I assume that when I edit a row there, Azure generates a T-SQL statement, which includes the OUTPUT without INTO. Using the C#/LINQ code to update a row works. — Miro J., Sep 23 '12 at 18:34
As a side note, I am not certain it will work correctly, i.e. inserted items containing multiple records if more than one row is inserted (insert into... select from elsewhere). Inserted will contain multiple records and may just fetch the first one into your variable (if it works at all). Rather join INSERTED and Messages to effect the update, — Anthony Horne, Apr 29 '14 at 09:27

score 0 · Answer 1 · answered May 24 '14 at 01:02

I think it may be related to what Anthony Horne wrote.

Does this work?

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

Statement contains an OUTPUT clause without INTO clause error

1 Answers1

Linked