How to verify if a Trigger exists or not before creating it?

Question

I intend to create a Trigger in table but first I would like to check if it already exists in my base .... how do I do ?

CREATE TRIGGER IF NOT EXISTS before_update_tableX ... ect

Answer 1

Consulting the triggers

It depends on what you call "knowing if there is". Knowing if there is a name, or content?

For a "peek" just the

SHOW TRIGGERS

Now, if you need anything more elaborate:

SELECT
   trigger_schema,
   trigger_name,
   action_statement
FROM
   information_schema.triggers

-- aqui vc cria sua condicao se quiser --
WHERE
   trigger_name = "batatas"

By creating the Trigger

According to the manual

https://dev.mysql.com/doc/refman/5.7/en/create-trigger.html

there is no specific way to condition creation to existence or not, but there is also no risk of creating two triggers with the same name, simply you will get an error return when trying.

Then it would just be a case of checking the return. If it failed, you will have the error code indicating the reason (and the code can determine if the fault was because the Trigger already exist).

Error 1359 - Trigger already exists

http://dev.mysql.com/doc/refman/5.7/en/error-messages-server.html#error_er_trg_already_exists

If you just want to avoid an error return, you can do the opposite of what you asked for. Remove the Trigger existing, and create again:

DROP TRIGGER IF EXISTS tres_pratos_de_trigo;

DELIMITER $
CREATE TRIGGER tres_pratos_de_trigo
... etc ...

But attention, in this case is the opposite of what was requested. The Trigger old will be eliminated, and only worth the new.