469,271 Members | 1,484 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,271 developers. It's quick & easy.

Trigger after the commit

I would like to know, how to get a trigger executed only after the commit is performed.
I have a table A which has a trigger tigg_A. In this trigger it would insert a record in Table B.
Now i want run a trigger Trigg_B on table B only after the commit is performed for the transaction which caused the insertion of record on Table B(ie the transaction which performed the Trigg_A)
Feb 12 '10 #1
5 20043
135 100+
If you want to implement such kind of functionality then don't use the triggers. You can do this by using procedure/function since the trigger gets called before/after DML regardless of commit/rollback of DML.
Feb 12 '10 #2
8,127 Expert 4TB
Try to use PRAGMA AUTONOMOUS TRANSACTION in the trigger at your own risk.
Feb 15 '10 #3
I guess the Pragma Autonomous and Function/procedure will not work in my scenario.Let me explain the scenario in detail.

I have 3 tables here 1. "Transaction" 2."Shipment" 3."Staging Table" and a Package "Interface"

Whenever I do a shipment "Transaction" Table will execute the trigger on it "Tran_Trigger" . In this trigger,It will insert the current Transaction details in "Staging Table".Once the records get committed, I need to call the package "Interface" which would pick up the data from "staging table" and update all transaction related tables(detail table) accordingly.
If I call the this package inside the "Tran_trigger", it will not be able to update all the trasaction detail tables as the data is not yet committed and as a result the data in detail table is out of sync.I would be able to call the "Interface" package only after the transaction is commited and all details tables are updated.
So whenever a commit is established i can immediately call the "interface" package otherwise it will cause a delay(if i schedule the package)
Feb 16 '10 #4
135 100+
Why do you think PRAGMA AUTONOMOUS_TRANSACTION will not work in your case. When "Tran_Trigger" gets called it will COMMIT your "STAGING TABLE". After that you can COMMIT your remaining transaction details and can call the package. If this also will not work in your case then post the code you have done so far.
Feb 17 '10 #5
What you said is true.But let explain why this wont work in my case.
The Shipment is happening in SIM(a system which i do not have access) during the shipment it triggers the "Transaction" table in RMS(where my program written) and the staging table is getting poulated.
Once the "transaction" table is updated, it will do some updation in SIM and finally get committed.Only after this point of time i can call my package (parameters are picked up from staging table data) as the entire transaction/shipment related tables are up to date.
Since i'm not sure when the transaction exactly getting completed or getting commited(as it is happening in SIM) i wont be able to call my package immediately after the process(shipment) completed. I could call the package on an intervel by using DBMS_JOBS but this will couse a delay as the job runs on a intervel and not immediately after the shipment transaction is completed.
My idea is, as soon as the transaction is completed(commited), i want to call the package so that there is no delay
Feb 18 '10 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Tom Urbanowicz | last post: by
11 posts views Thread by Peter Childs | last post: by
reply views Thread by JohnO | last post: by
1 post views Thread by deepdata | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.