MySQL Forums
Forum List  »  Triggers

Re: Check for existence of a trigger
Posted by: Andrew Gilfrin
Date: March 11, 2005 02:38AM

IF EXISTS isn't supported for triggers at present and there is no way currently to find out what triggers do exists as there is no INFORMATION_SCHEMA.TRIGGERS at present.

I have a few suggestions of ways around this on my website which contains information on stored procedures and triggers, it's at www.mysqldevelopment.com

Essentially the suggestions is that you maintain a standard naming convention for you triggers which basically uses the trigger type and name of table. As it's not possible to have 2 triggers of the same type anyway this works well.

So lets say you have a before insert for each row trigger on the emps table then you trigger name would be.

bi_emps_fer

Therefore you don't need the INFORMATION_SCHEMA.TRIGGERS to know what the triggers associated with a trigger is called. The biggest problem you will face is that you might create a test trigger on a table and forget the name trying to add a trigger of the same type will produce an error message, I think the only way around that at present is to drop the table it's attached to.

As for your script not running if the trigger is not there to be dropped, I have never seen this as a problem, all the scripts I have created just produce the message and carry on.

Andrew Gilfrin
http://www.mysqldevelopment.com

Options: ReplyQuote


Subject
Views
Written By
Posted
8408
CMS
March 10, 2005 04:23PM
Re: Check for existence of a trigger
3093
March 11, 2005 02:38AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.