Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

raise application error Trigger in MySQL DBMS

Tags:

mysql

triggers

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 ;
like image 789
Bashir Avatar asked Apr 14 '16 00:04

Bashir


People also ask

What is raise application error?

The RAISE_APPLICATION_ERROR procedure accepts user-defined error-number values from -20000 to -20999. The SQLCODE that is returned in the error message is SQL0438N.

What is the difference between raise and raise application error?

You can RAISE your own exception but if you want to associate your own custom error message with the exception you would use RAISE_APPLICATION_ERROR.

What must you do to throw the call raise application error?

The procedure raise_application_error allows you to issue an user-defined error from a code block or stored program. By using this procedure, you can report errors to the callers instead of returning unhandled exceptions. In this syntax: The error_number is a negative integer with the range from -20999 to -20000 .

What is the difference between Pragma Exception_init and RAISE_APPLICATION_ERROR?

EXCEPTION_INIT is helps to assign a error number to the exception and which can be raised using RAISE and can be handled in EXCEPTION block. RAISE_APPLICATION_ERROR is used to return a valid error message to the calling application like Java, .


1 Answers

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 ;
like image 82
Gordon Linoff Avatar answered Nov 14 '22 23:11

Gordon Linoff