Aborting record deletion in a trigger.

By: Ben Matterson

Abstract: How to abort deletion of a record based on some criterion.

Problem:

I need a BEFORE DELETE trigger that will check to see if a record should be deleted or not based on some criterion. For example, if the key value is less than 999, then do not delete the record.

I want something like this:
   SET TERM!!;
   CREATE TRIGGER my_Delete_Confirmation_Trigger
   FOR mytable ACTIVE BEFORE DELETE AS
   BEGIN
   IF (KEY < 999) THEN (abort the deletion)
   END!!
How do I implement the "(abort the deletion)" part?

Solution:

Use an exception.

Create an exception like this:
   CREATE EXCEPTION CANT_DELETE_RECORD 'This record cannot be deleted.'
The use the exception in your trigger like this:
   SET TERM!!;
   CREATE TRIGGER my_Delete_Confirmation_Trigger
   FOR mytable ACTIVE BEFORE DELETE AS
   BEGIN
   IF (KEY < 999) THEN EXCEPTION CANT_DELETE_THIS_RECORD
   END!!

Server Response from: ETNASC03