Is it true I can't edit a MySQL trigger, I have to drop it and create a new one?

Question

Is it true I can't edit a MySQL trigger, I have to drop it and create a new one?

Also, being a relative newcomer to triggers, it feels like they seem liable to causing 'erroneous' data. For example I might want a trigger to be fired (inserting data into another table) after one particular type of update query, but not others.

Any tips here gratefully received!

Answer 1

Edit: Yes, it is true that versions 5.n and 6.n of MySQL 5 & 6 implement CREATE TRIGGER and DROP TRIGGER and nothing else. According to this hunk of Postgres documentation, there is not even CREATE TRIGGER in SQL 92, so consider yourself lucky to have TRIGGER at all :-)

The Visual Studio MySQL plugin documentation has:

To modify an existing trigger, double click on a node of the trigger you wish to modify, or right click on this node and choose the Alter Trigger command from a context menu. Either of the commands opens the SQL Editor.

... which seems to do what you want. My guess is this is GUI sugar and behind the scenes you get a DROP CREATE.

As far as a trigger for some UPDATEs and not others, SQL has exactly one UPDATE per table. Put an IF clause at the start of your UPDATE trigger so that your logic - whatever you are doing in some of your UPDATEs - is only executed when you think it is appropriate.

Answer 2

MySQL has REPLACE TRIGGER, right?

As a sidenote.. Is it an issue? If you're worried queries are executed in between DROP and CREATE, you could always lock the table beforehand.