Subquery returned more than 1 value. Msg 512, level 16

Question

0.00/5 (No votes)

See more:

Hi,
I have a trigger and two tables as follows,
Source Table TInfo:

SQL

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:

SQL

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.

SQL

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:

SQL

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.

Posted 6-Jan-17 21:52pm

m.r.m.40

Updated 7-Jan-17 2:25am

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2017-01-07T02:25:00

As far as I can see the problem is that a single DELETE statement can delete multiple rows. In such case the trigger is fired only once and the deleted table contains one row for each deleted row. So when you fetch value from the deleted table you get many rows.

Here's a simplified test case

SQL

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

SQL

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

SQL

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