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);