6

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?

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

Improve this question

3 Answers 3

Reset to default
4

There is no CREATE TRIGGER IF NOT EXISTS

There is DROP TRIGGER IF EXISTS

You must execute the trigger's creation as follows:

USE xxx_admin
DELIMITER $$
DROP TRIGGER IF EXISTS test_AFTER_UPDATE $$
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 $$
DELIMITER ;

GIVE IT A TRY !!!

As for you error, using IF like that is not valid from the CLI.

Improve this answer
3
  • 1
    since we run it in every new version it will mean some information is lost until the trigger is created? Commented Aug 25, 2015 at 10:25
  • You need to schedule downtime of 1 min to do this. That will ensure no data loss. Commented Aug 25, 2015 at 17:47
  • I'm looking for a way that does not drop the trigger to re-create it.. I don't want any downtime if there is not reason.. the trigger does not change Commented Oct 4, 2019 at 8:25
2

For MySQL 8.0 there is CREATE TRIGGER IF NOT EXISTS syntax:

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

Improve this answer
1 
CREATE TRIGGER `delete_from_code` BEFORE DELETE ON `users`
 FOR EACH ROW BEGIN

delete from  code
where  nom = old.nom  ;

END

YOU CAN CREATE TRIGGERS IF NOT EXISTS LIKE THIS : –

String query11 ="CREATE TRIGGER IF NOT EXISTS delete_prix_pack BEFORE DELETE ON users\n" + 
" FOR EACH ROW BEGIN\n" + 
"delete from prixpacketgros\n" + 
"where nom = old.nom ;\n" + 
"END" ; 
executeSQLQuery(query11);
Improve this answer
1
  • Please edit your answer to add more details. I have done that for you, it's waiting for review and should appear shortly. Commented Mar 19, 2023 at 11:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.