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 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.

Also these are my sql codes:

Declare @NAME varchar(255) = 'ONE';
Declare @MKOD varchar(255);
Declare @OMKOD varchar(255);
DECLARE @STATUS BIT = 0;


IF @STATUS = 1
BEGIN
    SET @MKOD = CASE @NAME
        WHEN 'ONE' THEN '1'
        WHEN 'TWO' THEN '2'
        WHEN 'TRHEE' THEN '3'
    END
    SET @OMKOD = CASE @NAME
        WHEN 'ONE' THEN '1 - ONE'
        WHEN 'TWO' THEN '2 - TWO'
        WHEN 'TRHEE' THEN '3 - TRHEE'
    END
END
ELSE
BEGIN
    SET @MKOD = CASE @NAME
        WHEN 'ONE' THEN '1 - ONE'
        WHEN 'TWO' THEN '2 - TWO'
        WHEN 'TRHEE' THEN '3 - TRHEE'
    END
    SET @OMKOD = CASE @NAME
        WHEN 'ONE' THEN '1'
        WHEN 'TWO' THEN '2'
        WHEN 'TRHEE' THEN '3'
    END
END

UPDATE PRODUCT SET MKOD = @MKOD WHERE MKOD = @OMKOD;

Can you help me solving this situations. Thank you..

回答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.