Re: triggers and commit

I don't speak for Oracle and won't until they promote me five or six more times.

In article <D1u6In.zo_at_sybase.com> pcoll_at_sybase.com writes:

>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.

Sigh.

Mr. Coll,

In the real world this is implemented as a pair of update statements in a single transaction. When the second statement fails, the entire transaction rolls back.

Since you seem to be advocating allowing rollback and commit statements in triggers, could you tell us how you think the following case should be handled?

(I don't have my manuals with me, so excuse any syntax errors.)

ON ROW PRE-DELETE on ORDERS table:

DELETE FROM ORDER_LINES OL WHERE OL.ORDER_ID = OLD.ORDER_ID; IF (ERROR)
        ROLLBACK;
INSERT INTO LOG_TABLE VALUES ('Here is what happened'); COMMIT;

If we delete a row from the ORDERS table and the delete on ORDER_LINES fails, then what do you want to happen?

Should a row be added to the LOG_TABLE despite the rollback? This does incredible violence to the entire concept of a transaction.

How does Sybase handle this?

>In article <CyAn58.LzM_at_nl.oracle.com>, <cgohring@lucifer> writes:
 

>> 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?.

The rationale is that an SQL statement, together with any triggers that get fired, is an atomic transaction in the database. Allowing commits and rollbacks eliminates this and makes it possible for such a statement to be executed as a series of atomic transactions. This can produce all kinds of problems.

>> : (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?.

Is there any reason why you are not committing after the transaction that fires the triggers? Received on Wed Jan 04 1995 - 08:28:39 CET