MySQL Bugs: #61712: Changes to trigger contents will not save

Bug #61712	Changes to trigger contents will not save
Submitted:	30 Jun 2011 16:46	Modified:	19 Jun 2012 1:21
Reporter:	Darren Martz	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S3 (Non-critical)
Version:	5.2.33 CE, 5.2.34	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	trigger alter

Description:
Changes to an existing trigger are not detected by the editor and it is assumed that no changes have been made. Unfortunately the editor will not even try to save the changed trigger(s).

How to repeat:
1. create one or more triggers on a table using the editor (the alter-table dialog box) and apply the changes. Use statements such as "SET NEW.fieldname=NULL;"

2. open the table editor and add another statement such as "SET NEW.fieldname2=NULL;"

3. attempt to apply changes to the table. The editor will inform you that no changes had been made so nothing will be applied.

Suggested fix:
1. add a do-nothing statement such as "DELETE FROM sometable WHERE 1=2" to the trigger and apply the changes.

2. remove the do-nothing statement and apply the changes.

Now your SET statements from the "how to repeat" example will be applied to the trigger.

Please, check if the same problem still happens with a newer version, 5.2.34.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Confirmed, it still exists as written on 5.2.34

I suspect this may be aggravated when multiple triggers are registered. 

In version 5.2.34 my trick of added a NOOP statement no longer works. The check is more strict and ONLY allows editing of one trigger.

What should be done to support multiple triggers on a single table? Example... "After Insert On" with a "Before Update On" and a "After Delete On".

The current implementation in 5.2.34 assumes only one trigger is ever used.

What server version, 5.x.y, are you working with? What operating system do you use to run Workbench?

I see no problem editing (in any) one of several triggers defined for a table on Windows XP, while working with local MySQL server 5.1.56.

Server is 5.0.77 on CentOS 5.6
Client is on Win7 32bit

Hello Darren.
Thanks a lot for your report.
Reproduced on WB 5.2.34 and 5.2.35, Windows 7x64
Repro steps:
1. Open WB.
2. Open SQL Editor.
3. Select an schema.
4. Right click on a table and select the "Alter table" option.
5. Select the "Trigger" tab and add the following code "SET NEW.codigo=NULL;".
6. Press the "Apply" button.

Actual results:
The "No changes to object were detected." message will appear.

Expected results:
The trigger changes should be saved.

Evidence

Attachment: Trigger.JPG (image/jpeg, text), 127.00 KiB.

This is fixed as of the upcoming 5.2.41 release of Workbench. Here's the changelog entry:

Adding a trigger with the "Alter Table",
"Trigger" interface now warns the user if the
inputted trigger statement was invalid. Before, Workbench
would output "No changes to object were detected." if the SQL
was valid, but without a "CREATE TRIGGER"
statement.

I have exactly the same problem on Workbench 6.0, build 6.0.7.11215, which can be reproduced the same way. 

The fix proposed by Darren Martz with "do-nothing statement" helps in this case too.

Looks like it is the same bug?