February 8, 2012 at 3:37 am
Hi,
I use SQL 2000.
I have a master table in which i save Master details like Name,contactNo etc..
I have a trigger on this master table on INSERT and UPDATE. (in this tirgger i bifurcate contactNos into different categories, and save in other table.)
In normal condition Query and Trigger are working OK.
but when i fire a query like ( which updates bulk records in table ) :
"Update EmpMaster set contactNo ='12345678' where empid < 200"
On fire of above query error comes as under :
"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
In the trigger i have used Inserted and Deleted tables to check whether No. is updated or not.
Yes, there is subquery in my trigger.
Thnks.:angry:
February 8, 2012 at 3:52 am
Can you paste in your trigger please and SQL to create the representative tables and data however as a work around to your issue, this is a bit ineficcient but you could bring in results into a cursor so it updates them 1 row ata time. might be slow but will get round your issue for now
***The first step is always the hardest *******
February 8, 2012 at 4:02 am
The trigger is probably written assuming there's only a single row in inserted or deleted. Common problem with triggers. If you post the code, we can probably fix it.
Leave cursors until there's absolutely no other option.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 8, 2012 at 4:30 am
Here my Trigger Code :
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE TRIGGER [trg_EmpMasterBfNo] ON [dbo].[EmpMaster]
FOR INSERT, UPDATE
AS
IF UPDATE(CantactNo)
BEGIN
DECLARE @EntryNo varchar(50)
SET @EntryNo = (select EntryNo from inserted)
DECLARE @OldCantactNo varchar(50)
DECLARE @NewCantactNo varchar(50)
SELECT @OldCantactNo=d.CantactNo,@NewCantactNo=i.CantactNo FROM Inserted i INNER JOIN Deleted d ON i.EntryNo = d.EntryNo
if @OldCantactNo <>@NewCantactNo
BEGIN
update BfNos set CantactNo = right(CantactNo,10) where EntryNo =@EntryNo
END
END
February 8, 2012 at 4:54 am
Yup, that's written assuming that there is only ever 1 row in inserted (which is far from true)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 8, 2012 at 4:56 am
This should do the job
CREATE TRIGGER [trg_EmpMasterBfNo] ON [dbo].[EmpMaster]
FOR INSERT, UPDATE
AS
IF UPDATE(CantactNo)
BEGIN
IF EXISTS ( SELECT 1
FROM inserted
INNER JOIN deleted ON i.EntryNo = d.EntryNo
WHERE i.CantactNo != d.CantactNo )
BEGIN
UPDATE BfNos
SET CantactNo = RIGHT(CantactNo, 10)
WHERE EntryNo IN (
SELECT i.EntryNo
FROM inserted
INNER JOIN deleted ON i.EntryNo = d.EntryNo
WHERE i.CantactNo != d.CantactNo )
END
END
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 8, 2012 at 10:29 pm
First of all thanks for yr quick reply.
I updated following code in trigger :
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER TRIGGER [trg_EmpMasterBfNo] ON [dbo].[EmpMaster]
FOR INSERT, UPDATE
AS
IF UPDATE(ContactNo)
BEGIN
IF EXISTS ( SELECT 1 FROM inserted i INNER JOIN deleted d ON i.EntryNo = d.EntryNo WHERE i.ContactNo != d.ContactNo )
BEGIN
UPDATE EmpMasterSearch SET ContactNo = RIGHT(ContactNo, 10)
WHERE EntryNo IN ( SELECT i.EntryNo FROM inserted i INNER JOIN deleted d ON i.EntryNo = d.EntryNo WHERE i.ContactNo != d.ContactNo)
END
END
It gives No error & No result.
🙁
February 9, 2012 at 1:36 am
What do you mean by 'no results'?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply