Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2012
    Posts
    4

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

    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


    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
    @@


    Following error cames when I save these trigger:
    #1415 - Not allowed to return a result set from a trigger

    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

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    There are things wrong with your conversion:

    1. to assign a value use SET var = value or SELECT field INTO var
    2. IF EXISTS should be replaced with DECLARE CONTINUE HANDLER FOR NOT FOUND or have a SELECT COUNT(*) INTO var and check on IF count = 1 THEN update.

    The actual message being returned is that the trigger is attempting to return a result set from within the trigger which is not allowed.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •