Trigger - Subquery returned more than 1 value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Trigger - Subquery returned more than 1 value

Author

Topic

i386
Starting Member

4 Posts

Posted - 2006-04-18 : 07:14:08

Hi,

I have a trigger on an SQL 2000 table. This is my first time writing SQL trigger.

When I update many records (5,000) on the 'Catalog' using a simple query: UPDATE Catalog SET thisfield=avalue, I recieve a error message in the Query Analyzer as follows

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.

The problem starts from the SELECT @DISCONTIUNED = (SELECT Discontinued FROM inserted) in my trigger.

This is my SQL Script below:

/**** Trigger We do AFTER UPDATE as there is already a trigger on the Catalog so this handle INSERTS and UPDATES nicely. ***/
CREATE TRIGGER [tgi386InventoryUpdate] ON [dbo].[Catalog]
AFTER UPDATE
AS
DECLARE @DISCONTIUNED SMALLDATETIME
SELECT @DISCONTIUNED = (SELECT Discontinued FROM inserted)
DECLARE @Pk VARCHAR(100)
SELECT @Pk = (SELECT apkCatalogID FROM inserted)
DECLARE @ManuCode VARCHAR(50)
SELECT @ManuCode = (SELECT ManufacturerCode FROM inserted)

/****** Only Insert into change table if certain fields are updated, otherwise hand back control to SQL Server ******/
IF NOT UPDATE(apkCatalogID) AND NOT UPDATE(CatalogName) AND NOT UPDATE(CostStandard) AND NOT UPDATE(PriceLevel1Amt) AND NOT UPDATE(OverridePriceRetail) AND NOT UPDATE(ManufacturerCode) AND NOT UPDATE(WebDesc) AND NOT UPDATE(StandardDesc) AND NOT UPDATE(fkCategoryDefault) AND NOT UPDATE(CatalogView)
BEGIN
RETURN
END
BEGIN
DECLARE @CategoryName VARCHAR(100)

DECLARE @CategoryID BIGINT
SELECT @CategoryID = (SELECT fkCategoryDefault FROM inserted)
SELECT @CategoryName =(SELECT [Name] FROM dbo.Category WHERE apkCategoryID=@CategoryID)

IF EXISTS (SELECT pk FROM inserted JOIN i386InventoryChanges ON inserted.apkCatalogID=i386InventoryChanges.pk WHERE pk=@Pk)
BEGIN
PRINT 'Already exists so delete it and add it back with the new values into i386InventoryChanges ' + @Pk
DELETE i386InventoryChanges WHERE pk=@Pk

END
PRINT 'Insert into i386InventoryChanges'
/* Local, central, expected stock values */
DECLARE @Local BIGINT
DECLARE @Central BIGINT
DECLARE @Expected SMALLDATETIME
DECLARE @ListPrice MONEY
SELECT @Local = (SELECT stock FROM vi386InventoryLocalStock WHERE pk=@pk)
SELECT @Central = (SELECT onorder FROM vi386InventoryCentralStock WHERE pk=@pk)
SELECT @Expected = (SELECT expected FROM vi386InventoryCentralStock WHERE pk=@pk)
SELECT @ListPrice = (SELECT centralprice FROM vi386InventoryCentralStock WHERE pk=@pk)
/** IF @Local and @Central are Zero we need to remove the item from the online store */
INSERT i386InventoryChanges (pk,descript,cost,price,OverridePriceRetail,sku,abstract,ldescript,category,categories,stock,onorder, expected, listprice, Special)
(SELECT apkCatalogID,CatalogName,CostStandard,PriceLevel1Amt,OverridePriceRetail,ManufacturerCode,WebDesc,StandardDesc,fkCategoryDefault, @CategoryName, @Local, @Central, @Expected, @ListPrice, ((CatalogView & 132)-128)
FROM inserted)
RETURN
END
GO

Hope someone can improve and fix my trigger.

Thanks,
Gavin

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-18 : 07:16:20

quote:
The problem starts from the SELECT @DISCONTIUNED = (SELECT Discontinued FROM inserted) in my trigger.

You can't assume there is only one record in the inserted table. inserted table contains records affected by your update operation.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-18 : 07:40:09

Probably you need to filter the record on primary key?

SELECT @DISCONTIUNED = (SELECT Discontinued FROM inserted where Pk=@PK)

Madhivanan

Failing to plan is Planning to fail

i386
Starting Member

4 Posts

Posted - 2006-04-18 : 08:31:07

Yep this is true, its returning more then one record in the 'inserted' table

How do I go through each record and insert them into another table (i.e my change table)

Thanks,
Gavin

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-18 : 08:39:22

use INNER JOIN to join inserted table to the required table.

Example :


-- remove these
SELECT @Pk = (SELECT apkCatalogID FROM inserted)
SELECT @Central = (SELECT onorder FROM vi386InventoryCentralStock WHERE pk=@pk) 
-- change to this 
INSERT i386InventoryChanges (pk, descript, ... onorder ...)
SELECT apkCatalogID, CatalogName, ...
       onorder ...
FROM inserted i INNER JOIN vi386InventoryCentralStock v
ON   i.apkCatalogID = v.pk

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources