Bug #100216 Saving Multiple Triggers Fails in Workbench 8.0.21

• Submitted: 14 Jul 2020 13:37
• Modified: 14 Jul 2020 14:54
• Reporter: IGG t
• Status: Can't repeat
• Category: MySQL Workbench: SQL Editor
• Severity: S3 (Non-critical)
• Version: 8.0.21
• OS: Windows (10)
• CPU Architecture: x86
• Tags: triggers, workbench

[14 Jul 2020 13:37] IGG t

Description:
In the latest version of MySQL workbench, when trying to create two triggers on the same table  / event the second one always fails.

How to repeat:
I am using MySQL 8.0.21 and Workbench 8.0.21

Create two simple tables:

CREATE TABLE `tab1` (
  `c1` int NOT NULL,
  `c2` int DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `tab2` (
  `d1` int NOT NULL,
  `d2` int DEFAULT NULL,
  PRIMARY KEY (`d1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

In Workbench now add the first trigger: 

Right Click on tab1 > Alter Table > Triggers

And add a trigger:

CREATE DEFINER=`root`@`::1` TRIGGER `tab1_AFTER_INSERT` AFTER INSERT ON `tab1` FOR EACH ROW BEGIN
	INSERT INTO test.tab2 (d1) values (new.c1);
END

and click apply. This works fine. 
Close the tab, and repeat to add the second trigger:

CREATE DEFINER = CURRENT_USER TRIGGER `test`.`tab1_AFTER_INSERT_1` AFTER INSERT ON `tab1` FOR EACH ROW
BEGIN
	INSERT INTO test.tab2 (d2) values (new.c2);
END

and click apply.

Clicking Apply then fails with a SQL error. On closer inspection, it is trying to execute the following:

DROP TRIGGER IF EXISTS `test`.`tab1_AFTER_INSERT_1`;

DELIMITER $$
USE `test`$$
CREATE DEFINER = CURRENT_USER TRIGGER `test`.`tab1_AFTER_INSERT_1` AFTER INSERT ON `tab1` FOR EACH ROW FOLLOWS `tab1_AFTER_INSERT`
$$
DELIMITER ;

e.g. it has automatically added the "FOLLOWS" statement, but then missed out the SQL query.

Creating the triggers normally works fine. It is only in the Table editor that it fails. 

Suggested fix:
Ensure that the second trigger picks up the SQL as well as adding the FOLLOWS statement.

[14 Jul 2020 14:53] MySQL Verification Team

2 triggers

Attachment: 100216_0.png (image/png, text), 23.46 KiB.

[14 Jul 2020 14:54] MySQL Verification Team

Thank you for the bug report. I couldn't repea the issue reported.

[14 Jul 2020 15:37] IGG t

Alter Table > Triggers

Attachment: Screenshot_17.png (image/png, text), 12.51 KiB.

[14 Jul 2020 15:37] IGG t

Missing the Insert Query

Attachment: Screenshot_19.png (image/png, text), 22.55 KiB.