Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : How to Get a List of User Views Within a Database
Error Messages
Home > SQL Server Error Messages > Msg 355 - The target table '<Table Name>' of the INSERT statement cannot have any enabled triggers when the FROM clause contains a nested INSERT, UPDATE, DELETE, or MERGE statement.
SQL Server Error Messages - Msg 355 - The target table '<Table Name>' of the INSERT statement cannot have any enabled triggers when the FROM clause contains a nested INSERT, UPDATE, DELETE, or MERGE statement.

Error Message

Server: Msg 355, Level 16, State 1, Line 1
The target table '<Table Name>' of the INSERT
statement cannot have any enabled triggers when the
FROM clause contains a nested INSERT, UPDATE, DELETE,
or MERGE statement.

Causes

The OUTPUT clause, introduced in SQL Server 2005, returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE or MERGE statement.

These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. The results can also be inserted into a table or table variable. Additionally, you can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view.

One of the restrictions when capturing the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE or MERGE statement and inserting those results into a target table, is that the target table cannot have any triggers enabled; otherwise this error message will be encountered.

To illustrate how this error is generated, given the following tables structures:

CREATE TABLE [dbo].[Student] (
    [StudentID]       INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
    [StudentName]     VARCHAR(100)
)
GO

CREATE TABLE [dbo].[Course] (
    [CourseCode]      VARCHAR(10) NOT NULL PRIMARY KEY,
    [CourseName]      VARCHAR(100)
)
GO

CREATE TABLE [dbo].[Teacher] (
    [TeacherID]       INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
    [TeacherName]     VARCHAR(100)
)
GO

CREATE TABLE [dbo].[StudentGrade] (
    [StudentID]       INT,
    [TeacherID]       INT,
    [CourseCode]      VARCHAR(10),
    [Grade]           INT
)
GO

ALTER TABLE [dbo].[StudentGrade]
ADD CONSTRAINT [FK_StudentGrade_Student]
FOREIGN KEY ( [StudentID] ) REFERENCES [dbo].[Student] ( [StudentID] )
GO

ALTER TABLE [dbo].[StudentGrade]
ADD CONSTRAINT [FK_StudentGrade_Course]
FOREIGN KEY ( [CourseCode] ) REFERENCES [dbo].[Course] ( [CourseCode] )
GO

ALTER TABLE [dbo].[StudentGrade]
ADD CONSTRAINT [FK_StudentGrade_Teacher]
FOREIGN KEY ( [TeacherID] ) REFERENCES [dbo].[Teacher] ( [TeacherID] )
GO

CREATE TABLE [dbo].[StudentGradeHistory] (
    [StudentID]             INT,
    [TeacherID]             INT,
    [CourseCode]            VARCHAR(10),
    [OldGrade]              INT,
    [NewGrade]              INT,
    [GradeChangeReason]     VARCHAR(1000)
)
GO

CREATE TABLE [dbo].[StudentGradeAlert] (
    [AlertID]        INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
    [StudentID]      INT,
    [TeacherID]      INT,
    [CourseCode]     VARCHAR(10),
    [AlertMessage]   VARCHAR(MAX),
    [AlertDate]      DATETIME
) 
GO

CREATE TRIGGER [dbo].[CheckGradeChange] ON [dbo].[StudentGradeHistory]
AFTER INSERT 
AS
    INSERT INTO [dbo].[StudentGradeAlert] ( [StudentID], [TeacherID], [CourseCode],
                                            [AlertMessage], [AlertDate] )
    SELECT [StudentID], [TeacherID], [CourseCode],
           'Grade increased by at least 10 points.', GETDATE()
    FROM [inserted]
    WHERE [NewGrade] - [OldGrade] >= 10
GO

The grades of students for MATH101 is being increased by 10 points for those students who have a grade of less than 70, with a maximum grade of 70, as they completed the extra credit assignment given to them. For each student grade increased, the old grade and new grade needs to be recorded in the student grade history table.

In fulfilling this requirement, the following statement, which uses a nested UPDATE statement, is executed.

INSERT INTO [dbo].[StudentGradeHistory] ( [StudentID], [TeacherID], [CourseCode], 
                                          [OldGrade], [NewGrade], [GradeChangeReason] )
SELECT A.[StudentID], A.[TeacherID], A.[CourseCode], A.[OldGrade],
       A.[NewGrade], 'Extra Assignment Submitted'
FROM (UPDATE [dbo].[StudentGrade]
      SET [Grade] = CASE WHEN [Grade] + 10 > 70
                         THEN 70
                         ELSE [Grade] + 10 END
      OUTPUT [inserted].[StudentID], [inserted].[TeacherID], [inserted].[CourseCode], 
             [deleted].[Grade] AS [OldGrade], [inserted].[Grade] AS [NewGrade]
      WHERE [Grade] < 70 AND
            [CourseCode] = 'MATH101') A

But since the target table, in this case the [dbo].[StudentGradeHistory] table, has an enabled trigger, in this case the [dbo].[CheckGradeChange] trigger which creates an alert message if the change in the grade is at least 10 points, the following error message is encountered:

Msg 355, Level 16, State 1, Line 1
The target table 'dbo.StudentGradeHistory' of the INSERT statement cannot have any enabled triggers
when the FROM clause contains a nested INSERT, UPDATE, DELETE, or MERGE statement.

Solution / Work Around:

As mentioned earlier, and as the message suggests, when capturing the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE or MERGE statement and inserting those results into a target table, that target table cannot have any enabled triggers. To overcome this restriction, one thing that can be done is to insert the updated student grades together with the old and new grades into a temporary table or a table variable. Then after the UPDATE statement, the updated student grades can now be inserted into the student grade history table.

The following script can be used to replace the UPDATE statement earlier which inserts all updated student grades together with their old and new grades after the UPDATE operation.

DECLARE @StudentGradeHistory TABLE (
    [StudentID]       INT,
    [TeacherID]       INT,
    [CourseCode]      VARCHAR(10),
    [OldGrade]        INT,
    [NewGrade]        INT
)

INSERT INTO @StudentGradeHistory ( [StudentID], [TeacherID], [CourseCode], [OldGrade], [NewGrade] )
SELECT A.[StudentID], A.[TeacherID], A.[CourseCode], A.[OldGrade], A.[NewGrade]
FROM (UPDATE [dbo].[StudentGrade]
      SET [Grade] = CASE WHEN [Grade] + 10 > 70
                         THEN 70
                         ELSE [Grade] + 10 END
      OUTPUT [inserted].[StudentID], [inserted].[TeacherID], [inserted].[CourseCode], 
             [deleted].[Grade] AS [OldGrade], [inserted].[Grade] AS [NewGrade]
      WHERE [Grade] < 70 AND
            [CourseCode] = 'MATH101') A

INSERT INTO [dbo].[StudentGradeHistory] ( [StudentID], [TeacherID], [CourseCode],
                                          [OldGrade], [NewGrade], [GradeChangeReason] )
SELECT [StudentID], [TeacherID], [CourseCode], [OldGrade], [NewGrade], 'Extra Assignment Submitted'
FROM @StudentGradeHistory

The first part of the script is the declaration of a table variable that will contain the student ID, teacher ID and course code of the student grades updated together with the old and new grade. This table variable is then used as the target table for in the OUTPUT INTO clause of the UPDATE statement.

Lastly, all updated student grades that are now in the table variable can now be inserted to the student grade history table without generating this error.

Related Articles :