Mssql Case When Statement Error

I'm working on trigger in MSSQL and i got this error message: Msg 512, Level 16, State 1, Procedure TirregerUpdate, Line 11 [Batch Start Line 0] Subquery returned more than 1 va

Solution 1:

While you didn't post the entire trigger code (and you really should have), the "Subquery returned more than 1 value..." error message, in triggers, usually (in over 90% of the cases I've seen) means that your trigger can't handle statements that effect multiple rows.

This is a very common mistake that almost everyone makes when writing their first couple of triggers (myself included).

In SQL Server, triggers are fired per statement, not per row. This means that the inserted and deleted pseudo tables may contain 0, 1, or multiple rows - and when writing triggers you must take that into consideration.

I'm willing to bet that somewhere in your trigger you have something like

SET@Variable= (SELECT ColumnName FROM Inserted)

This is usually the cause of the problem.