Re: triggers and commit
Date: Tue, 3 Jan 1995 23:36:00 GMT
Message-ID: <D1u6In.zo_at_sybase.com>
Lets move into the real world - I have a debit/credit transaction and perform the debit from one account, but find the the account to be credited does not exist. Rollback would be nice, and the natural way to do it. Having taken 7 years to get triggers, I guess another 7 should make them work.
In article <CyAn58.LzM_at_nl.oracle.com>, <cgohring_at_lucifer> writes:
> Subject: Re: triggers and commit
>
> fbap3120_at_leonis.nus.sg (Virinchipuram J. A.) writes:
> : 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
> :
>
> 1) commit and rollback are meant to commit or rollback DML statements.
> By definition a trigger occurs in the middle of a DML statement. The
> concept of commiting or rolling back in the middle of the execution of a
> DML statement does not make sense.
>
> 2) The DML statements in a trigger occur within the same transaction as the
> DML statement which triggered the trigger. Hence, they are not commited
> until the session which invoked the DML which triggered the trigger issues
> a commit. Hence, the session that issued that DML which triggered the
> trigger will be the only session that will see the DML operations that
> occured in the trigger. There is no way around this. This is the correct
> operation of the RDBMS. One suggestion that I might is to, instead of
> issuing DML in the trigger, use the DBMS_PIPE package owned by SYS to
> send a message to another session, possibly a C program, or PL/SQL
program.
> Upon receiving the message, this session can perform the required DML and
> commit it. Since this is being performed by another session, it is
another
> transaction, and hence the commit will only affect those DML statements,
and
> not the DML statements in the transaction that sent the message.
>
> --
> regards,
> Carl
>
+-----------------------------------------------------------------------------+
> Carl Gohringer, European Development Centre
> Oracle Park, Bittams Lane, Chertsey, Surrey, England, KT16 9RG
> Internet : cgohring_at_uk.oracle.com
>
+-----------------------------------------------------------------------------+
>
Received on Wed Jan 04 1995 - 00:36:00 CET