语句错误时的MSSQL案例 [英] MSSQL Case When Statement Error

问题描述

我正在使用MSSQL触发，并且收到以下错误消息：

I'm working on trigger in MSSQL and i got this error message:

Msg 512，级别16，状态1，过程TirregerUpdate，第11行[批处理开始第0行]

子查询返回的值超过1。当子查询遵循=，！=，<，< =，>，> =或将子查询用作表达式时，不允许这样做。
语句已终止。

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.

这些也是我的SQL代码：

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

推荐答案

虽然您没有发布完整的触发器代码（您确实应该发布），但是
子查询返回了多个值... 错误消息，在触发器中，通常（在我所见过的案例中，有90％以上）意味着您的触发器无法处理以下语句影响多行。

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).

在SQL Server中，触发器是按语句而不是每行触发的。

这意味着插入和删除的伪表可能包含0、1或多行-在编写触发器时，您必须采用

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)

通常问题的原因。

这篇关于语句错误时的MSSQL案例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！