mysqltriggersphpmyadmindatabase-triggerraiserror

phpMyAdmin trigger gui checking age

I want to check whether age is greater than 18 years before inserting the record to 'employees' table. I'm using phpMyAdmin gui trigger tool. But it gives following error when I type this script in Definition section.

BEGIN
    IF (DATEDIFF(CURRENT_DATE(),NEW.birth_date) < 6570) THEN
        RAISEERROR('Age is less than 18 years!',16,1)
        ROLLBACK
    END IF
END

error message

Please help me to resolve this.

Solution

  • RAISEERROR and ROLLBACK are used in TSQL (Microsoft SQL Server) syntax.

    In the case of MySQL, we use SIGNAL .. SET MESSAGE_TEXT .. to throw an exception inside the Trigger:

    BEGIN
    IF (DATEDIFF(CURRENT_DATE(),NEW.birth_date) < 6570) THEN

      -- Throw Exception
      SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Age is less than 18 years!';

    END IF;  -- A semicolon (delimiter) is missing here
END

    To signal a generic SQLSTATE value, use '45000', which means “unhandled user-defined exception.”

    The error values that are accessible after SIGNAL executes are the SQLSTATE value raised by the SIGNAL statement and the MESSAGE_TEXT and MYSQL_ERRNO items.