raise application error Trigger in MySQL DBMS

Question

I am dealing with triggers in MySQL, and I want to add the raise application error, but my code indicates :

Error code 1064, SQL state 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(-20000,'Pay is below Texas minimum wage!');

END IF;

END' at line 9

and if I remove the part of raise application error, it works perfectly .

Trigger:

DELIMITER @@
DROP TRIGGER IF EXISTS gmtt.after_update_mcorr @@

CREATE TRIGGER gmtt.after_update_mcorr
AFTER UPDATE ON gmtt.mcorr
FOR EACH ROW
BEGIN
       IF OLD.etat = '0' AND NEW.etat = '1' THEN
            INSERT INTO historique(message, User, dateHisto) VALUES (CONCAT( 'a achevé la Maintenance ', OLD.codeMaint) , CURRENT_USER(), NOW()); 
       ELSE
raise_application_error(-20000,'Pay is below Texas minimum wage!');     

    END IF;

    END @@ 
DELIMITER ;

Wachaga Mwaura · Accepted Answer · 2019-04-19 12:58:12Z

9

Your syntax appears to be MySQL. And yet, raise_application_error is an Oracle construct. You want signal, documented here:

DELIMITER @@
DROP TRIGGER IF EXISTS gmtt.after_update_mcorr @@
CREATE TRIGGER gmtt.after_update_mcorr
AFTER UPDATE ON gmtt.mcorr
FOR EACH ROW
BEGIN
   IF OLD.etat = '0' AND NEW.etat = '1' THEN
        INSERT INTO historique(message, User, dateHisto)
             VALUES (CONCAT( 'a achevé la Maintenance ', OLD.codeMaint) , CURRENT_USER(), NOW()); 
   ELSE
       signal sqlstate '-20000' set message_text = 'Pay is below Texas minimum wage!';     
   END IF;
END @@ 
DELIMITER ;

edited Apr 19, 2019 at 12:58

Wachaga Mwaura

3,6503 gold badges31 silver badges33 bronze badges

answered Apr 14, 2016 at 1:40

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

raise application error Trigger in MySQL DBMS

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related