In this blog post, let’s learn about the error message “The target table ‘%.*ls’ of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.” in Microsoft SQL Server, the reason why it appears and the solution to fix it.
SQL Server Error Message
The target table ‘%.*ls’ of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause. – 16
Reason for the Error and Solution
SQL Server Error Msg 334 is encountered when a Data Manipulation Language (DML) statement, such as INSERT, UPDATE, or DELETE, includes an OUTPUT clause without an INTO clause, and the target table of the statement has an enabled trigger. This error occurs because when an OUTPUT clause is used without an INTO clause, the changes made by the DML statement are directly returned to the client, which can interfere with the operation of the trigger.
Here is an example of a DML statement that could trigger this error:
DELETE FROM MyTable OUTPUT DELETED.* WHERE Column1 = 'Value1'
By adding the INTO clause, the changes made by the DELETE statement are returned to the @DeletedRows table variable instead of directly to the client. This allows the trigger to function properly without interference.
- Disabling the trigger on the target table
DISABLE TRIGGER MyTrigger ON MyTable; DELETE FROM MyTable WHERE Column1 = 'Value1'; ENABLE TRIGGER MyTrigger ON MyTable;
By disabling the trigger on the target table, the DML statement can be executed without interference from the trigger. Once the statement is complete, the trigger can be re-enabled.
It’s important to note that disabling a trigger can have other consequences, such as temporarily disabling other business logic that may depend on the trigger. So, if possible, it’s recommended to modify the DML statement to include an INTO clause instead of disabling the trigger.
