472,353 Members | 1,490 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,353 software developers and data experts.

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

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

Similar topics

by: Tom Urbanowicz | last post by:
I have a trigger. Each time the triggering event fires, I want to insert some information into another 'audit-like' table (not at all related to...
by: chandan | last post by:
hi, I have a insert trigger on a table. I want commit to happen after insert before invoking the trigger. So that if a quey is made before the...
by: Neil Rutherford | last post by:
During testing of an application, i noticed a difference between SQL 2000 and SQL 7, both with identical config. In a nutshell: A table has a...
by: Peter Childs | last post by:
Is it possible to deferr a trigger until commit, Or to have the trigger not occur if the transaction is rolled back? Like transaction. I think its...
by: vijai kumar via DBMonster.com | last post by:
Hi, I am using UDB v 8.1 on win2000. I have setup the ODBC datasources. My application is connecting to the database and performing DML...
by: JohnO | last post by:
Hi Folks, I have an update trigger that fails (it inserts an audit table record) in some circumstances. This is causing the triggering...
by: JohnO | last post by:
Thanks to Serge and MarkB for recent tips and suggestions. Ive rolled together a few stored procedures to assist with creating audit triggers...
by: Frank van Vugt | last post by:
Hi, If during a transaction a number of deferred triggers are fired, what will be their execution order upon the commit? Will they be...
by: deepdata | last post by:
Hi, I am creating a trigger in DB2 express version. When i use the following syntax to create trigger CREATE TRIGGER USER_PK_TRIGGER BEFORE...
by: HotFrost | last post by:
Hello everyone, i am trying to work with linked servers... The local server is the one used by UPS worldwide software (it is Microsoft SQL...
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge...
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific...
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the...
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.