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:


Delete
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.

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).

Sorry, should have posted that in the first place.

Here is my definition:

CREATE TRIGGER trigger_test
AFTER DELETE ON OrderDetail
REFERENCING OLD AS o
FOR EACH ROW MODE DB2SQL
-- 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.