By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,364 Members | 1,343 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,364 IT Pros & Developers. It's quick & easy.

While Loop

P: 10
Hi All

I need some help again. I am using a instead of insert to update and insert records. When I am doing multiple records i get am error

Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <=, <, >= or when the subquery is used as an
expression.

So i am thinking of using a cursor to do this.
Am i right in wanting to use a cursor or is there some other way in achieving this

I was using a table variable in the stored procedure

the insert statement in the store procedure looks like this

INSERT Customer
Select * FROM @Table
this then fires the instead on insert trigger

thanks in advance
Mar 20 '08 #1
Share this Question
Share on Google+
2 Replies


ck9663
Expert 2.5K+
P: 2,878
Hi All

I need some help again. I am using a instead of insert to update and insert records. When I am doing multiple records i get am error

Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <=, <, >= or when the subquery is used as an
expression.

So i am thinking of using a cursor to do this.
Am i right in wanting to use a cursor or is there some other way in achieving this

I was using a table variable in the stored procedure

the insert statement in the store procedure looks like this

INSERT Customer
Select * FROM @Table
this then fires the instead on insert trigger

thanks in advance

The error you're getting came from one of your subquery. This happens when you're expecting a subquery to return a single value but instead returns two or more. Since you did not post your code, I can't say if it's inside the trigger or outside.

-- CK
Mar 20 '08 #2

P: 10
This is the code in the instead of insert trigger

ALTER TRIGGER [Cust_Audit] ON [dbo].[Customer]
INSTEAD OF INSERT
AS

DECLARE @CustNum int
DECLARE @CustomerCode INT,@CustomerIdentificationType INT
DECLARE @CustomerIdentificationGroupCode CHAR(50),@DocumentNumber CHAR(50)
DECLARE @FirstName CHAR(50),@LastName CHAR(50),@MiddleName CHAR(50)
DECLARE @CustomerName CHAR(50)
BEGIN
SET @CustomerCode = (select customercode from inserted)
SET @CustomerIdentificationType = (select CustomerIdentificationType from inserted)
SET @CustomerIdentificationGroupCode = (select CustomerIdentificationGroupCode from inserted)
SET @DocumentNumber = (select DocumentNumber from inserted)
SET @FirstName = (select FirstName from inserted)
SET @LastName = (select LastName from inserted)
SET @MiddleName = (select MiddleName from inserted)
SET @CustomerName =(select CustomerName from inserted)
END
Set @CustNum =(Select top 1 cc.CustomerCode From Customer cc, inserted i WHERE cc.CustomerCode = i.CustomerCode)


--set nocount off

If ISNULL(@CustNum,'')=''

BEGIN
INSERT INTO CUSTOMER(CustomerCode,CustomerIdentificationType ,
CustomerIdentificationGroupCode,DocumentNumber,
FirstName,LastName,MiddleName,
CustomerName)
values(@CustomerCode,@CustomerIdentificationType,@ CustomerIdentificationGroupCode,@DocumentNumber,
@FirstName,@LastName,@MiddleName,@CustomerName)
end

SELECT CustomerCode FROM Customer where CustomerCode = @CustomerCode
INSERT INTO
SysDatabase_Transactions( TableName,Operation,TransferDateTime)
Select b.CustomerCode,'Customer','I',Getdate()
From Inserted b
--END
ELSE
BEGIN
UPDATE Customer
SET CustomerCode = @CustomerCode,CustomerIdentificationType = @CustomerIdentificationType,
CustomerIdentificationGroupCode = @CustomerIdentificationGroupCode,
DocumentNumber = @DocumentNumber,FirstName=@FirstName,LastName=@Las tName,
MiddleName=@MiddleName,CustomerName=@CustomerName WHERE CustomerCode = @CustomerCode
--END

SELECT CustomerCode FROM Customer where CustomerCode = @CustNum
IF @@ROWCOUNT <> 0
INSERT INTO
SysDatabase_Transactions( Record_Id,TableName,Operation,TransferDateTime)
Select b.CustomerCode,'Customer','U',Getdate()
From Inserted b
END





GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Mar 21 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.