Re: triggers and commit

In article <38hqoj$1gu_at_nuscc.nus.sg>, Virinchipuram J. A. <fbap3120_at_leonis.nus.sg> wrote:
>It has been well documented in the mannuals, that one cannot use commit
>or rollback statments in triggers.
>
> There are two questions that I would like to ask:
> (1). The rational or why is it that commit and rollback
> are not allowed?.
> (2). In the project that I am working, I am using ms-access as client
> and oracle as server. I have situations where, I need to insert
> records into tables using triggers at the server. This works fine.
> The problem occurs when I am trying to query the new records that
> have been inserted through triggers from my client. The records don't
> seem to exist. But if, I give an explicit commit after the triggers
> have been executed, my client gets a positive answer.
>
> My question is that I want to use triggers for the above mentioned
> actions and at the same time I would like my client get positive
> answers. Is there a way out?.
>
> thanks for the help and suggestions
>
>-anand
>
>--
>Anand. V. J Internet:fbap3120_at_leonis.nus.sg
>Dept Of Decision Sciences
>National University Of Singapore
>

1. I cant think of a reason why you would want to have transaction control statements in a trigger. Remember that transaction control statements apply to the effects of all statements of the current transaction and not just the trigger. (To rollback just the effects of the trigger body (and triggering statement) raise an exception.
2. Hmm....In a given session you should be able read all the changes made to the database before a commit. Ofcourse other sessions may not see this until a commit. Two exceptions here are
3. If you use DISCRETE TRANSACTION option.
4. Stetements of a row trigger cannot read(or modify) a mutating table of the triggering statement.

   So is your MS access client starting two different sessions to the server ?

• Amit