Bug #97344 MySQL Workbench only allows one trigger per event and action time.

• Submitted: 23 Oct 2019 14:45
• Modified: 24 Oct 2019 12:36
• Reporter: Oliver Chattlesworth
• Status: Verified
• Category: MySQL Workbench
• Severity: S7 (Test Cases)
• Version: 8.0.18
• OS: Windows (Microsoft Windows 10 Pro)
• CPU Architecture: Any
• Tags: WBBugReporter

[23 Oct 2019 14:45] Oliver Chattlesworth

Description:
MySQL Workbench only allows the creation of one BEFORE INSERT, AFTER INSERT, BEFORE UPDATE, AFTER UPDATE, BEFORE DELETE, AFTER DELETE trigger at a time.

How to repeat:
Create more than one BEFORE INSERT trigger for a table using MySQL Workbench.  Upon clicking apply to commit the changes to the server you get the following error:

Executing:
DROP TRIGGER IF EXISTS `test`.`tbl_vendor_BEFORE_UPDATE`;

DELIMITER $$
USE `test`$$
CREATE DEFINER = CURRENT_USER TRIGGER `test`.`tbl_vendor_BEFORE_UPDATE` BEFORE UPDATE ON `tbl_vendor` FOR EACH ROW FOLLOWS `tbl_vendor_MODIFIEDBY`
$$
DELIMITER ;

Operation failed: There was an error while applying the SQL script to the database. ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
SQL Statement:
CREATE DEFINER = CURRENT_USER TRIGGER `test`.`tbl_vendor_BEFORE_UPDATE` BEFORE UPDATE ON `tbl_vendor` FOR EACH ROW FOLLOWS `tbl_vendor_MODIFIEDBY`

There is no error in the SQL syntax and if the two triggers are combined into a single trigger the commit succeeds.

[23 Oct 2019 14:50] Oliver Chattlesworth

Creating two triggers doesn't work

Attachment: MySQL Bug 1.png (image/png, text), 8.26 KiB.

[23 Oct 2019 14:51] Oliver Chattlesworth

Creating one trigger works

Attachment: MySQL Bug 2.png (image/png, text), 7.70 KiB.

[24 Oct 2019 12:36] MySQL Verification Team

Hello Oliver Chattlesworth,

Thank you for the report and feedback.

regards,
Umesh

[7 Apr 2023 23:59] Matz Flander

I was able to add second/third triggers once I realized the following;

Take Olivers example.

Executing:
DROP TRIGGER IF EXISTS `test`.`tbl_vendor_BEFORE_UPDATE`;

DELIMITER $$
USE `test`$$
CREATE DEFINER = CURRENT_USER TRIGGER `test`.`tbl_vendor_BEFORE_UPDATE` BEFORE UPDATE ON `tbl_vendor` FOR EACH ROW FOLLOWS `tbl_vendor_MODIFIEDBY`

-- Problem is right here. 

-- The newly added trigger definition is incomplete. Everything between BEGIN and  END is missing.

-- If you add (paste) the code right here,  it actually succeeds. Otherwise only the drop succeeds (hence only one trigger remains)

-- But this is dangerous. Any DB that uses triggers with FOLLOWS / PRECEDES created from a script and edited with Workbench would be subject to this issue

-- As it was in my case. 

$$
DELIMITER ;