Error: 512 severity: 16 State: 1 trigger error Summary

SQL Server database problems, error description:Subquery returned more than 1 value. This is illegal when the subquery follows = ,! =, <, <=,>, >=, Or when the subquery is used as an expression.A subquery returns more than one value, which is not allowed when some operators or expressions are used.

The error is obvious. It is nothing more than replacing the operator with in to solve the problem.

In theory, this is the case, but the reality is cruel and it still doesn't work. This is depressing. After searching for half a day, the SQL event probe has been used for half a day, it was indeed an error on the trigger when the deletion occurred. No, I finally found the SQL statement that encountered the error and executed it in the query analyzer. Finally, I found the real culprit .....

Is the trigger problem. When the trigger is executed, another trigger is triggered. The root cause of the error is this, which is changed to in.

However, there is another problem. For example, when I delete a user, the trigger executes the delete all items and the delete product where pid = (select userid from deleted), The subquery return must be unique; when deleting an item, the delete item message table where id = (select PID from deleted) is also unique. Delete a user, obtain its ID from the trigger, and delete all its items based on the ID. In this way, when an item is deleted, the message is deleted by the item ID, it should be normal.

However, it is not normal. It is estimated that when we delete a user and delete multiple items of the user, all items are deleted. After the deleted table is filled, instead of deleting an item, filling in the deleted table once and triggering it once. In this way, the error "error 512" is returned when the message is deleted .......

Quiet much more ........