Re: Trigger behaviour

It all depends on how you code the trigger.....

If you use the "for each row" clause then the trigger will fire for every row of data affected by your transaction in the table where the trigger resides.

If you omit this clause the trigger will fire once for the transaction.

What you have to bear in mind is that more than one user might be executing transacitons in the database at the same time. This is why you cannot use max(value)+1 for generating primary key values in an insert trigger - since if two transaction execute the trigger at the same time they will get the same value - and one transaction will fail with a primary key violation.

If you want a mechanism to ensure that only one transaction can perform this operation at a time you can take out an exlcusive lock on a table, or

use the DBMS_LOCK package.

Hope that helps

Graham

Simon Irvin wrote:

> I am in the process of writing a trigger which makes uses of the value
> of a package variable and then sets it to null. However a colleague
> tells me that, atleast upto version 7, the database engine might
> execute the trigger more than once (with rollbacks to savepoints in
> between the multiple calls) while attempting the same update. This
> raises the possibility that the trigger may try to read the variable
> again having already set it to null.
>
> I'd be grateful for advice as to whether or not this could happen and
> if so whether anything has changed in Oracle 8 to change this
> behaviour.
>
> Cheers
>
> Simon Irvin
  Received on Wed Jun 27 2001 - 17:32:01 CDT