|
|
Error Message
Server: Msg 334, Level 16, State 1, Line 1
The target table '<Table Name>' of the DML statement
cannot have any enabled triggers if the statement
contains an OUTPUT clause without INTO clause.
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, the results of an OUTPUT clause can also be captured in a nested INSERT, UPDATE, DELETE or MERGE statement, and insert those results into a target table or view.
The OUTPUT clause may be useful to retrieve the value of identity columns or computed columns after an INSERT or UPDATE operation. One restriction of the OUTPUT clause without also specifying the INTO keyword, as this error message suggests, is that the target of the DML operation, whether an INSERT, UPDATE, DELETE or MERGE operation, cannot have any enabled UPDATE triggers.
To illustrate on how this error can be encountered, given the following table structures and triggers:
CREATE TABLE [dbo].[Products] (
[ProductID] INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
[ProductName] NVARCHAR(100),
[UnitPrice] MONEY,
[Quantity] INT
)
GO
CREATE TABLE [dbo].[ProductAlert] (
[ProductAlertID] INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
[ProductID] INT,
[Quantity] INT,
[AlertDate] DATETIME DEFAULT(GETDATE())
)
GO
CREATE TRIGGER [dbo].[CheckQuantity] ON [dbo].[Products]
AFTER INSERT, UPDATE
AS
BEGIN
INSERT INTO [dbo].[ProductAlert] ( [ProductID], [Quantity] )
SELECT [ProductID], [Quantity]
FROM [inserted]
WHERE [Quantity] <= 20
END
GO
New and updated products are stored in the following table:
CREATE TABLE [dbo].[ProductUpdate] (
[ProductID] INT,
[ProductName] NVARCHAR(100),
[UnitPrice] MONEY,
[Quantity] INT
)
GO
When inserting new products to the [dbo].[Products] table, the system-generated product ID together with the product name, unit price and quantity needs to be returned to the calling application. In fulfilling this requirement, the following INSERT statement is executed which includes the OUTPUT clause without the INTO clause to return the product information of the newly inserted products together with the system assigned product IDs:
INSERT INTO [dbo].[Products] ( [ProductName], [UnitPrice], [Quantity] )
OUTPUT [inserted].[ProductID], [inserted].[ProductName],
[inserted].[UnitPrice], [inserted].[Quantity]
SELECT [ProductName], [UnitPrice], [Quantity]
FROM [dbo].[ProductUpdate] [ProdUpdate]
WHERE NOT EXISTS (SELECT 'X' FROM [dbo].[Products] [Prod]
WHERE [ProdUpdate].[ProductID] = [Prod].[ProductID])
GO
Unfortunately, since the target table, in this case the [dbo].[Products] table, has an enabled UPDATE trigger, the [dbo].[CheckQuantity] trigger, the following error is encounted when specifying the OUTPUT clause without the INTO clause:
Msg 334, Level 16, State 1, Line 2
The target table 'dbo.Products' of the DML statement cannot have any
enabled triggers if the statement contains an OUTPUT clause without INTO clause.
Solution / Work Around:
As mentioned earlier, and as the error message suggests, the target table of a DML statement, whether it be an INSERT, UPDATE, DELETE or MERGE statement, cannot have an enabled UPDATE trigger if the DML statement contains an OUTPUT clause without the INTO keyword.
To overcome this restriction, one thing that can be done is to insert the newly inserted products together with the system-assigned product IDs into a temporary table or a table variable first. Then after the INSERT statement, these new products can now be returned to the calling application.
The following script can be used to replace the INSERT statement earlier which inserts all newly inserted products together with the system-generated product IDs into a table variable, then returned to the calling application through a SELECT statement on that table variable:
DECLARE @NewProducts TABLE (
[ProductID] INT,
[ProductName] NVARCHAR(100),
[UnitPrice] MONEY,
[Quantity] INT
)
INSERT INTO [dbo].[Products] ( [ProductName], [UnitPrice], [Quantity] )
OUTPUT [inserted].[ProductID], [inserted].[ProductName],
[inserted].[UnitPrice], [inserted].[Quantity]
INTO @NewProducts
SELECT [ProductName], [UnitPrice], [Quantity]
FROM [dbo].[ProductUpdate] [ProdUpdate]
WHERE NOT EXISTS (SELECT 'X' FROM [dbo].[Products] [Prod]
WHERE [ProdUpdate].[ProductID] = [Prod].[ProductID])
SELECT * FROM @NewProducts
The first part of the script is the declaration of the table variable that will hold the newly inserted products together with the system-generated product IDs. This table variable is then used as the target table for the OUTPUT INTO clause of the INSERT statement. Lastly, all new products are now returned to the calling application through the SELECT statement on the table variable.
|