I finally figured out that triggers are processed in batches. I tried chaning my = to IN in my sub queries but I'm still getting:

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.

Any suggestions?

Thanks.

  AFTER INSERT
AS 
BEGIN

    IF @@ROWCOUNT = 0 
	 RETURN
  

	SET NOCOUNT ON;
	
	declare @WarningMsg			Infobar 
	
	IF(
	(select [Uf_MinOrderQty]
	FROM itemcustprice ICP 
	where effect_date IN (select MAX(effect_date) from itemcustprice where itemcustprice.item =ICP.item and itemcustprice.cust_num=ICP.cust_num) 
	AND ICP.Uf_MinOrderQty is not null 
	AND item IN (select item from inserted)	
	AND cust_num IN (select cust_num from co where co.co_num IN (select co_num from inserted))) 
	> (select qty_ordered_conv from inserted))
	
		BEGIN
			set @WarningMsg = 'Minimum Order Quantity NOT MET!  Item set to PLANNED.'
			exec dbo.WarningSp @WarningMsg
            UPDATE coitem
            SET stat = 'P'
            WHERE co_num IN (select co_num from inserted)
            and co_line IN (select co_line from  inserted) 
            and co_release IN (select co_release from inserted)
        END
	Else UPDATE coitem SET stat = 'O' 
		WHERE co_num IN (select co_num from inserted) 
		 and co_line IN (select co_line from  inserted) 
		 and co_release IN (select co_release from inserted)


END

Pls ensure the following returning only one value

if(

select [Uf_MinOrderQty]

-- (select qty_ordered_conv from inserted))

• Marked as answer by JohnDMP Tuesday, January 27, 2015 12:03 AM
• Unmarked as answer by JohnDMP Tuesday, January 27, 2015 12:03 AM

I think that's my problem. I can't ensure that. inserted is returning a batch of values. I think I may have to run inserted into a cursor(sic).

if you can put the where clause in below statement

> (select qty_ordered_conv from inserted where item = ICP.item and cust_num = ICP.cust_num))

I just put as example, you check your table and put appropriate where clause there.

Hope this works.

I tried WHERE co_num IN (select co_num from inserted) and co_line IN (select co_line from  inserted) and co_release IN (select co_release from inserted)

But I'll try your suggestion tomorrow.

Thanks.

           

Hi, I don't know all the details but has to ensure one value. Please take a look and tweak accordingly.

Option 1:

	IF(
	(select top(1) [Uf_MinOrderQty]
	FROM itemcustprice ICP 
	where effect_date IN (select MAX(effect_date) from itemcustprice where itemcustprice.item =ICP.item and itemcustprice.cust_num=ICP.cust_num) 
	AND ICP.Uf_MinOrderQty is not null 
	AND item IN (select item from inserted)	
	AND cust_num IN (select cust_num from co where co.co_num IN (select co_num from inserted)
order by ICP.item )) 
	> (select top(1) qty_ordered_conv from inserted))

Option 2:
	IF(
	(select max([Uf_MinOrderQty])
	FROM itemcustprice ICP 
	where effect_date IN (select MAX(effect_date) from itemcustprice where itemcustprice.item =ICP.item and itemcustprice.cust_num=ICP.cust_num) 
	AND ICP.Uf_MinOrderQty is not null 
	AND item IN (select item from inserted)	
	AND cust_num IN (select cust_num from co where co.co_num IN (select co_num from inserted)
order by ICP.item )) 
	> (select max(qty_ordered_conv) from inserted))

Thanks.  I'll try that...

I was wondering....since I'm taking the "inserted" file a line at a time and comparing each line to a corresponding row in another table to see if certain conditions are met and then modifying the inserted into table accordingly, would I be better off running the inserted file into a cursor?

This is for loading customer order EDI files.  If the order quantity is under the minimum contract quantity the row has to be flagged (set status = 'P'). The trigger works when doing manual inserts one at a time but fails when loaded by batch.

Hi, You are right in the sense if you manually insert one row then it works but when insert in a batch it would fail.

Perhaps you can update status flag outside trigger by checkign the order quantity soon after batch process or first update statging table with proper status and then processed records into final table..??

If you still want to use trigger then Please take a look at the code and tweak as per your table/columns. 

All the best!

 CREATE TRIGGER trg1 
 ON itemcustprice
 AFTER INSERT
AS 
BEGIN

    IF @@ROWCOUNT = 0 
	 RETURN;
  
	SET NOCOUNT ON;
	
		UPDATE CO
		SET CO.stat = 
		CASE 
			WHEN CO.qty_ordered_conv < (
			 
				SELECT TOP(1) ICP.Uf_MinOrderQty 
				FROM itemcustprice AS ICP
				WHERE ICP.item = CO.item
				and ICP.co_num = CO.co_num
				and ICP.cust_num = CO.cust_num
				and ICP.Uf_MinOrderQty is not null 
				ORDER BY effect_date DESC

			)   THEN  'P' 
			ELSE 'O'
		END
		FROM coitem AS CO 
		WHERE EXISTS ( 
				select * 
				from inserted 
				WHERE co_num		= CO.co_num 
				and co_line		= CO.co_line
				and co_release  = CO.co_release 
		);



END

• Proposed as answer by Eric__ZhangMicrosoft contingent staff, Moderator Saturday, January 31, 2015 7:23 AM
• Marked as answer by Eric__ZhangMicrosoft contingent staff, Moderator Tuesday, February 03, 2015 8:49 AM

I gave up using a trigger and am currently working with the SP that loads data into this table.

Sorry cant help much on this unless you explain what you're trying to achieve with update above. Give some sample data and explain what you want as output from it.

select [Uf_MinOrderQty] gives you multiple rows, please check your where clause to make sure select statement returns only 1 row.