Subject Re: [ib-support] Triggers technique question
Author Paul Reeves
Marcin Bury wrote:
> Hi List
> I'm currently creating brand new database for new project and I was
> wondering about using triggers "before" or "after".
> I.e. if I have the table of invoice headers and the table of invoice items,
> when the user wants to delete the invoice (don't ask why) I should use
> "before delete" or "after delete" trigger to delete invoice items. Are there
> any rules, recommendation, safety and stability issues for choosing "before"
> or "after" triggers for updating additional tables.

Invoices are not really the best example in this case. Rather than
deleting invoices I would suggest that you mark them as cancelled. I
would be surprised if your tax jurisdiction would be happy about an
accounts system that allowed invoice deletion. Tax collectors are
suspicious people, usually ready to believe that we are all trying to
dodge something. (And they are usually right).

On the general subject...

If there is a foreign key dependency between the detail table and the
master table you will have to use the before trigger to delete the
details. If details exist still exist an error will be raised if you try
to delete the master. The deletion takes place (obviously, I suppose)
after the before triggers and before the after triggers.

If you wish to update other tables you need to consider what you are
updating and why. If, for instance, you are logging the deletion then do
that in an after trigger. If you do it in a before there is still a
possibility that the deletion will fail.


Paul Reeves
taking InterBase further