Re: Multiple triggers on the same table

Hi Tom,

> Siotos> My question is simple. I have two triggers on the same table
> Siotos> that execute the same code. Which one will be executed first?
> Siotos> Consider the case that both triggers are 'before insert or
> Siotos> update or delete'.
>
> I seem to remember reading somewhere there is no guarantee on the
> order of triggers firing when you have multiple triggers which could
> fire and that any code which depended on this would be error prone -
> in short a bad idea (TM).

That's true. There is no way to find out which triggers will be executed first.

> I don't think you have a problem here. First of all, the trigger is
> not going to delete a row - an on delete trigger is going to fire
> because the row is being deleted, not to delete. Besides, if you had a
> trigger which tried to do this, wouldn't you hit the mutating table
> problem?

Correct

> Apart from all of the above, you also have the inserting, updating and
> deleting tests which you can use in your trigger to determine what is
> happening to the row.
>
> Possibly the only real problem you would have is if you are going to
> modify a column that also gets modified by a trigger which runs after
> your trigger. this could result in your modification being lost.

Well, I do modify a column through my trigger but the good news are that the other trigger in the dissemination database doesn't know of the existence of this particular column!!!

> I have to say, I'm a bit amazed you are writing triggers for
> databases you don't have access to and from what you imply don't have
> clones of for testing etc. No matter how careful you are, there is
> bound to be problems.

It's true that I don't have access to the dissemination database and I will never get access because it's an European Union's classified database. Of course I get some feedback from the production database which looks like the dissemination. Some times you need to follow the traditional way and call them in order to get some information about the procedures/structure of the database!!!! :)

Thanx for your reply and your time.

Vangelis
Greece Received on Thu Feb 13 2003 - 10:12:16 CET