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

Discussion in 'MySQL' started by priyadarshana, Jul 7, 2012.

0
  1. #1
    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
     
    priyadarshana, Jul 7, 2012 IP