MySQL Bugs: #52997: Looaing trigger version 5.2.19 (Different than the old fixed one)

Bug #52997	Looaing trigger version 5.2.19 (Different than the old fixed one)
Submitted:	20 Apr 2010 17:30	Modified:	12 May 2010 12:51
Reporter:	Martin Pirringer	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S3 (Non-critical)
Version:	5.2.19	OS:	Windows (7)
Assigned to:	Alexander Musienko	CPU Architecture:	Any
Tags:	Trigger loosing

Description:
When first making an error and then fixing it trigger gets dropped

How to repeat:
1.) Create a test table table1 with 2 int columns (idtable1 and field1)
2.) create the following triggers
 CREATE DEFINER=`admin`@`%` TRIGGER `beforeinsert`
BEFORE INSERT On table1
FOR EACH ROW
BEGIN 
    SET new.field1= new.idtable1;
END$$CREATE DEFINER=`admin`@`%` TRIGGER `beforeunsert`
BEFORE UPDATE On table1
FOR EACH ROW
BEGIN 
    SET new.field1= new.idtable1;
END$$

3.) Now in the 2nd trigger right before the end  insert an error (badcommand) like
CREATE DEFINER=`admin`@`%` TRIGGER `beforeinsert`
BEFORE INSERT On table1
FOR EACH ROW
BEGIN 
    SET new.field1= new.idtable1;
END$$CREATE DEFINER=`admin`@`%` TRIGGER `beforeunsert`
BEFORE UPDATE On table1
FOR EACH ROW
BEGIN 
    SET new.field1= new.idtable1;
    badcommand;
END$$

4.) CLick apply and you get something like "Your DDL statement has errors - Do you want to save anyway" - Click no

5.) Go back and remove the error to restore it to the original version

6.) click apply and you get the following to execute
DELIMITER $$

DROP TRIGGER IF EXISTS test.beforeunsert$$

USE `test`$$

DROP TRIGGER IF EXISTS `test`.`beforeunsert` $$

DELIMITER ;

Efectively killing all the triggers

Note: This also happens if you fix the error with a valid statement

Suggested fix:
Don't loose the triggers

Thank you for the bug report. Verified just as described.

fix confirmed in repository

An entry has been added to the 5.2.21 changelog:

In the Alter Table dialog of the SQL Editor, if two triggers were created, the second with a bad command, and the error corrected, then when changes were applied, the generated code was incorrect, dropping both triggers.