Subject Re: [firebird-support] a doubt about "cascade" triggers & exceptions
Author Alexandre Benson Smith
Sergio H. Gonzalez wrote:
> Hello, I want to do some AFTER_DELETE triggers on
> some tables to clean some other tables and to check if the delete can be done.
> TABLE1_AFTER_DELETE runs a delete on TABLE2 or raises an exception, depending on
> some condition.
> TABLE2_AFTER_DELETE runs a delete on TABLE3 or raises an exception...
> if TABLE3_AFTER_DELETE raises an exception, would all the previous delete
> statement be rolled back?


> By the way: which is the best way to do this task? ISTR (don't remember where
> I read it) that is better to use triggers than foreign key to mantain the DB
> consistence. Is it right?

I don't think so...

I prefer FK's, and the trigger approach will be fault because of
transaction isolation view of the data.

FK's see a kind of dirty-read, triggers don't.

> Or should I create all FK for this cases? And with FK,
> a fail in the last delete, would rollback al the previous ones?


> Thanks for all the help!!
> -sergio

see you !

Alexandre Benson Smith
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil