Commit in Oracle trigger??

Hi,

I want to achieve this:
If ATable has new record, fire the AFTER INSERT row level trigger. And inside the trigger will insert the ATable's primary key into BTable. Then inside trigger, wait for 120 seconds:

INSERT INTO BTable VALUES (:NEW.ATableKey);
DBMS_LOCK.SLEEP(120);

During this 120 period, i would like to let other table read the ATable primary key data in BTable. However, this fail because commit is not yet performed on BTable.

So, BTable will have no data until 120 seconds is over. So, i would like to commit the insertion to BTable in ATable's trigger. Is this possible?


Thanks,

With a simple:
But check if the triggers need an authonomous transaction or not.
For reference see this

While this may be technically possible, you might want to consider a different technique. Without knowing specifically what you are trying to accomplish, it is difficult to comment.

Just be very careful when using autonomous transactions and give special consideration to what happens if, for some reason, the insert trigger fails for Table A. The autonomous transaction is going to perform the commit, perhaps with bad or otherwise unusable data.

FYI

A common misuse of autonomous transactions is to eliminate mutating errors that occur when trying to perform a select on the same table that is being modified.

A common practical use is for creating log entries whether or not the calling commit fails.

Now i have another issue.....

I have two tables: TableA, TableB, TableC

TableB will keep track the TableA's primary key.

TableC will have AFTER INSERT row level trigger that will execute by checking th primary key of TableA in TableB.

I run these insert statements in one script:
INSERT INTO TableA VALUES (...);
COMMIT;

INSERT INTO TableC VALUES (...);
COMMIT;

Found that after insert statement executed on TableA,
it wait for 120 secs.

During 120 secs, yes, the TableB will have primary key of TableA. (using autonomous transaction)

But Then the TableC trigger is not being executed. Because TableC insert statement come after
TableA trigger.

When 120 secs is over, which is too late, because the TableA have finish execution.

I would like to achieve this:
While during 120 secs, insert the data into TableC.


Is this possible?


Thanks.

Without knowing EXACTLY what you are trying to accomplish, I will hazard a guess that you may want to reconsider this obviously error-prone method.

Consider this alternative ( again I may be off because of lack of information ).

It sounds like you could better accomplish your desired results using transactions.

Basically, you begin a transaction, perform one or more operations and end the transaction. The concept is to be able to "roll-back" at any point during the transaction should something fail.

If this is way off-base and you still need help, could you explain in words ( not syntax ) what you are trying to accomplish ?