Raising an Exception in a Trigger

Go Up to Trigger Exceptions

To raise an existing exception in a trigger, use the following syntax:

EXCEPTION name;

where <name> is the name of an exception that already exists in the database. Raising an exception:

• Terminates the trigger, undoing any changes caused (directly or indirectly) by the trigger.
• Returns the exception message to the application which performed the action that fired the trigger. If an isql command fired the trigger, the error message is displayed on the screen.

Note: If an exception is handled with a WHEN statement, it will behave differently. For more information on exception handling, see Working with Stored Procedures.

For example, suppose an exception is created as follows:

CREATE EXCEPTION RAISE_TOO_HIGH 'New salary exceeds old by
 more than 50%. Cannot update record.';

The trigger, SAVE_SALARY_CHANGE, might raise the exception as follows:

CREATE TRIGGER SAVE_SALARY_CHANGE FOR EMPLOYEE
AFTER UPDATE AS
DECLARE VARIABLE PCNT_RAISE;
BEGIN
PCNT_RAISE = (NEW.SALARY - OLD.SALARY) * 100 / OLD.SALARY;
IF (OLD.SALARY <> NEW.SALARY)
 THEN
IF (PCNT_RAISE > 50)
 THEN EXCEPTION RAISE_TOO_HIGH;
ELSE
BEGIN
INSERT INTO SALARY_HISTORY (EMP_NO, CHANGE_DATE,
 UPDATER_ID, OLD_SALARY, PERCENT_CHANGE)
VALUES (OLD.EMP_NO, 'NOW', USER, OLD.SALARY,
 PCNT_RAISE);
END END ;