MySQL Bugs: #45931: Trigger editor useless on copy/paste between tables

Bug #45931	Trigger editor useless on copy/paste between tables
Submitted:	3 Jul 2009 6:05	Modified:	28 Aug 2009 10:30
Reporter:	Petr Burian	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench	Severity:	S3 (Non-critical)
Version:	5.1.16	OS:	Any
Assigned to:	Sergei Tkachenko	CPU Architecture:	Any
Tags:	CHECKED, copy, paste, trigger

Description:
Copying a trigger from first table to second table and editing the trigger on second table brokes trigger in the first table.

How to repeat:
Create tables 'main', 'table_a', 'table_b'. Click on table 'table_a' and create trigger:

CREATE TRIGGER trig_table_a
AFTER DELETE ON table_a FOR EACH ROW
DELETE FROM main WHERE id = OLD.main_id

Now I wont the similar trigger on 'table_b'. The easist way is to copy trigger from 'table_a' to 'table_b' and edit it on 'table_b' right?

If I follow this rule the first trigger is broken because it starts to clone itself why I edit the second one. Please take a look to attached mwb. Open trigger section on 'table_a' - first trigger is the right one. Second is the self-created clone of the first one which was created why I edited the copied trigger on 'table_b'.

If the source trigger is large and I make a lot of changes in destination trigger (create from a source trigger - copy/paste) more clones are created...

MWB to see the bug

Attachment: trigger.mwb (application/octet-stream, text), 6.17 KiB.

Thank you for the problem report. Indeed, if after copy/paste one will NOT edit ON table clause fast enough to match current table, trigger definition will "jump" back to the initial table. This makes creation of large, similar triggers for several tables too complicated.

Verified on Windows XP.

Triggers editor was changed to treat all entered/pasted triggers as triggers of the current table but their names still have to be unique within the table, otherwise same named triggers will overwrite each other resulting in 1 trigger (the latter of all same-named parsed triggers).
Additionally wrong schema and/or table name pointed in `create trigger` statement made to be highlighted same way as ordinary syntax errors.

I have used WB 5.1.16 SE in Linux past few days a lot and there are other problems with trigger editor. Maybe fixed already but for sure I mention.

1) If I edit existing triggers on table each change results in new blank lines. See attached trigger2.mwb, open table1, trigger editor and e.g. append TAB on line 8. Now between before and after trigger 2 new blank lines appear. If you remove TAB, the same situation - 2 new blank lines appear.

2) When you do the same on second trigger - adding TAB on line starting with "AFTER DELETE ON table1..." the situation is even more crazy - not only 2 blank lines appear but also cursor is moved to another position ;)

3) I have to use external editor to write or change triggers (e.g. Kate). Internal trigger editor is useless due to those and older bugs I reported. Sometimes I examine Copy&Paste (Copy from Kate and Paste to trigger editor) is not working in Linux. Only paste using middle mouse button is working. But this problem is again hardly reproducable.

Other file to reproduce new problems

Attachment: trigger2.mwb (application/octet-stream, text), 4.52 KiB.

these issues have been fixed.
fix will be included in 5.1.17

An entry was added to the 5.1.17 changelog:

If an attempt was made to copy and paste a trigger, the trigger code would revert to the source trigger, when the pasted trigger was edited.

MySQL Workbench has been changed so that triggers are associated with the table in which they are edited. If a trigger has the wrong schema or table pointed to by its CREATE TRIGGER statement, it will be highlighted as a syntax error.