Re: What constitutes a Transaction ?

Dave Young wrote:
>
> If I insert into 4 other tables (C,D,E and F which could have been done by 4
> different procedures nested from the original trigger on table B) and the
> insert on F fails (which there will be an exception routine for) will C,D and E
> be rollbacked ? Does the ON ERROR clause automatically consitute a rollback and
> if so from what point? From the entry into that particular procedure or from
> the beginning trigger ? What manual do I find these answers ?

I don't claim to be an expert on this subject, but I have been doing some work recently with triggers and have done something similar to you in that I set up an after insert-update-delete trigger on table A that inserts/updates/deletes corresponding rows in table B. I have verified that if the update to table B is failed using raise_application_error(), the update to table A is rejected as well. I would suspect that any raised exceptions in a trigger, whether triggered programmatically or by the database itself, would function in this same manner. Check the "Oracle7 Server Application Developer's Guide," which has a detailed discussion of trigger behavior.

Regarding ON ERROR clauses, in "regular" PL/SQL, not called by a trigger, you are expected to code your own rollback statement.

-- 
John Verbil
U S WEST Communications
Information Technologies
jverbil_at_uswest.com
(303) 896-0916