Home » SQL & PL/SQL » SQL & PL/SQL » IMPLEMENTING COMMIT IN TRIGGERS
IMPLEMENTING COMMIT IN TRIGGERS [message #220599] Wed, 21 February 2007 06:14 Go to next message
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 #220602 is a reply to message #220599] Wed, 21 February 2007 06:25 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,


 i thnik its not possible without autonomous_transaction.




regards,
Re: IMPLEMENTING COMMIT IN TRIGGERS [message #220610 is a reply to message #220599] Wed, 21 February 2007 06:56 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #224999 is a reply to message #220619] Fri, 16 March 2007 08:28 Go to previous messageGo to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
What exactly is an autonoumous transaction?
Re: IMPLEMENTING COMMIT IN TRIGGERS [message #225004 is a reply to message #224999] Fri, 16 March 2007 08:59 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.
Re: IMPLEMENTING COMMIT IN TRIGGERS [message #227891 is a reply to message #224999] Fri, 30 March 2007 01:16 Go to previous message
sv.venkat
Messages: 12
Registered: July 2006
Location: HYDERABAD
Junior Member
Autonomonus Transaction is a child transaction in the context another transaction which will be committed or rolled back irrespective of the parent transaction.
OK
bye
Venkat.
Previous Topic: String manipulations
Next Topic: Procedure to drop all the user tables in the schema .
Goto Forum:
  


Current Time: Sat Dec 10 20:24:02 CST 2016

Total time taken to generate the page: 0.08191 seconds