Raising an Exception in a Trigger
Go Up to Trigger Exceptions
To raise an existing exception in a trigger, use the following syntax:
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
WHENstatement, 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.';
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 ;