英文:
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
'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 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 'comments', id, 'D', GETDATE(), 'NS', '', 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 '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;
答案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...
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论