unexpected error in trigger when export

Question

Documentation says that

There cannot be two triggers for a given table that have the same trigger action time and event.

I use mysql 5.5.16. I created two triggers. upd_check1 and sale_tri. I am sure that my two trigger perform two different tables on INSERT event. But why I get this error in both triggers. when I try export database(phpmyadmin doesn't show error but mysql workbench, heidisql, query browser show error).

SQL Error (1235): This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'

Here my upd_check1 trigger

--
-- Triggers `product_purchases`
--
DROP TRIGGER IF EXISTS `upd_check1`;
DELIMITER //
CREATE TRIGGER `upd_check1` AFTER INSERT ON `product_purchases`
 FOR EACH ROW BEGIN
  DECLARE X INTEGER;
  SET X = (SELECT product_id FROM product_stock where product_id = NEW.product_id );
  IF NEW.product_id =X  THEN
  UPDATE
    product_stock AS S,
    product_purchase_item AS I,
    product_purchases AS P
  SET
    S.product_total_quantity=S.product_total_quantity+I.quantity
  WHERE
    S.product_id=I.product_id
  AND
    I.product_purchase_item_id=P.product_purchase_item_id
  AND
    P.product_purchase_id=NEW.product_purchase_id;
  ELSE
    INSERT INTO
      product_stock (product_id,product_total_quantity)
    SELECT
      product_id, quantity
    FROM
      product_purchase_item
    WHERE
      product_purchase_item_id=NEW.product_purchase_item_id;
  END IF;

  END
//
DELIMITER ;

And Here my sale tri trigger

--
-- Triggers `product_sales`
--
DROP TRIGGER IF EXISTS `sale_tri`;
DELIMITER //
CREATE TRIGGER `sale_tri` AFTER INSERT ON `product_sales`
 FOR EACH ROW BEGIN
       DECLARE X INTEGER;

        SET X = (SELECT product_id FROM product_stock where product_id = NEW.product_id );
        IF NEW.product_id =X THEN
          update product_stock as s,
          product_sale_item as i,
          product_sales as p
        SET s.product_total_quantity=s.product_total_quantity-i.quantity
        WHERE s.product_id=i.product_id AND
           i.product_sale_item_id=p.product_sales_item_id AND
           p.product_sales_id=NEW.product_sales_id;
       END IF;
   END
//
DELIMITER ;

Answer 1

If both

SHOW TRIGGERS

and

SELECT * from INFORMATION_SCHEMA.TRIGGERS

only display one AFTER INSERT trigger for each table, the next step is to make sure there is no corruption in the trigger definition itself.

MySQL keeps triggers in *.TRG and *.TRN files in the data directory, and the format is textual, so a visual inspection can help to verify how many triggers are really defined.

You should see something similar to this for the table:

malff@linux-3ezv:test> more product_sales.TRG
TYPE=TRIGGERS
triggers='CREATE DEFINER=`root`@`localhost` TRIGGER `sale_tri` AFTER INSERT ON `product_sales`\n FOR EACH ROW BEGIN\n       D
ECLARE X INTEGER;\n ... END'
sql_modes=0
definers='root@localhost'
client_cs_names='utf8'
connection_cl_names='utf8_general_ci'
db_cl_names='latin1_swedish_ci'

And for the trigger name file:

malff@linux-3ezv:test> more sale_tri.TRN
TYPE=TRIGGERNAME
trigger_table=product_sales

Multiple triggers in the TRG file will confirm if the definition somehow is corrupt (in which case you might have to drop and create the triggers again), but not how it actually became corrupt.

In any case, never edit *.TRN or *.TRG files manually.