unexpected error in trigger when export
mysql, sql, triggers
Solution
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.
Problem
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 ; ```