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

[priya sarin]

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