Bug #61698 Copy to Clipboard/Send to SQL Editor Trigger Issue in Create Statement

• Submitted: 29 Jun 2011 18:39
• Modified: 27 Jan 2012 3:20
• Reporter: Kyle Stevens
• Status: Closed
• Category: MySQL Workbench: SQL Editor
• Severity: S3 (Non-critical)
• Version: 5.2.34
• OS: Windows (Windows 7)
• CPU Architecture: Any

[29 Jun 2011 18:39] Kyle Stevens

Description:
When right-clicking on a table that has triggers to copy the create statement to either the clipboard or the SQL editor, the trigger creation statement occurs before the table creation statement and has to be manually modified as a trigger cannot be created until after the table is created.

How to repeat:
The SQL statements below will create the necessary tables/triggers.  After that, try and copy the create statement by right clicking on the table in the SQL editor.  Delete the tables and try recreating them with the copied statements.  It will fail.

CREATE TABLE `table1` (
  `idtest` int(11) NOT NULL AUTO_INCREMENT,
  `message` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`idtest`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1$$

CREATE  TABLE `nepco2_dbo`.`testaudittable1` (
  `idtestaudittable1` INT NOT NULL AUTO_INCREMENT,
  `message` VARCHAR(45) NULL ,
  PRIMARY KEY (`idtestaudittable1`) );

DELIMITER $$

USE `nepco2_dbo`$$

CREATE
DEFINER=`root`@`%`
TRIGGER `nepco2_dbo`.`OnInsert`
AFTER INSERT ON `nepco2_dbo`.`table1`
FOR EACH ROW
BEGIN

    INSERT INTO testaudittable1(message)
    values(NEW.message);
END
$$

Suggested fix:
Have the trigger creation statements appear after the table creation statements.

[30 Jun 2011 2:49] MySQL Verification Team

Thank you for the bug report.

[27 Jan 2012 3:20] Philip Olson

Fixed as of 5.2.38:

When right-clicking on a table that has triggers, and then
choosing "Create Statement" to either send the
statement to the clipboard or SQL editor, the trigger creation
statement is written before the table creation statement.