Re: Which trigger fires first?

A copy of this was sent to "Peter Laursen" < ptl_at_edbgruppen.dk> (if that email address didn't require changing) On 30 Jul 1999 08:49:02 GMT, you wrote:

>If there exists two triggers on a table that are both 'before insert or
>update' how can I know which trigger fires first?
>Can I specify a sequence, ie tell the db i want this trigger to fire first?
>

they are defined to have NO defined firing order. Triggers of the same type fire in an arbitrary order (its not really arbitrary, but it is subject to change and you should never depend on it)

>I my db I have on each table a 'before insert or update' trigger that
>fetches a sequence.nextval into the primary key and updates others fields
>common to all tables. These triggers are all alike and made after a
>template. I dont want to edit these individually.
>
>However i want to write triggres to enforce constraints on individual
>tables like:
>create table t(a int primary key, b int references t)
>with the rule that b may be null, and at most one row can be referenced by
>another.
>How do I write an efficient trigger to enforce this? (assuming a trigger as
>mentiod above is in place)
>

the the rule that "b may be null" -- you already have that in place. since you did not specify "b int NOT NULL...", b is allowed to be null already.

for the "at most one row can be referenced by another" do you mean you are trying to enforce a 1-1 relationship? (a parent has at most one child and a child has at most one parent?) if so, just put a unique constraint on the childs foreign key -- thats the most efficient way to enforce that particular rule. You'll find that to implement this rule in a trigger is infinitely more difficult then at first appears in a multi-user system.

>Thanks
>Peter
>
>

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Jul 30 1999 - 06:41:38 CDT