子查询返回的值超过1。消息512，级别16 [英] Subquery returned more than 1 value. Msg 512, level 16
本文介绍了子查询返回的值超过1。消息512，级别16的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一个触发器和两个表如下，
源表TInfo ：
CREATE TABLE [dbo]。[TInfo]（
[F_Code] [Int_Positive] IDENTITY （ 1 ， 1 ） NOT FOR REPLICATION < span class =code-keyword> NOT NULL ，
[FirstName] [ varchar ]（ 256 ），
[LastName] [ varchar ]（ 256 ），
[Guid] [ uniqueidentifier ] NOT NULL
） ON [ PRIMARY ]
传输触发器TInfoDeleteTrigger 应该记录将从源表中删除的每一行：
CREATE TRIGGER TInfoDeleteTrigger ON [dbo]。[TArtikl]
AFTER DELETE
AS
BEGIN
DECLARE @ tempGUID UNIQUEIDENTIFIER
set @ tempGUID =（ SELECT GUID FROM DELETED）
INSERT INTO TInfo_Performance_Queue
（F_Cod，FirstName，LastName，Guid，用户名，操作， datetime ，
editionno） - - 两个版本号变量
SELECT
F_Cod，FirstName，LastName，Guid， System_user ，' 从TInfo'删除行，GetDate（），
（ SELECT dbo.GetMaxEditionNo（ @ tempGUID ））
FROM DELETED
END
函数GetMaxEditionNo 应该读取的内容记录的最大版本号：
版本号的方案是知道通过应用程序更新记录的时间，我还有其他操作的触发器但问题在于删除操作。
C REATE FUNCTION dbo.GetMaxEditionNo（ @ GUID UNIQUEIDENTIFIER ）
RETURNS INT AS
BEGIN
DECLARE @ EditionNo int
SET @ EditionNo =（ SELECT MAX（editionno） FROM TInfo_Performance_Queue WHERE TInfo_Performance_Queue.GUID = @ GUID ）
RETURN @ EditionNo
END
目标表TInfo_Performa nce_Queu：
CREATE TABLE [dbo]。[TInfo_Performance_Queue]（
[F_Cod] [ bigint ] NULL ，
[FirstName] [ varchar ]（ 256 ），
[LastName] [ varchar ]（ 256 ）
[Guid] [ uniqueidentifier ] NULL ，
[用户名] [ nvarchar ]（ 256 ） COLLATE Arabic_CI_AS NULL ，
[operation] [ nvarchar ]（ 256 ） COLLATE Arabic_CI_AS NULL ，
[ datetime ] [ datetime ] NULL ，
[editionno] [ int ] NULL
）
如果我手动删除一行它没关系，一切都按照我的预期进行，但是如果我尝试使用行的GUID删除一行，则触发器会因以下错误而停止：
从TInfo中删除Guid ='{0B08A4F5-13A9-4417-992A-312B0F6CC310}'
服务器：消息512，级别16，状态1，过程TInfoDeleteTrigger，第7行
子查询返回的值超过1。当子查询遵循=，！=，<，< =，>，> =或子查询用作表达式时，不允许这样做。
声明已被终止。
我找不到该来源错误，但还不能解决。
我尝试过的事情：
我正在阅读可能的方法来解决它，但我无法修复它。
解决方案
据我所知，问题是单个DELETE语句可以删除多行。在这种情况下，触发器只被触发一次，已删除表包含每个已删除行的一行。因此，当您从已删除的表中获取值时，您将获得许多行。
这是一个简化的测试用例
CREATE TABLE TriggerTest（
col1 int
）;
CREATE TRIGGER trg_TriggerTest_Delete ON TriggerTest
AFTER DELETE AS
BEGIN
DECLARE @ col1 INT ;
SET @ col1 =（ SELECT col1 FROM DELETED）;
PRINT @ col1 ;
END ;
- 添加五行
INSERT INTO TriggerTest（col1）值
（< span class =code-digit> 1 ），（ 2 ），（ 3 ），（ 4 ），（ 5 ）
- 删除单行成功
DELETE < span class =code-keyword> FROM TriggerTest WHERE col1 = 2 ;
- 删除多行失败
DELETE FROM TriggerTest WHERE col1 IN （ 3 ， 4 ）;
最后一次删除返回
Msg 512，Level 16，State 1，Procedure trg_TriggerTest_Delete，Line 5 [Batch Start Line 20]
子查询返回的值超过1。当子查询遵循=，！=，<，< =，>，> =或子查询用作表达式时，不允许这样做。
作为修复，我会首先尝试删除变量。如果您根据删除的行选择数据，您是否可以简单地使用
CREATE TRIGGER TInfoDeleteTrigger ON [dbo]。[TArtikl]
AFTER DELETE
AS
BEGIN
INSERT INTO TInfo_Performance_Queue
（F_Cod，FirstName，LastName，Guid ，用户名，操作， datetime ，editionno）
- - 两个版本号变量
SELECT
F_Cod，FirstName，LastName，Guid， System_user ，
' 从中删除行TInfo'，GetDate（），
dbo.GetMaxEditionNo（deleted.Guid）
FROM DELETED
END
[已添加]
另一个例子包括guid列
CREATE TABLE TriggerTest（
col1 int ，
Guidcol uniqueidentifier 默认 newid（）
）;
CREATE TRIGGER trg_TriggerTest_Delete ON TriggerTest
AFTER DELETE AS
BEGIN
SELECT deleted.guidcol 来自已删除;
END ;
- 添加五行
INSERT INTO TriggerTest（col1）值
（< span class =code-digit> 1 ），（ 2 ），（ 3 ），（ 4 ），（ 5 ）
< span class =code-comment> - 删除几行
DELETE FROM TriggerTest WHERE col1 IN （< span class =code-digit> 3 ， 4 ）;
运行删除产生输出如
guidcol
-------
4F1D6049-0558-4D2F-A81A-E207D8AC1795
F6B88232-3326- 4F7C-8D36-64ED9F3CC826
Hi,
I have a trigger and two tables as follows,
Source Table TInfo:
CREATE TABLE [dbo].[TInfo] (
[F_Code] [Int_Positive] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[FirstName] [varchar] (256),
[LastName] [varchar] (256),
[Guid] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
Transfering Trigger TInfoDeleteTrigger Which is supposed to record every rows that will be deleted from the source table:
CREATE TRIGGER TInfoDeleteTrigger ON [dbo].[TArtikl]
AFTER DELETE
AS
BEGIN
DECLARE @tempGUID UNIQUEIDENTIFIER
set @tempGUID = (SELECT GUID FROM DELETED)
INSERT INTO TInfo_Performance_Queue
(F_Cod, FirstName, LastName, Guid, username, operation, datetime,
editionno)---two edition number variables
SELECT
F_Cod, FirstName, LastName, Guid, System_user, 'Delete row from TInfo', GetDate(),
(SELECT dbo.GetMaxEditionNo(@tempGUID))
FROM DELETED
END
Function GetMaxEditionNo Which is supposed to read the maximum edition number of the record:
The scenario of the edition number is to know how many time a record has beed updated through the application, I also have triggers for other operations but the problem is with the delete operation.
CREATE FUNCTION dbo.GetMaxEditionNo(@GUID UNIQUEIDENTIFIER)
RETURNS INT AS
BEGIN
DECLARE @EditionNo int
SET @EditionNo = (SELECT MAX(editionno) FROM TInfo_Performance_Queue WHERE TInfo_Performance_Queue.GUID = @GUID)
RETURN @EditionNo
END
The Destination Table TInfo_Performance_Queu:
CREATE TABLE [dbo].[TInfo_Performance_Queue] (
[F_Cod] [bigint] NULL ,
[FirstName] [varchar] (256),
[LastName] [varchar] (256)
[Guid] [uniqueidentifier] NULL ,
[username] [nvarchar] (256) COLLATE Arabic_CI_AS NULL ,
[operation] [nvarchar] (256) COLLATE Arabic_CI_AS NULL ,
[datetime] [datetime] NULL ,
[editionno] [int] NULL
)
If I delete a row manually It's quite alright and everything goes as I expect it, but if I try to delete a row using the GUID of the row the trigger stops by the following error:
Delete from TInfo where Guid = '{0B08A4F5-13A9-4417-992A-312B0F6CC310}'
Server: Msg 512, Level 16, State 1, Procedure TInfoDeleteTrigger, Line 7 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.
I can't find the source of the error and cannot solve it yet.
What I have tried:
Well I'm reading possible ways to solve it but yet I can't fix it.
解决方案
As far as I can see the problem is that a singleDELETEstatement can delete multiple rows. In such case the trigger is fired only once and thedeletedtable contains one row for each deleted row. So when you fetch value from thedeletedtable you get many rows.
Here's a simplified test case
CREATE TABLE TriggerTest ( col1 int ); CREATE TRIGGER trg_TriggerTest_Delete ON TriggerTest AFTER DELETE AS BEGIN DECLARE @col1 int; SET @col1 = (SELECT col1 FROM DELETED); PRINT @col1; END; -- add five rows INSERT INTO TriggerTest (col1) values (1), (2), (3), (4), (5) -- deleting a single row succeeds DELETE FROM TriggerTest WHERE col1 = 2; -- deleting multiple rows fails DELETE FROM TriggerTest WHERE col1 IN (3,4);
The last delete returns with
Msg 512, Level 16, State 1, Procedure trg_TriggerTest_Delete, Line 5 [Batch Start Line 20] Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
As a fix I would try to remove the variable in the first place. If you're selecting data based on the deleted rows, could you simply have something like
CREATE TRIGGER TInfoDeleteTrigger ON [dbo].[TArtikl] AFTER DELETE AS BEGIN INSERT INTO TInfo_Performance_Queue (F_Cod, FirstName, LastName, Guid, username, operation, datetime, editionno) ---two edition number variables SELECT F_Cod, FirstName, LastName, Guid, System_user, 'Delete row from TInfo', GetDate(), dbo.GetMaxEditionNo(deleted.Guid) FROM DELETED END
[ADDED]
Another example including guid column
CREATE TABLE TriggerTest ( col1 int, Guidcol uniqueidentifier default newid() ); CREATE TRIGGER trg_TriggerTest_Delete ON TriggerTest AFTER DELETE AS BEGIN SELECT deleted.guidcol from deleted; END; -- add five rows INSERT INTO TriggerTest (col1) values (1), (2), (3), (4), (5) -- delete few rows DELETE FROM TriggerTest WHERE col1 IN (3,4);
Running the delete produces output like
guidcol ------- 4F1D6049-0558-4D2F-A81A-E207D8AC1795 F6B88232-3326-4F7C-8D36-64ED9F3CC826
这篇关于子查询返回的值超过1。消息512，级别16的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
