Trigger Exceptions
Go Up to Triggers (Data Definition Guide)
An exception is a named error message that can be raised from a trigger or a stored procedure. Exceptions are created with CREATE EXCEPTION, modified with ALTER EXCEPTION, and removed from the database with DROP EXCEPTION. For more information about these statements, see Working with Stored Procedures.
When raised in a trigger, an exception returns an error message to the calling program and terminates the trigger, unless the exception is handled by a WHEN statement in the trigger. For more information on error handling with WHEN, see Working with Stored Procedures.
For example, a trigger that fires when the EMPLOYEE table is updated might compare the employee’s old salary and new salary, and raise an exception if the salary increase exceeds 50%. The exception could return an message such as:
New salary exceeds old by more than 50%. Cannot update record.
- Important: Like procedures and triggers, exceptions are created and stored in a database, where they can be used by any procedure or trigger in the database. Exceptions must be created and committed before they can be used in triggers.