At 04:36 AM 14/01/2010, lseckde wrote:

>Hi,
>
>I have an question about triggers, where triggers doesn't behave like i excepted.
>
>I have an AFTER DELETE POSITION 10 trigger
>
>in this trigger I would like to do an action, if an record has been deleted.
>
>This works fine, until the record could not be deletet because of an foreign key constraint. In this case the trigger is called, also the delete will not be carried out.
>
>I would have expected that the check constraints (which are as far as I know also implemented as Triggers) will be called before the delete?
>
>I would except the following order:
>any user "before delete" trigger
>system check triggers
>any user "after delete" trigger
>
>Is there an way to get the above behavior? (Trigger at a point of no returne)
>
>(I'm using firebird (2.1.3.18185))

No. Actually, your sequence omits the deciding step: the writing of the new record version to disk, which is attempted after the BEFORE triggers have executed.

"before delete" triggers
attempt to write to disk in transaction's isolated space
--> system check triggers
--> on failure, throw exception and pass control back to client
[no failure: proceed
"after delete" triggers

This is intentional. A DML operation either succeeds totally, or fails totally. Thus, if a constraint check fails, no changes are written at all, execution stops, and state reverts to that which existed prior to the Before triggers.

>Is there an way to get the above behavior? (Trigger at a point of no returne)

No. In an ACID-compliant database system, a declared constraint is applied unconditionally. If you have a situation where a declared constraint interferes with what you need to do, remove the constraint and write your own integrity rules in a trigger.

./heLen