Modifying a MySQL Trigger

Question

I have a MySQL trigger that I would like to be modified. The only changes are in the trigger body.

Will updating the ACTION_STATEMENT Column in INFORMATION_SCHEMA.TRIGGERS suffice? Is this the right way to update a trigger? Specifically, I am looking for any problems that might arise by doing this.

score 2 · Answer 1 · answered Mar 17 '11 at 14:35

2

That won't work.

You need to drop the trigger and recreate it.

http://dev.mysql.com/doc/refman/5.1/en/create-trigger.html

answered Mar 17 '11 at 14:35

Ike Walker

64,401
14
110
109

Is that still applicable nowadays that mysql is 5.6/5.7/8.0? I don't see any warning about edition `information_schema.TRIGGERS` in the link (tho it's now 8.0 and 5.1 is no longer online apparently) – Xenos Oct 11 '19 at 08:04
@Xenos I don't think this has changed in newer versions. As of MySQL 8.0 you still need to DROP/CREATE a trigger if you want to change it. – Ike Walker Oct 15 '19 at 01:30
Indeed, I tried that after posting the comment, and the `TRIGGERS` table is not actually update-able (table cannot be updated or so). Hence the need of dropping and creating the trigger again. – Xenos Oct 15 '19 at 11:52

Johan · Answer 2 · 2018-12-26T16:37:03.943

1

Download dbForge express (is free as in Beer).
Connect with that and edit the trigger.

Download page http://www.devart.com/dbforge/mysql/studio/download.html

Direct link https://www.devart.com/dbforge/mysql/studio/dbforgemysql80exp.exe

Don't muck about in the information_schema.
Oh and don't use MySQL workbench 5.2 I've had that one eat my work twice (..gone..) never again.

No I don't have shares, just a happy dbForge user

edited Dec 26 '18 at 16:37

answered Apr 14 '11 at 21:48

Johan

74,508
24
191
319

Thanks for recommendations! The correct link is https://www.devart.com/dbforge/mysql/studio/dbforgemysql80exp.exe – Devart Dec 26 '18 at 15:42
It would be far better to propose the statements to use, and eventually suggest a GUI editor that does it, rather than promoting an SQL client just because you like it. – Xenos Oct 11 '19 at 08:03

score 0 · Answer 3 · answered Mar 17 '11 at 14:40

The ACTION_STATEMENT column contains the statement to be executed when the trigger is invoked. This is the same as the text displayed in the Statement column of the output from SHOW TRIGGERS. Note that this text uses UTF-8 encoding. MYSQL TRIGGERS SCHEMA TABLE

You're right, if you edit the ACTION_STATEMENT field, you modify the SQL action.

Modifying a MySQL Trigger

3 Answers3