Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2008

    Unanswered: After delete trigger and cascade delete constraint

    Is an after delete trigger allowed on a table that has a foreign key constraint with a cascade delete action?

    To test, I've defined an Order and OrderDetail table, and I'm trying to add an after delete trigger on the OrderDetail table, but i'm getting the error:

    ... "Constraint rule DELETE CASCADE is not allowed with a delete trigger."...

    However, I read the following from an IBM redbook:

    A delete trigger is activated if a database row is deleted by using the SQL statement
    DELETE or a delete in native I/O, etc. A delete trigger can also be fired as result of a
    referential constraint clause ON DELETE CASCADE.
    For example, you defined in your constraint that all Order Detail rows must be deleted if
    the associated header is deleted. Additionally, you put an AFTER DELETE trigger on the
    Order Detail table. When deleting the Order Header, this trigger will be fired.

    I don't see why you can't have a delete trigger on a table that has a cascade delete action.

  2. #2
    Join Date
    Jan 2007
    Jena, Germany
    How do you define the trigger exactly?

    The redbook says that it doesn't matter where the DELETE operation on the table (for which the trigger is defined) comes from. Your application can either delete directly on the table, or the application can direct the DELETE on a parent table and a referential constraint with cascading delete causes rows to be removed from the table with the trigger. So defining a trigger is orthogonal to referential constraints and your conclusion that both are related and one prevents the other is not correct. Hence, give us the exact SQL statements and error messages (along with the information which DB2 version you are using on which platform).
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Oct 2008
    Sorry, should have posted that in the first place.

    Here is my definition:

    CREATE TRIGGER trigger_test
    AFTER DELETE ON OrderDetail
    -- eventually do something useful here
    Update order set desc = '';

    It doesn't matter if i remove the referencing statement either.

    Here is the full error message:

    SQL State: 42892
    Vendor Code: -675
    Message: [SQL0675] Specified delete rule not allowed with trigger on table ORDERDETAIL in XXX. Cause . . . . . : The delete rule specified in referential constraint *N in *N on table ORDERDETAIL in XXX is not allowed for the specified trigger. Constraint rule DELETE CASCADE is not allowed with a delete trigger. Constraint rules DELETE SET NULL and DELETE SET DEFAULT are not allowed with an update trigger. Recovery . . . : Either use the RMVPFTRG command to remove the trigger, use the RMVPFCST command to remove the constraint, define the constraint with a valid delete rule, or define the trigger with a different event.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts