CODEWITHSUNDEEP
mysqltriggers
lior
lior

Reputation: 1187

Mysql create trigger if not exist

I have a script that creates triggers, I would like to run it multiple times and if a trigger already exist it needs to skip the creation part.

Is there a "create if not exist" in mysql triggers?

EDIT:

I tried :

    IF NOT EXISTS ((SELECT TRIGGER_NAME
        FROM information_schema.triggers
        WHERE TRIGGER_SCHEMA = 'xxx_admin' AND TRIGGER_NAME = 'test_AFTER_UPDATE')) 
        THEN
    CREATE DEFINER=`root`@`localhost` TRIGGER `xxx_admin`.`test_AFTER_UPDATE` AFTER UPDATE ON `test` FOR EACH ROW
        BEGIN
        INSERT INTO auditTest 
        select *, now() from test where id = NEW.id;
        END;
  END IF;

I get : syntex error " 'IF' is not valid input at this location

Upvotes: 1

Views: 1694

Answers (2)

Fathah Rehman P
Fathah Rehman P

Reputation: 8741

To check a trigger exist or not use following

SELECT *
FROM information_schema.triggers
where trigger_schema = 'schema_name'
and trigger_name = 'triggername'

Upvotes: 1

KaeL
KaeL

Reputation: 3659

You can check the INFORMATION_SCHEMA for existing triggers (credits to this answer):

SELECT trigger_schema, trigger_name, action_statement
    FROM information_schema.triggers

If a particular trigger is existing, you can then skip creating it:

IF (NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA = 'your_schema' AND TRIGGER_NAME = 'your_trigger_name'))
    CREATE TRIGGER ...

Upvotes: 0

Related Questions