SQL Server触发器中的值比较:子查询返回了多个值

huangapple go评论79阅读模式
英文:

SQL Server trigger in value comparison: Subquery returned more than 1 value

问题

我有一个表格,我想在更新操作上添加触发器:

ALTER TRIGGER [dbo].[cmt_update]
ON [dbo].[comments] 
FOR UPDATE
AS 
    DECLARE @Cinfo VARBINARY(128) 

    SELECT @Cinfo = Context_Info() 

    IF @Cinfo = 0x55555 
        RETURN 

    -- 插入同步请求
    INSERT INTO syncros_requests (db_table_name, db_table_id,  action_type, request_date, syncro_status, syncro_msg, creation_date, usr,  edition_date, usr_edition)  
        SELECT 
            'comments', id, 'U', GETDATE(), 'NS', '', GETDATE(), usr, GETDATE(), usr 
        FROM 
            inserted

    IF (SELECT pendent FROM deleted) = 0 AND (SELECT pendent FROM inserted) = 1
    -- 插入同步请求
    INSERT INTO syncros_requests (db_table_name, db_table_id,  action_type, request_date, syncro_status, syncro_msg, creation_date, usr,  edition_date, usr_edition)  
    SELECT 'comments', id,  'D', GETDATE(), 'NS', '', GETDATE(), usr, GETDATE(), usr FROM inserted

问题是,当我一次更新多行时,会出现以下错误:

Msg 512, Level 16, State 1, Procedure cmt_update, Line 17 [Batch Start Line 8]
子查询返回多个值。当子查询跟随 =、!=、<、<=、>、>= 或子查询用作表达式时,不允许出现这种情况。

这个错误是指的这一行:

IF (SELECT pendent FROM deleted) = 0 AND (SELECT pendent FROM inserted) = 1

在搜索过程中,我发现SQL Server触发器是按语句执行的,而不是按行执行的,这导致了在更新多行时出现此错误。

我尝试了多种解决方案,比如这个,但它们都没有起作用:

IF (SELECT pendent FROM comments c INNER JOIN deleted d ON d.id = c.id) = 0 AND (SELECT pendent FROM comments c INNER JOIN inserted i ON i.id = c.id) = 1

我发现使用游标可能会有所帮助,但我无法编写正确的代码来使其工作,有人可以帮助我吗?谢谢!

英文:

I have a table where I want to have a trigger on update action:

ALTER TRIGGER [dbo].[cmt_update]
ON [dbo].[comments] 
FOR UPDATE
AS 
	DECLARE @Cinfo VARBINARY(128) 

	SELECT @Cinfo = Context_Info() 

	IF @Cinfo = 0x55555 
		RETURN 
	
	-- insert into syncro requests
	INSERT INTO syncros_requests (db_table_name, db_table_id,  action_type, request_date, syncro_status, syncro_msg, creation_date, usr,  edition_date, usr_edition)  
	    SELECT 
            &#39;comments&#39;, id, &#39;U&#39;, GETDATE(), &#39;NS&#39;, &#39;&#39;, GETDATE(), usr, GETDATE(), usr 
        FROM 
            inserted

	IF (select pendent from deleted) = 0 AND (select pendent from inserted) = 1
	-- insert into syncro requests
	insert into syncros_requests(db_table_name, db_table_id,  action_type, request_date, syncro_status, syncro_msg, creation_date, usr,  edition_date, usr_edition)  
	select &#39;comments&#39;, id,  &#39;D&#39;, GETDATE(), &#39;NS&#39;, &#39;&#39;, GETDATE(), usr, GETDATE(), usr from inserted

The problem is when I update multiple rows at once, it gives the following error:

> Msg 512, Level 16, State 1, Procedure cmt_update, Line 17 [Batch Start
> Line 8]
> Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

This error refers to this line:

IF (select pendent from deleted) = 0 AND (select pendent from inserted) = 1

While searching I've found that SQL Server trigger is executed by statement and not by row, causing this error only when I update multiple rows.

I have tried multiple solutions like this one but they didn't worked:

IF (select pendent from comments c inner join deleted d on d.id = c.id) = 0 AND (select pendent from comments c inner join inserted i on i.id = c.id) = 1

None of the solutions that I found where like my example, because trigger only breaks when I compare the values, because the code of both inserts are with the correct syntax.

I've found that using cursors may help but I'm not managing to make code right to work with it, can anyone help me? Thanks!

答案1

得分: 2

以下是翻译好的部分:

"Stab in the dark but...":

INSERT INTO syncros_requests (db_table_name, db_table_id, action_type, request_date, syncro_status, syncro_msg, creation_date, usr, edition_date, usr_edition)
SELECT 'comments',
       i.id,
       'D',
       GETDATE(),
       'NS',
       '',
       GETDATE(),
       i.usr,
       GETDATE(),
       i.usr
FROM inserted i
     JOIN deleted d ON i.YourIDColumn = d.YourIdColumn
WHERE i.pendent = 1
  AND d.pendent = 0;
英文:

Stab in the dark but...:

INSERT INTO syncros_requests (db_table_name,db_table_id,action_type,request_date,syncro_status,syncro_msg,creation_date,usr,edition_date,usr_edition)
SELECT &#39;comments&#39;,
       i.id,
       &#39;D&#39;,
       GETDATE(),
       &#39;NS&#39;,
       &#39;&#39;,
       GETDATE(),
       i.usr,
       GETDATE(),
       i.usr
FROM inserted i
     JOIN deleted d ON i.YourIDColumn = d.YourIdColumn
WHERE i.pendent = 1
  AND d.pendent = 0;

答案2

得分: -1

错误在这里:IF (select pendent from deleted) = 0 AND (select pendent from inserted) = 1
你需要使用像这样的方式:IF EXISTS(SELECT pendent FROM deleted WHERE pendent = 0) AND (SELECT pendent FROM inserted WHERE pendent = 0)

继续享受...

英文:

error is hare: IF (select pendent from deleted) = 0 AND (select pendent from inserted) = 1
you need to use like `IF exists(select pendent from deleted where pendent = 0) AND (select pendent from deleted where pendent = 0)

Keep enjoying...

huangapple
  • 本文由 发表于 2020年1月3日 17:56:25
  • 转载请务必保留本文链接:https://go.coder-hub.com/59576427.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定