While Loop

[allik7]

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

[ck9663]

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

[allik7]

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,@CustomerId entificationTyp e INT
DECLARE @CustomerIdenti ficationGroupCo de CHAR(50),@Docum entNumber CHAR(50)
DECLARE @FirstName CHAR(50),@LastN ame CHAR(50),@Middl eName CHAR(50)
DECLARE @CustomerName CHAR(50)
BEGIN
SET @CustomerCode = (select customercode from inserted)
SET @CustomerIdenti ficationType = (select CustomerIdentif icationType from inserted)
SET @CustomerIdenti ficationGroupCo de = (select CustomerIdentif icationGroupCod e 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(Custom erCode,Customer IdentificationT ype ,
CustomerIdentif icationGroupCod e,DocumentNumbe r,
FirstName,LastN ame,MiddleName,
CustomerName)
values(@Custome rCode,@Customer IdentificationT ype,@CustomerId entificationGro upCode,@Documen tNumber,
@FirstName,@Las tName,@MiddleNa me,@CustomerNam e)
end

SELECT CustomerCode FROM Customer where CustomerCode = @CustomerCode
INSERT INTO
SysDatabase_Tra nsactions( TableName,Opera tion,TransferDa teTime)
Select b.CustomerCode, 'Customer','I', Getdate()
From Inserted b
--END
ELSE
BEGIN
UPDATE Customer
SET CustomerCode = @CustomerCode,C ustomerIdentifi cationType = @CustomerIdenti ficationType,
CustomerIdentif icationGroupCod e = @CustomerIdenti ficationGroupCo de,
DocumentNumber = @DocumentNumber ,FirstName=@Fir stName,LastName =@LastName,
MiddleName=@Mid dleName,Custome rName=@Customer Name WHERE CustomerCode = @CustomerCode
--END

SELECT CustomerCode FROM Customer where CustomerCode = @CustNum
IF @@ROWCOUNT <> 0
INSERT INTO
SysDatabase_Tra nsactions( Record_Id,Table Name,Operation, TransferDateTim e)
Select b.CustomerCode, 'Customer','U', Getdate()
From Inserted b
END





GO
SET QUOTED_IDENTIFI ER OFF
GO
SET ANSI_NULLS ON
GO