Subquery returned more than 1 value.

Question

Using the following Update Trigger I return

the Subquery returns more than one value This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expressionThe statement has been terminated.

Could someone please advise me on the change I need to make

USE [db]
GO
/****** Object:  Trigger [dbo].[tr_updatestockquantity_productvarient_details_main]    Script Date: 03/02/2013 00:27:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tr_updatestockquantity_productvarient_details_main]
ON [dbo].[ProductVariant]
AFTER UPDATE  
AS
IF UPDATE(StockQuantity)
BEGIN

DECLARE @newCCCQTYINSTOCK varchar(13)
DECLARE @newCCCQTYINSTOCKCount int
DECLARE @newSTOCKCODE varchar(9)
DECLARE @newAllocateStockQuantity varchar(13)

SET @newAllocateStockQuantity = (SELECT Quantity FROM dbo.OrderProductVariant pvo INNER JOIN dbo.[ProductVariant] pv ON pv.Sku = pvo.Sku)

SET @newSTOCKCODE = (SELECT SKU FROM Inserted)
SET @newCCCQTYINSTOCKCount =(SELECT COUNT(StockQuantity) FROM Inserted)

IF @newCCCQTYINSTOCKCount > 1 
RETURN

UPDATE  db.dbo.ProductVariant SET StockQuantity = @newCCCQTYINSTOCK WHERE [Sku] = @newSTOCKCODE 

UPDATE  db.dbo.STOCK SET Allocated = @newAllocateStockQuantity WHERE [STOCK CODE] = @newSTOCKCODE
END

If I base the trigger on the OrderProductVariant Table it doesn't update the Alllocate feild as the quantities are  inserted and not updated.

All help regarded

Richard

Answer 1

Seems like either or both the SELECT queries are selecting more than 1 value and assigning to the variables causing the error. You can assign only one value to the variables

@newAllocatedStockQuantity or @ newSTOCKCODE

SET @newAllocateStockQuantity = (SELECT Quantity FROM dbo.OrderProductVariant pvo INNER JOIN dbo.[ProductVariant] pv ON pv.Sku = pvo.Sku)

SET @newSTOCKCODE = (SELECT SKU FROM Inserted)

---

Narsimha

Answer 2

A trigger fires once per statement, so you cannot write a trigger from the assumptions that it fires once per row. You need to write the trigger without variables.

Unfortunately, I don't really get what the trigger is supposed to achieve, so I cannot re-write the trigger for you.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 3

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

The error message is quite clear. You cannot put more than one scalar value into a column. The real point is that good SQL: programmers do not use triggers. They are procedural code, in a dialect that cannot port. But bad SQL programmers who do not know how to write declarative code love them! Triggers look just like BASIC, COBOL, or FORTRAN! 

When you write triggers, using local variable is also a bad practice that violates the declarative programming paradigm. For example, the statement:

SET @new_ccc_qty_instock_count =(SELECT COUNT(stock_qty) FROM INSERTED);

can be removed by using the scalar subquery on the right hand side of this wherever you have that procedural local variable. 

I am also trying to figure out how @new_allocate_stock_quantity, which seems to be a quantity (numeric value by its nature)can be modeled with VARCHAR(13). 

Look at:
(SELECT something_quantity FROM Order_Product_Variants AS PVO 
 INNER JOIN 
 Product_Variants AS PV 
 ON PV.sku = PVO.sku)

Where is the PVO.sku column in the table expressions? You never selected it. 

It looks like this is a prime example of attribute splitting. Things that should be in one table with a status attribute can put into their own tables. This design flaw comes from mimicking punch card inventory systems, where the cards are moved from one deck to another as the order is processed. How are “Order_Product_Variants”, “Product_Variants” and “Stock” totally different entity, as distinct from each other as automobiles and squids? 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 4

Right!

Thank you all for the responses!

The following table i replicated from db1 using Merge replication process.

/****** Object:  Table [dbo].[STOCK]    Script Date: 03/02/2013 17:59:17 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[STOCK](
	[STOCK CODE] [varchar](9) NOT NULL,
	[SC LINK] [varchar](9) NULL,
	[DESCRIPTION] [varchar](36) NULL,
	[VAT] [float] NULL,
	[PACK] [smallint] NULL,
	[PACK DESC] [varchar](6) NULL,
	[SUPPLIER] [varchar](8) NULL,
	[LANE] [varchar](2) NULL,
	[ANAL CODE] [varchar](8) NULL,
	[COST PRICE] [money] NULL,
	[WHOLESALE PRICE] [money] NULL,
	[RETAIL PRICE] [money] NULL,
	[MRP] [money] NULL,
	[DISCOUNT BREAK] [smallint] NULL,
	[DISCOUNT PRICE] [money] NULL,
	[QTY IN STOCK] [float] NULL,
	[REORDER LEVEL] [float] NULL,
	[REORDER QTY] [float] NULL,
	[ON ORDER NO] [smallint] NULL,
	[ON ORDER DATE] [smalldatetime] NULL,
	[ON ORDER QTY] [float] NULL,
	[LAST ORDER QTY] [float] NULL,
	[LAST ORDER DATE] [smalldatetime] NULL,
	[UPDATE DATE] [smalldatetime] NULL,
	[INSERT DATE] [smalldatetime] NULL,
	[BAY] [varchar](3) NULL,
	[COUNT BY] [varchar](4) NULL,
	[QTY COUNTED] [float] NULL,
	[DATE COUNTED] [smalldatetime] NULL,
	[SEARCH WORD 1] [varchar](6) NULL,
	[SEARCH WORD 2] [varchar](6) NULL,
	[VARIANT] [varchar](9) NULL,
	[GEN NO] [smallint] NULL,
	[OFFER CODE] [varchar](1) NULL,
	[WK-1] [smallint] NULL,
	[WK-2] [smallint] NULL,
	[WK-3] [smallint] NULL,
	[WK-4] [smallint] NULL,
	[WK-5] [smallint] NULL,
	[WK-6] [smallint] NULL,
	[WK-7] [smallint] NULL,
	[WK-8] [smallint] NULL,
	[PO ACTION] [varchar](5) NULL,
	[SOURCE BAR CODE] [varchar](13) NULL,
	[DISCOUNT BREAK2] [smallint] NULL,
	[DISCOUNT PRICE2] [money] NULL,
	[DISCOUNT BREAK3] [smallint] NULL,
	[DISCOUNT PRICE3] [money] NULL,
	[BEST CUSTOMER PRICE] [money] NULL,
	[RETAIL BAR CODE] [varchar](13) NULL,
	[SHOP PRICE] [money] NULL,
	[TOP] [varchar](1) NULL,
	[END] [varchar](1) NULL,
	[COMMODITY CODE] [varchar](8) NULL,
	[WEIGHT] [float] NULL,
	[TARIFF] [varchar](15) NULL,
	[Bleep No] [float] NULL,
	[Bleep Active] [varchar](1) NULL,
	[Allocated] [float] NULL,
	[CUBIC CAPACITY] [float] NULL,
	[QTY PACKS PER LAYER] [smallint] NULL,
	[QTY LAYERS PER PALLET] [smallint] NULL,
	[PACK TOTAL PER PALLET] [smallint] NULL,
	[Vat Code] [varchar](1) NULL,
	[Unit Code] [varchar](4) NULL,
	[No Units] [float] NULL,
	[Imported] [varchar](1) NULL,
	[rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
 CONSTRAINT [PK_STOCK] PRIMARY KEY CLUSTERED 
(
	[STOCK CODE] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[STOCK] ADD  CONSTRAINT [MSmerge_df_rowguid_B87252466BC4488EBFC6EA60531D2741]  DEFAULT (newsequentialid()) FOR [rowguid]
GO

An order is processed through the Web Application and EF inserts into the following table

/****** Object:  Table [dbo].[OrderProductVariant]    Script Date: 03/02/2013 18:01:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[OrderProductVariant](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[OrderProductVariantGuid] [uniqueidentifier] NOT NULL,
	[OrderId] [int] NOT NULL,
	[CustomerId] [int] NOT NULL,
	[ProductVariantId] [int] NOT NULL,
	[Quantity] [int] NOT NULL,
	[UnitPriceInclTax] [decimal](18, 4) NOT NULL,
	[UnitPriceExclTax] [decimal](18, 4) NOT NULL,
	[PriceInclTax] [decimal](18, 4) NOT NULL,
	[PriceExclTax] [decimal](18, 4) NOT NULL,
	[DiscountAmountInclTax] [decimal](18, 4) NOT NULL,
	[DiscountAmountExclTax] [decimal](18, 4) NOT NULL,
	[AttributeDescription] [nvarchar](max) NULL,
	[AttributesXml] [nvarchar](max) NULL,
	[DownloadCount] [int] NOT NULL,
	[IsDownloadActivated] [bit] NOT NULL,
	[LicenseDownloadId] [int] NULL,
	[Sku] [nvarchar](max) NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[OrderProductVariant]  WITH CHECK ADD  CONSTRAINT [OrderProductVariant_Order] FOREIGN KEY([OrderId])
REFERENCES [dbo].[Order] ([Id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[OrderProductVariant] CHECK CONSTRAINT [OrderProductVariant_Order]
GO

ALTER TABLE [dbo].[OrderProductVariant]  WITH CHECK ADD  CONSTRAINT [OrderProductVariant_ProductVariant] FOREIGN KEY([ProductVariantId])
REFERENCES [dbo].[ProductVariant] ([Id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[OrderProductVariant] CHECK CONSTRAINT [OrderProductVariant_ProductVariant]
GO

I need to update the Allocated feild in tb1 from the quantity inserted into table 2 when the order is processed based on the STOCK CODE / Sku. This is the basic trigger I have wrote but it doesn't update when removing the variables.

USE [ConcordWholesales_new]
GO
/****** Object:  Trigger [dbo].[tr_updatestockquantity_productvarient_details_main1]    Script Date: 03/02/2013 18:05:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tr_updatestockquantity_productvarient_details_main1]
ON [dbo].[OrderProductVariant]

AFTER UPDATE  
AS
IF UPDATE(Quantity)
BEGIN

UPDATE dbo.STOCK SET Allocated = opv.Quantity
FROM dbo.OrderProductVariant opv
INNER JOIN dbo.STOCK s ON opv.Sku = s.[STOCK CODE]

END

I hope this explains all in the experience I need to update on inserted! I think if i write the trigger as insert it violates the pk stockcode constraint....

Thank you kindly btw...

Richard

Answer 5

In a trigger you have access of the virtual tables "inserted" and "deleted". In case of an ON INSERT trigger, "inserted" holds the newly inserted rows, and in a ON DELETE trigger, "deleted" holds the deleted rows. In an ON UPDATE trigger, "inserted" holds the rows after the update while "deleted" is how the rows looke before the statement ran.

These two tables have exactly the same column as the source tables. You can only access them directly from the triggers; not from stored procedures or dynamic SQL.

Very few triggers that do not use these tables are meaningful. Thus, you trigger should be:

ALTER TRIGGER [dbo].[tr_updatestockquantity_productvarient_details_main1]
ON [dbo].[OrderProductVariant]
AFTER UPDATE  AS
IF UPDATE(Quantity)
BEGIN
   UPDATE dbo.STOCK
   SET    Allocated = i.Quantity
   FROM   inserted i
   INNER JOIN dbo.STOCK s ON i.Sku = s.[STOCK CODE]
END

However, the UPDATE is problematic. What if there are multiple rows inserted for the same STOCK CODE inserted at the same time? The way the query is written, you will set Allocated to Quantity to one of them, which we don't know. And exactly this happned with the original query, except that you operated on the entire table.

I don't really know what you want to achieve, but if I take out my crystal ball, I come up with this trigger:

ALTER TRIGGER [dbo].[tr_updatestockquantity_productvarient_details_main1]
ON [dbo].[OrderProductVariant]
AFTER UPDATE  AS
IF UPDATE(Quantity)
BEGIN
   UPDATE dbo.STOCK
   SET    Allocated = s.Allocated + i.Quantity - d.Quantity
   FROM   (SELECT Sku, SUM(Quantity) AS Quantity
           FROM   inserted
           GROUP  BY Sku) AS i
   JOIN   (SELECT Sku, SUM(Quantity) AS Quantity
           FROM   deleted
           GROUP  BY Sku) AS d ON i.Sku = d.Sku
   INNER  dbo.STOCK s ON i.Sku = s.[STOCK CODE]
END

But my imagination may be running away with me.

By the way, the data type of Sku is nvarchar(MAX). That looks wrong.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 6

However, the UPDATE is problematic. What if there are multiple rows inserted for the same STOCK CODE inserted at the same time? The way the query is written, you will set Allocated to Quantity to one of them, which we don't know.

I don't really know what you want to achieve, but if I take out my crystal ball, I come up with this trigger:

ALTER TRIGGER [dbo].[tr_updatestockquantity_productvarient_details_main1]
ON [dbo].[OrderProductVariant]
AFTER UPDATE  AS
IF UPDATE(Quantity)
BEGIN
   UPDATE dbo.STOCK
   SET    Allocated = s.Allocated + i.Quantity - d.Quantity
   FROM   (SELECT Sku, SUM(Quantity) AS Quantity
           FROM   inserted
           GROUP  BY Sku) AS i
   JOIN   (SELECT Sku, SUM(Quantity) AS Quantity
           FROM   deleted
           GROUP  BY Sku) AS d ON i.Sku = d.Sku
   INNER  dbo.STOCK s ON i.Sku = s.[STOCK CODE]
END

---

Hi Yes I agree the Crystal ball is great can I have one :)... Your right there could multiple orders based on that STOCK CODE and I will require the Quantity to allocate for each order and not the one. Is the query above the correct statement multiple orders based on the Stock code

Answer 7

Well, whether the query is correct with regards to your business requirements, is something you need to verify. I can make guesses from what make sense, but whether it actually applies to your business I don't have the faintest idea.

To make it clear, say that Allocated is 320 for a STOCK CODE. Now you update Quantity for row in the table OrderProductVariant from 90 to 100. My assumption is that Allocated should now be changed to 330. But I could be completely wrong - you know that better than me.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 8

Yes Sorry right full explanation.

Stock System has 41 in Stock

eg.

dbo.STOCK

QTY IN STOCK
41

An order is placed on the Web.

40 products.  Which inserts into OrderProductVariant

Qunatity
40

QTY IN STOCK remains

QTY IN STOCK
41

Allocated becomes 40.

Allocated
40

In the Ui It is represented by

Stock Quantity = 41

Allocate = 40

Free Srock = 1

I updated the datatype error as pointed out and tested the first trigger you have provided but still returned 0.

IF UPDATE(Quantity)
BEGIN
   UPDATE dbo.STOCK 
   SET    Allocated = i.Quantity
   FROM   inserted i
   INNER JOIN dbo.STOCK s ON i.Sku = s.[STOCK CODE]
 END

There will also be multiple rows inserted via different customer based on a stock code at the same time.

Thank you

Answer 9

40 products.  Which inserts into OrderProductVariant

Inserts? You trigger is AFTER UPDATE, so it won't fire on INSERT.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 10

Yes a customer places an order on the Web app and the Quantitys are inserted into the OrderProductVariant Table.

but If I use the trigger as an Insert trigger

ALTER TRIGGER [dbo].[tr_insert_Allocated_Number_Into_STOCK]
ON [dbo].[OrderProductVariant] 
AFTER INSERT  
AS
 
BEGIN
Insert Into  dbo.STOCK (Allocated)
SELECT opv.Quantity 
FROM Inserted opv INNER JOIN dbo.[STOCK] s ON s.[STOCK CODE] = opv.Sku
END

CONSTRAINT [PK_STOCK] PRIMARY KEY CLUSTERED 

I return an error due to the the above constraint as the Stock Code is allready there in dbo.STOCK or can't Insert a null value.. I just need to insert the quantity ordered on the Web Application Into the Allocation feild based on that STOCK CODE for multiple orders. The StockMangement system automatically deducts the Allocated Quantity from the QTY IN STOCK.

I think thats built on Delphi bye the way and over 15 years old.....

Answer 11

The Insert trigger Return the following error If I use the insert.

Violation of PRIMARY KEY constraint 'PK_STOCK'. Cannot insert duplicate key in object 'dbo.STOCK'. The duplicate key value is (xxxxxxx).
The statement has been terminated.

CONSTRAINT [PK_STOCK] PRIMARY KEY CLUSTERED 
([STOCK CODE] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]

IGNORE_DUP_KEY is off I can't alter this Index, but Can I ignore it in the Select Statement and Just Insert into the Allocate feild from the OrderProductVariant Inserted Quantity!???

Thank you.

Answer 12

Hi,

If Sub query return more than one row then it will be problematic .So need to be care full  at the time of on row selection (where clause / joining  ) .Lastly  if you think data can be inconsistence then "top 1 " need to be incorporate .

---

Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

Answer 13

Just because the trigger is AFTER INSERT does not mean you need to have INSERT statement in the body of the trigger. AFTER XXX specifies after what type of operation on the target table the trigger should fire. So this is perfectly possible:

ALTER TRIGGER [dbo].[tr_insert_Allocated_Number_Into_STOCK]
                ON [dbo].[OrderProductVariant]
AFTER INSERT AS
BEGIN
   UPDATE dbo.STOCK
   SET    Allocated = i.Quantity
   FROM   inserted i
   INNER JOIN dbo.STOCK s ON i.Sku = s.[STOCK CODE]
END

However, the problem that there may be multiple rows inserted with the same value in Sku. And what happens if someone updates the quantity on an existing order? Or deletes one?

And what if there are already 20 allocated, and someone adds an order with a Quantity of 4? Should Allocated be set to 4?

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 14

However, the problem that there may be multiple rows inserted with the same value in Sku. And what happens if someone updates the quantity on an existing order? Or deletes one?

And what if there are already 20 allocated, and someone adds an order with a Quantity of 4? Should Allocated be set to 4?

---

1.

What happens if someone updates the quantity on an existing order?

The order has to fully procesed through the web application before it triggers into the Stock Management System at this present moment in time.

2.

a. what if there are already 20 allocated, and someone adds an order with a Quantity of 4.

Allocate should be set to 24.

b. Or deletes one

The order has to fully procesed through the web application before it triggers into the Stock Management System at this present moment in time.

3.

The problem that there may be multiple rows inserted with the same value in Sku.

The Sku remain's the same on indivdual stocklines in both application. They Link the two.. I just need On insert through the Web to update the specified feilds based on that Sku that never change's...

Answer 15

What happens if someone updates the quantity on an existing order?

The order has to fully procesed through the web application before it triggers into the Stock Management System at this present moment in time.

That doesn't answer the question. In programming it is incorrect assumption to say "this does not happen". I don't know the details of your system, but it is not inconceivable that an order is modified or deleted after the fact. And moreover, your trigger should also handle the case that some uses a back door and updates a row in the OrderProductVariant directly through SQL.

a. what if there are already 20 allocated, and someone adds an order with a Quantity of 4.

Allocate should be set to 24.

OK, so we need to modify the trigger to achieve this.

3.

The problem that there may be multiple rows inserted with the same value in Sku.

The Sku remain's the same on indivdual stocklines in both application. They Link the two.. I just need On insert through the Web to update the specified feilds based on that Sku that never change's...

You didn't get the point. The problem is that

 UPDATE dbo.STOCK
 SET    Allocated = i.Quantity
 FROM   inserted i
 INNER JOIN dbo.STOCK s ON i.Sku = s.[STOCK CODE]

And there are two rows in inserted for the Sku ABC, one with quantity 18 and one with quantity 41, which should win? Well, from you answer above it follows that the Allocated should be incremented with 59. (i.e. not set to 59.)

It seems that the trigger that I suggested a couple of posts back is actually what you need, but we have to make some modifications:

ALTER TRIGGER [dbo].[tr_updatestockquantity_productvarient_details_main1]
ON [dbo].[OrderProductVariant]
AFTER INSERT, UPDATE, DELETE  AS
BEGIN
   UPDATE dbo.STOCK
   SET    Allocated = s.Allocated + isnull(i.Quantity, 0) -
                                    isnull(d.Quantity, 0)
   FROM   dbo.STOCK s
   LEFT   JOIN  (SELECT Sku, SUM(Quantity) AS Quantity
                 FROM   inserted
                 GROUP  BY Sku) AS i ON i.Sku = s.[STOCK CODE]
   LEFT    JOIN (SELECT Sku, SUM(Quantity) AS Quantity
                 FROM   deleted
                 GROUP  BY Sku) AS d ON d.Sku = s.[STOCK CODE]
END

The trigger now fires for all operations on the OrderProductVariant table, and handles both single-row operations and multi-row operations. It handle changes in Quantity as well as changes in Sku. And don't tell me that this does happen - I've been in this game long enough to know that this will happen sooner or later. Important also is that STOCK.Allocated is updated from the current value.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 16

>If I base the trigger on the OrderProductVariant Table it doesn't update the Alllocate feild as the quantities are  inserted and not updated.

Hi Richard,

Why trigger?  Stored procedure would be a better choice if feasible in your application. CHECK constraint or computed column solutions also should be considered.

Trigger should be the last resort if other methods of implementation are not available or would be inferior to trigger solution.

Open a new thread if you are interested in a stored procedure solution, since this thread is about trigger solution.  Thanks.

---

Kalman Toth Database & OLAP Architect
Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Answer 17

ALTER TRIGGER [dbo].[tr_updatestockquantity_productvarient_details_main1]
ON [dbo].[OrderProductVariant]
AFTER INSERT, UPDATE, DELETE  AS
BEGIN
   UPDATE dbo.STOCK
   SET    Allocated = s.Allocated + isnull(i.Quantity, 0) -
                                    isnull(d.Quantity, 0)
   FROM   dbo.STOCK s
   LEFT   JOIN  (SELECT Sku, SUM(Quantity) AS Quantity
                 FROM   inserted
                 GROUP  BY Sku) AS i ON i.Sku = s.[STOCK CODE]
   LEFT    JOIN (SELECT Sku, SUM(Quantity) AS Quantity
                 FROM   deleted
                 GROUP  BY Sku) AS d ON d.Sku = s.[STOCK CODE]
END

The trigger now fires for all operations on the OrderProductVariant table, and handles both single-row operations and multi-row operations. It handle changes in Quantity as well as changes in Sku. And don't tell me that this does happen - I've been in this game long enough to know that this will happen sooner or later. Important also is that STOCK.Allocated is updated from the current value.

---

Hello I'm still returning

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 bellow I need to find out...

>don't tell me that this does happen - I've been in this game long enough to know that this will happen sooner or later

Answer 18

Hi Kalman

If I test both operation's the trigger and the Stored Procedure where would I begin on writing the procedure? I don't know.

Thank you All

Richard

Answer 19

So much is clear: you don't get that error message from the trigger I posted. But maybe you have some more triggers around. Beware that you can have more than one trigger on a table, so if you change the trigger name, you may suddenly have multiple triggers on the table. Or there is a trigger on the STOCK table that is haunting you.

As for Kalman's suggestion to use a stored procedure, my strong recommendation is to keep the trigger. This is exactly the type of code you should have in a trigger. If you rely on a stored procedure, and someone runs an update outside the stored procedure, you stock numbers will be incorrect.

One possible alternative is to have a view, but this would mean that you would have to compute sum from orders every time and that can be costly.

Yet an option is to have a job that runs periodically to update the column, but that job would either have to sum all orders, or be smart to only detect the changes since last time. The latter is by no means impossible, but it certainly more advanced than your current trigger.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 20

So much is clear: you don't get that error message from the trigger I posted. But maybe you have some more triggers around. Beware that you can have more than one trigger on a table, so if you change the trigger name, you may suddenly have multiple triggers on the table. Or there is a trigger on the STOCK table that is haunting you.

Yes I am extremley gratefull for you help and I absoulty agree, I want to keep with the triggers. I tend to very clean when enabling and disabling the triggers and document all testing procedures that have been made.

There are trigger's based on the STOCK table that where wrote by a outside source Insert Update and Delete.. They work on Single row operation this is the update trigger.

USE [ConcordWholesales_new]
GO
/****** Object:  Trigger [dbo].[tr_update_product_details_1]    Script Date: 03/03/2013 18:28:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tr_update_product_details_1]
ON [dbo].[STOCK]
AFTER UPDATE  
AS

DECLARE @newDESCRIPTION varchar(36)
DECLARE @newPACK smallint       
DECLARE @newLANE varchar(2)  
DECLARE @newWHOLESALE_PRICE money
DECLARE @newRETAIL_PRICE money  
DECLARE @newMRP money
DECLARE @newQTYINSTOCK float
DECLARE @newBAY varchar(3)
DECLARE @newSOURCEBARCODE varchar(13)
DECLARE @newRETAILBARCODE varchar(13)
DECLARE @newWEIGHT float
DECLARE @newQTYPACKSPERLAYER smallint
DECLARE @newQTYLAYERSPERPALLET smallint
DECLARE @newPACKTOTALPERPALLET smallint
DECLARE @newANALCODE varchar(8)
DECLARE @newSTOCKCODE varchar(9)
DECLARE @newBestCustomerPrice money 
DECLARE @categoryId int
DECLARE @productId int

SET @newSTOCKCODE = (SELECT [STOCK CODE] FROM Inserted)
SET @newDESCRIPTION = (SELECT [DESCRIPTION] FROM Inserted)
SET @newPACK = (SELECT PACK FROM Inserted)
SET @newLANE = (SELECT LANE FROM Inserted)
SET @newWHOLESALE_PRICE = (SELECT [WHOLESALE PRICE] FROM Inserted)
SET @newRETAIL_PRICE = (SELECT [RETAIL PRICE] FROM Inserted)
SET @newMRP = (SELECT MRP FROM Inserted)
SET @newQTYINSTOCK = (SELECT [QTY IN STOCK] FROM Inserted)
SET @newBAY = (SELECT [BAY] FROM Inserted)
SET @newSOURCEBARCODE = (SELECT [SOURCE BAR CODE] FROM Inserted)
SET @newRETAILBARCODE = (SELECT [RETAIL BAR CODE] FROM Inserted)
SET @newWEIGHT = (SELECT [WEIGHT] FROM Inserted)
SET @newQTYPACKSPERLAYER = (SELECT [QTY PACKS PER LAYER] FROM Inserted)
SET @newQTYLAYERSPERPALLET = (SELECT [QTY LAYERS PER PALLET] FROM Inserted)
SET @newPACKTOTALPERPALLET = (SELECT [PACK TOTAL PER PALLET] FROM Inserted)
SET @newANALCODE = (SELECT [ANAL CODE] FROM Inserted)
/*Richard Evans Description 3% on WholesalePrice */
set @newBestCustomerPrice = (SELECT [BEST CUSTOMER PRICE] FROM inserted)	   
                
Update  ConcordWholesales_new.dbo.Product set Name=@newDESCRIPTION where SKU=@newSTOCKCODE                   
Update  ConcordWholesales_new.dbo.ProductVariant set Name=@newDESCRIPTION where ProductId in (select Id from ConcordWholesales_new.dbo.Product where SKU=@newSTOCKCODE)
Update  ConcordWholesales_new.dbo.ProductVariant set Name=@newDESCRIPTION where ProductId in (select Id from ConcordWholesales_new.dbo.Product where SKU=@newSTOCKCODE)    
update  ConcordWholesales_new.dbo.Product set CCCPack=@newPACK where SKU=@newSTOCKCODE             
Update  ConcordWholesales_new.dbo.Product set CCCLane=@newLANE where SKU=@newSTOCKCODE   

/* Richard Evans -- Description Update the CustomerBestPrice To display 3% on the Website */
Update  ConcordWholesales_new.dbo.Product set CCCCustomerBestPrice=@newBestCustomerPrice where SKU=@newSTOCKCODE 
Update  ConcordWholesales_new.dbo.ProductVariant set Price=@newWHOLESALE_PRICE where ProductId in (select Id from ConcordWholesales_new.dbo.Product where SKU=@newSTOCKCODE)

/* Richard Evans -- Description - Original Wholesale Price On Update 
Update  ConcordWholesales_new.dbo.Product set CCCWholesalePrice=@newWHOLESALE_PRICE where SKU=@newSTOCKCODE*/
       
Update  ConcordWholesales_new.dbo.Product set CCCRetailPrice=@newRETAIL_PRICE where SKU=@newSTOCKCODE   
Update  ConcordWholesales_new.dbo.ProductVariant set OldPrice=@newRETAIL_PRICE where ProductId in (select Id from ConcordWholesales_new.dbo.Product where SKU=@newSTOCKCODE)       
Update  ConcordWholesales_new.dbo.Product set CCCMRP=@newMRP where SKU=@newSTOCKCODE   

/* Richard Evans -- Description If Stock Quantity goes above 0 the line Republishes
                    If it goes below 0 then it becomes Unpublished                          */
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

UPDATE  ConcordWholesales_new.dbo.Product set CCCQTYINSTOCK=isnull(@newQTYINSTOCK,'') where SKU=@newSTOCKCODE   
UPDATE  Product SET Published = CASE WHEN CCCQTYINSTOCK > 0 THEN 1 ELSE 0 END

UPDATE  ConcordWholesales_new.dbo.ProductVariant set StockQuantity=isnull(@newQTYINSTOCK,'') where ProductId = (select Id from ConcordWholesales_new.dbo.Product where SKU=@newSTOCKCODE)       
UPDATE  ProductVariant SET Published = CASE WHEN StockQuantity > 0 THEN 1 ELSE 0 END

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Update  ConcordWholesales_new.dbo.Product set CCCBay=@newBAY where SKU=@newSTOCKCODE   
Update  ConcordWholesales_new.dbo.Product set CCCSourceBarcode=@newSOURCEBARCODE where SKU=@newSTOCKCODE   
Update  ConcordWholesales_new.dbo.ProductVariant set Gtin=@newSOURCEBARCODE where ProductId in (select Id from ConcordWholesales_new.dbo.Product where SKU=@newSTOCKCODE)       
Update  ConcordWholesales_new.dbo.Product set CCCRetailBarcode=@newRETAILBARCODE where SKU=@newSTOCKCODE   
Update  ConcordWholesales_new.dbo.ProductVariant set ManufacturerPartNumber=@newRETAILBARCODE where ProductId in (select Id from ConcordWholesales_new.dbo.Product where SKU=@newSTOCKCODE)       
Update  ConcordWholesales_new.dbo.Product set CCCWeight=isnull(@newWEIGHT,'') where SKU=@newSTOCKCODE   
Update  ConcordWholesales_new.dbo.ProductVariant set [Weight]=isnull(@newWEIGHT,'') where ProductId in (select Id from ConcordWholesales_new.dbo.Product where SKU=@newSTOCKCODE)       
Update  ConcordWholesales_new.dbo.Product set CCCQTYPacksPerLayer=@newQTYPACKSPERLAYER where SKU=@newSTOCKCODE   
Update  ConcordWholesales_new.dbo.ProductVariant set [Length]=isnull(@newQTYPACKSPERLAYER,'') where ProductId in (select Id from ConcordWholesales_new.dbo.Product where SKU=@newSTOCKCODE)       
Update  ConcordWholesales_new.dbo.Product set CCCQTYLayersPerPallet=@newQTYLAYERSPERPALLET where SKU=@newSTOCKCODE   
Update  ConcordWholesales_new.dbo.ProductVariant set [Width]=isnull(@newQTYLAYERSPERPALLET,'') where ProductId in (select Id from ConcordWholesales_new.dbo.Product where SKU=@newSTOCKCODE)       
Update  ConcordWholesales_new.dbo.Product set CCCQTYTotalPerPallet=@newPACKTOTALPERPALLET where SKU=@newSTOCKCODE   
Update  ConcordWholesales_new.dbo.ProductVariant set [Height]=isnull(@newPACKTOTALPERPALLET,'') where ProductId in (select Id from ConcordWholesales_new.dbo.Product where SKU=@newSTOCKCODE)       
Update  ConcordWholesales_new.dbo.Product set CCCAnalCode=@newANALCODE where SKU=@newSTOCKCODE   
Update  ConcordWholesales_new.dbo.ProductVariant set [CCCAnalCode]=isnull(@newANALCODE,'') where ProductId in (select Id from ConcordWholesales_new.dbo.Product where SKU=@newSTOCKCODE)       

The only change's I made where area's I have commented. Do you think it would be worth changing it to a MultiRow operational trigger so it all works in conjuction with the one you have helped with btw.

This trigger Updates the second database from dbo.STOCK table there is also an Insert trigger based on this table.

Make it fully clear db1.dbo.STOCK is replicated into db2 when new lines are inserted or updated they trigger into db2.Product and ProductVariant. Could this be the confilct  and do you think it could be worth updating to MultiRow operational trigger. Or what could we think off.

As after this above post is solved this is MY FIRST intergration between two application I only have 18 months experience my end.. In all forms of programming.. Then I have a live C#, MVC, EF ecommerce based system that automatically updates stock quantities , prices -Images. Whilst registering users and processing orders back into the Stock Mangement system. O)

Answer 21

Sigh. Yes, that trigger ought to be written for multi-row operation. Did you pay that outside source to wrote that trigger?

However, I note that this trigger does not care about the Allocated column, so it is bit unnecessary to run all those cascading updates that will not actually change anything.

So it would be a good idea to add this in the beginning of the trigger:

IF NOT (UPDATE(DESCRIPTION) OR UPDATE(PACK) OR UPDATE(LANE) ...)
   RETURN

That is, if none of the columns the trigger is interested in are affected by the UPDATE statement, call it quits direclty. The main problem with this is that there are so many colunms, and if you add a new column to be handled in the trigger, you must also remember to update the initial IF statement. But I don't really see any better alternative.

Once you have added the above, there is not the same urgent need to rewrite trigger, as the trigger on the OrderProductVariant table will be able to work. You might still encounter other situations where you to perform multi-row updates on the STOCK table.

Rewriting the STOCK trigger to handle multi-row updates, should be straightforward, and note that the trigger currently have many UPDATE statements on the same table. But it is a bit of work, and you also need to test it, so you may want to put your priorities elsewhere.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 22

Sigh. Yes, that trigger ought to be written for multi-row operation. Did you pay that outside source to wrote that trigger?

However, I note that this trigger does not care about the Allocated column, so it is bit unnecessary to run all those cascading updates that will not actually change anything.

So it would be a good idea to add this in the beginning of the trigger:

IF NOT (UPDATE(DESCRIPTION) OR UPDATE(PACK) OR UPDATE(LANE) ...)
   RETURN

That is, if none of the columns the trigger is interested in are affected by the UPDATE statement, call it quits direclty. The main problem with this is that there are so many colunms, and if you add a new column to be handled in the trigger, you must also remember to update the initial IF statement. But I don't really see any better alternative.

Once you have added the above, there is not the same urgent need to rewrite trigger, as the trigger on the OrderProductVariant table will be able to work. You might still encounter other situations where you to perform multi-row updates on the STOCK table.

1. Yep they paid him...

2. The Allocated feild wasn't included, as the Data is being replicated up and automatically updating and inserting the products. When a Order Is processed the data is being replicated down and allocated. Two way merge

We are making progress if I disable the UPDATE trigger on dbo.STOCK the OrderProductVariant UPDATE trigger works!!! Thank you I'm learning allot here very grateful for the future!!!!

I added the IF statement

I add the Statement As follows

ALTER TRIGGER [dbo].[tr_update_product_details]
ON [dbo].[STOCK]
AFTER UPDATE  
AS

IF NOT (UPDATE(DESCRIPTION) OR UPDATE(PACK) OR UPDATE([LANE]) OR UPDATE([WHOLESALE PRICE])OR UPDATE([RETAIL PRICE])OR UPDATE([MRP])
        OR UPDATE([BAY])OR UPDATE([SOURCE BAR CODE])OR UPDATE([RETAIL BAR CODE])OR UPDATE([WEIGHT])
        OR UPDATE([QTY PACKS PER LAYER])OR UPDATE([QTY LAYERS PER PALLET])OR UPDATE([PACK TOTAL PER PALLET])OR UPDATE([ANAL CODE])
        OR UPDATE([BEST CUSTOMER PRICE]))
RETURN

But still return is it in the right place. 

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 also need to ask questions concerning there requirment so I can minimize that update statement...

Answer 23

Hm, I will have to admit that I am a little stumped here.

Assuming that you run the tests from SQL Server Management Studio, can you add this SELECT statement to the trigger tr_update_product_details:

SELECT columns_updated()

And give me the output?

Also, before you test, run this:

EXEC sp_configure  'show advanced options', 1
RECONFIGURE
EXEC sp_configure  'disallow results from triggers', 0
RECONFIGURE

If you run tests from an application, the SELECT is less useful, as it will only cause confusion, so it would be better to run from the SSMS. (And in that case, I would like to see the full error message, including the message header.)

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 24

Hm, I will have to admit that I am a little stumped here.

Assuming that you run the tests from SQL Server Management Studio, can you add this SELECT statement to the trigger tr_update_product_details:

SELECT columns_updated()

And give me the output?

Also, before you test, run this:

EXEC sp_configure  'show advanced options', 1
RECONFIGURE
EXEC sp_configure  'disallow results from triggers', 0
RECONFIGURE

Hi

1. When I run sp_configure I return the following error

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option 'show advance options' does not exist, or it may be an advanced option.
Configuration option 'disallow results from triggers' changed from 0 to 0. Run the RECONFIGURE statement to install.

2. When I try to Output the Updated Columns Trigger. I return the following,

(1 row(s) affected)
(1 row(s) affected)
Msg 512, Level 16, State 1, Procedure tr_update_product_details, Line 28
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 based the select statement after the return on the IF Statement before the Update begins.

IF NOT (UPDATE... ))
RETURN
SELECT COLUMNS_UPDATED()
Update  db.dbo etc.....

How can I output this correctly for us to review. I am testing in SQL Mangement Studio.

Thank you

Richard

Answer 25

>Theconfiguration option 'show advance options'does notexist

It is spelled "advanced" .

---

Kalman Toth Database & OLAP Architect
Paperback / Kindle: Windows Azure SQL Database Programming & Design

Answer 26

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option 'show advance options' does not exist, or it may be an advanced option.

You know, copy and paste is sometimes a virtue. :-)

Anyway, the important part is this:

Configuration option 'disallow results from triggers' changed from 0 to 0.

0 is the setting we want, so we can drop this.

2. When I try to Output the Updated Columns Trigger. I return the following,

Of course, you got the error, but did you look in the Results tab for the result set? Since there is an error message, SSMS switches to the Messages tab. You can press Ctrl-T and run in Text Mode to get all output in one window.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 27

Hello,

Extremly sorry for my late reply I have been tackling a replication issue. :-)

I havent been able to output the error sorry as when I programmatically testing in SSMS no results tab appears when I run the test I just return the error message In message tab. Which I find weird as all other Select and Insert statments output correctly

I thought I would proced and write the Update Trigger on dbo.STOCK as a multirow operation as reading and researching there are going to be allot of updates on that table and I feel it would be better to condense the script and agree not to use variables!!. I have started to write the script but

How do I update both tables Product p and ProductVariant pv from dbo.STOCK s with the correct syntax as we can see in the Single Row Update Trigger there are updates from the product table to the productvariant table. This is the simple statement I have so far.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[tr_update_P_PV_MultirowFunction_from_Stock]
 ON [dbo].[STOCK]
 AFTER INSERT, UPDATE, DELETE  AS


UPDATE	p
SET	    p.Name = i.[DESCRIPTION]
	   ,p.CCCPack = i.PACK
	   ,p.CCCLane = i.LANE
	   ,p.CCCWholesalePrice = i.[WHOLESALE PRICE]
	   ,p.CCCRetailPrice = i.[RETAIL BAR CODE]
	   ,p.CCCMRP = i.MRP
	   ,p.CCCBay = i.BAY
	   ,p.CCCSourceBarcode = i.[SOURCE BAR CODE]
	   ,p.CCCRetailBarcode = i.[WEIGHT]
	   ,p.CCCQTYLayersPerPallet = i.[QTY LAYERS PER PALLET]
	   ,p.CCCQTYPacksPerLayer = i.[QTY PACKS PER LAYER]
	   ,P.CCCQTYTotalPerPallet = i.[PACK TOTAL PER PALLET]
	   ,P.CCCAnalCode = i.[ANAL CODE]
	   ,p.CCCCustomerBestPrice = i.[BEST CUSTOMER PRICE]

FROM	dbo.Product p
JOIN    Inserted i
ON      i.[STOCK CODE] = p.SKU

Thank you

Answer 28

I havent been able to output the error sorry as when I programmatically testing in SSMS no results tab appears when I run the test I just return the error message In message tab. Which I find weird as all other Select and Insert statments output correctly

If there is an error message, but no results, I need to ask: are there more than one trigger? Does the procedure name in the error message match the trigger you are looking at?

How do I update both tables Product p and ProductVariant pv from dbo.STOCK s with the correct syntax as we can see in the Single Row Update Trigger there are updates from the product table to the productvariant table. This is the simple statement I have so far.

I have a rewrite of the trigger below. But first some coments on your trigger

CREATE TRIGGER [dbo].[tr_update_P_PV_MultirowFunction_from_Stock]
 ON [dbo].[STOCK]
 AFTER INSERT, UPDATE, DELETE  AS

It is not likely that you want an AFTER INSERT, UPDATE, DELETE trigger here. This is different from the other trigger where you did an incremental update of a total. Here you are just copying data, and the UPDATE on the two target tables is likely only to be meaningful, if you there was an UPDATE on Stock.

Then again, I can see that you could have use for triggers on INSERT and DELETE as well, but I don't really know what purpose that other database serves.

In the trigger code, I found these statements which look suspicious:

UPDATE  Product
SET     Published = CASE WHEN CCCQTYINSTOCK > 0 THEN 1 ELSE 0 END
UPDATE  ProductVariant
SET Published = CASE WHEN StockQuantity > 0 THEN 1 ELSE 0 END

Here you go update all rows in the target tables. That cannot really be the intention, can it?

Anyway, here is my rewritten trigger, but I have ignored Published - that is left as an exercise to the reader :-)

ALTER TRIGGER [dbo].[tr_update_product_details_1]
ON [dbo].[STOCK]
AFTER UPDATE 
AS

Update  ConcordWholesales_new.dbo.Product
set Name=i.DESCRIPTION,                  
    CCCPack=i.PACK,            
    CCCLane=i.LANE,  
    CCCCustomerBestPrice=i.[BEST CUSTOMER PRICE],
    CCCWholesalePrice=i.[WHOLESALE PRICE],
    CCCRetailPrice=i.RETAIL PRICE],  
    CCCMRP=i.MRP,  
    CCCBay=i.BAY,  
    CCCQTYINSTOCK=isnull(i.[QTY IN STOCK], 0),  
    CCCSourceBarcode=i.[SOURCE BAR CODE],  
    CCCWeight=isnull(i.WEIGHT,''),  
    CCCRetailBarcode=i.[RETAIL BARC ODE],  
    CCCQTYPacksPerLayer=i.[QTY PACKS PER LAYER],  
    CCCQTYLayersPerPallet=i.[QTY LAYER SPER PALLET],  
    CCCAnalCode=i.[ANAL CODE],  
    CCCQTYTotalPerPallet=i.[PACK TOTAL PER PALLET]
FROM    ConcordWholesales_new.dbo.Product P
JOIN    inserted i ON P.SKU = i.[STOCK CODE]

Update  ConcordWholesales_new.dbo.ProductVariant
set     Name          = i.DESCRIPTION,
        Price         = i.[WHOLESALE PRICE],
        OldPrice      = i.[RETAIL_PRICE],      
        StockQuantity = isnull(i.[QTY IN STOCK], 0),
        Gtin          = i[SOURCE BAR CODE],      
        ManufacturerPartNumber = i.[RETAIL BAR CODE],      
        [Weight] = isnull(i.WEIGHT,''),      
        [Length]=isnull(i.[QTY PACKS PER LAYER], 0),      
        [Width]=isnull(i.[QTYL AYERS PER PALLET], 0),      
        [Height]=isnull(i.[PACK TOTAL PER PALLET], 0),      
        [CCCAnalCode]=isnull(i.ANALCODE,'')
FROM    ConcordWholesales_new.dbo.ProductVariant PV
JOIN    ConcordWholesales_new.dbo.Product P ON P.Id = PV.ProductID
JOIN    inserted i ON P.SKU i.[STOCK CODE]

---

Erland Sommarskog, SQL Server MVP, [e-mail]