1 Replies - 2581 Views - Last Post: 07 July 2012 - 05:16 AM

#1 Priyadarshana  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 16-May 12

Trigger error...#1415 - Not allowed to return a result set from a trig

Posted 07 July 2012 - 02:20 AM

Hello Friends,

I am working on one project where I need to change the database. Previously I was working in MS Sql server and now there is requirement to work on MySql. I want to convert my trigger code from MS Sql to Mysql. Here is that code in MSsql which was successfully run on MSsql.

ALTER TRIGGER billingtrigger
ON dbo.Billing
AFTER Insert/* INSERT, UPDATE, DELETE */
AS
declare @item int;
declare @quantity int;
declare @loc int;
declare @inv int;
declare @lotnum varchar(50);

Select @item = i.item_id from inserted i;
Select @quantity = i.quan from inserted i;
Select @loc = (Select b.loc_id from billstatus b where b.id = (Select bill_id  from inserted i));
Select @inv = (Select b.inv_id from billstatus b where b.id = (Select bill_id from inserted i));
Select @lotnum = i.lot_num from inserted i;

Begin
	/*Insert into onhand_quan (quantity) values (@quan)*/
	SET NOCOUNT ON 

	if exists(( Select * from onhand_quan where (item_id = @item) and (loc_id = @loc) and (lot_number = @lotnum) and (inv_id = @inv)) ) 
	
	Update onhand_quan set quantity= quantity-@quantity where (item_id = @item) and (loc_id = @loc) and (lot_number = @lotnum) and (inv_id = @inv) 
	
End


Following error cames when I save these trigger:
#1415 - Not allowed to return a result set from a trigger
I had convert these in Mysql but it gives me error . Here that code of mysql.

delimiter @@
Create TRIGGER billingtrigger
AFTER Insert ON billing

For Each Row
Begin
declare titem integer;
declare tquantity integer;
declare tloc integer;
declare tinv integer;
declare tlotnum varchar(50);


Select titem = i.item_id from inserted i;
Select tquantity = i.quan from inserted i;
Select tloc = (Select b.loc_id from billstatus b where b.id = (Select bill_id  from inserted i));
Select tinv = (Select b.inv_id from billstatus b where b.id = (Select bill_id from inserted i));
Select tlotnum = i.lot_num from inserted i;


	/*Insert into onhand_quan (quantity) values (@quan)
	SET NOCOUNT ON */

	if exists(( Select * from onhand_quan where (item_id = titem) and (loc_id = tloc) and (lot_number = tlotnum) and (inv_id = tinv)) ) then
	
	Update onhand_quan set quantity= quantity-tquantity where (item_id = titem) and (loc_id = tloc) and (lot_number = tlotnum) and (inv_id = tinv) ;
	end if;
	
	
END
@@



If any question, if you did not get my point clear than please ask. Its urgent. Please Help!!!

Please give me suggestion and help me to work on these.

It would be pleased and appreciated to any help

Thanks in Advance

MOD EDIT: Changed COLOR tags to CODE tags.

This post has been edited by JackOfAllTrades: 07 July 2012 - 05:10 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Trigger error...#1415 - Not allowed to return a result set from a trig

#2 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6077
  • View blog
  • Posts: 23,545
  • Joined: 23-August 08

Re: Trigger error...#1415 - Not allowed to return a result set from a trig

Posted 07 July 2012 - 05:16 AM

Looking into this, it appears you need to use SELECT INTO, rather than the SELECTs you have. Maybe, for example

Select i.item_id into titem from inserted i;


and so on.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1