Общности Articles Блогове Ресурси Сваляне Помош

Aborting record deletion in a trigger.

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!!