| IMPLEMENTING COMMIT IN TRIGGERS [message #220599] |
Wed, 21 February 2007 06:14  |
sv.venkat
Messages: 12
Registered: July 2006
Location: HYDERABAD
|
Junior Member |
|
|
DEAR FRIENDS,
I HAVE A AFTER INSERT TRIGGER WHICH INTURN INSERT INTO LOG TABLE SOME DATA WHENEVER ASSOCIATED EVENT FOR TRIGGER OCCURS.WITHOUT USING AUTOMONOUS TRANSACTION(8i) COULD WE COMMIT THE CHANGES MADE BY THE TRIGGER EVENT.
THANKS IN ADVANCE.
BYE
VENKAT
|
|
|
|
|
|
| Re: IMPLEMENTING COMMIT IN TRIGGERS [message #220610 is a reply to message #220599] |
Wed, 21 February 2007 06:56  |
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
|
Senior Member |
|
|
A trigger cannot do an explicit commit unless within an Autonomous Transaction.
Why would you not want to use Autonomous ?
Simply create a procedure which populates your log table inside an Autonomous Transaction and call the procedure from within your trigger.
|
|
|
|
| Re: IMPLEMENTING COMMIT IN TRIGGERS [message #220619 is a reply to message #220610] |
Wed, 21 February 2007 07:29 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Well one reason for not using them is if the transaction is distributed (uses Database Links) then you can't use Autonomous Transactions.
What you can do is set up a pipe using DBMS_PIPE, and then have a process listening to the far end of the pipe, which will insert anything it gets from the pipe into a log table.
This is the old fashioned way of doing asynchronous commits.
|
|
|
|
|
|
| Re: IMPLEMENTING COMMIT IN TRIGGERS [message #225004 is a reply to message #224999] |
Fri, 16 March 2007 08:59 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
It is an independent transaction that starts in the same session as your current transaction. Neither transaction can see uncommited data from the other.
You can make any procedure or function act as an Autonomous Transaction by including the line PRAGMA autonomous_Transaction; in the declaration. You need to include a COMMIT or ROLLBACK at the end of the procedure/function.
Read more about them here
|
|
|
|
| Re: IMPLEMENTING COMMIT IN TRIGGERS [message #225012 is a reply to message #220599] |
Fri, 16 March 2007 09:58 |
ziggy25
Messages: 206
Registered: July 2005
|
Senior Member |
|
|
Please see this example
CREATE OR REPLACE TRIGGER Scheduled_Jobs_After_Insert
AFTER INSERT ON CCS_SCHEDULED_JOBS
BEGIN
pck_job_scheduler.run_errors_report(sysdate);
pck_job_scheduler.log_error(sysdate);
END;
/
PROCEDURE run__errors_report(date_in DATE, sequence_no_in CCS_SCHEDULED_JOBS.job_id%Type)
IS
BEGIN
pck_batch.write_batch_job_log('Job Scheduled Successfully',
'ERRORS_REPORT',
'S',
SYSDATE);
END run_billing_errors_report;
If i change the run_errors_report procedure to the following
PROCEDURE run_errors_report(date_in DATE, sequence_no_in CCS_SCHEDULED_JOBS.job_id%Type)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
ccsuk.pck_batch.write_batch_job_log('Job Scheduled Successfully',
'ERRORS_REPORT',
'S',
SYSDATE);
END run_errors_report;
Does this mean that the when the trigger is active it will run both procedures in parallel? Will it still wait for the first procedure to finish before it calls teh second one?
|
|
|
|
| Re: IMPLEMENTING COMMIT IN TRIGGERS [message #225019 is a reply to message #225012] |
Fri, 16 March 2007 10:16 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
You stay within the same session, you just start up a second transaction.
So no, it will not process anything in parallel, and yes, the original transaction will wait until the autonomous transaction has finished before continuing.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
