8. SQL Statements : RAISE ERROR : RAISE ERROR Example
 
Share this page                  
RAISE ERROR Example
The following example enforces a relationship (or integrity constraint) between an employee and a manager. When an employee is entered into the database, a check is performed to enforce the existence of the manager of the employee. If the manager is not found, the Raise Error statement returns a message to the user and rolls back the changes made to the database by the statement that fired the rule.
CREATE PROCEDURE validate_manager
       (mname VARCHAR(30)) AS
DECLARE
       msg VARCHAR(80) NOT NULL;
       check_val INTEGER;
BEGIN
       SELECT COUNT(*) INTO :check_val FROM manager
              WHERE name = :mname;
       IF check_val = 0 THEN
              msg = 'Error 99999: Manager "' + :mname +
                     '" not found.';
RAISE ERROR 99999 :msg;
ENDIF;
END;

CREATE RULE check_emp AFTER INSERT INTO employee
EXECUTE PROCEDURE validate_manager
       (mname = new.manager);