[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Partial Commit

Posted on 2001-09-18
6
Medium Priority
?
1,911 Views
Last Modified: 2013-12-12
I would like to write a stored proc.
and update a table in the stored proc.
and there is a trigger on that table to log the process.
No matter the stored proc is successed or failed.
the Log should be commited.

How to rollback back the stored proc updated data but commit the log written by the trigger.

Thank You.
0
Comment
Question by:keith_leung
6 Comments
 
LVL 27

Accepted Solution

by:
kretzschmar earned 300 total points
ID: 6490203
use an
AUTONOMOUS_TRANSACTION
like in this sceleton

PROCEDURE Ins_log(
  What_ever_parameters you have
  )
IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO LOG_TABLE (FieldList)
    VALUES (ValueList);
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
END;

meikl ;-)
0
 
LVL 4

Expert Comment

by:fva
ID: 6490223
If you are running 8i you can use Autonomous Transactions.
Write a procedure (eventually inside a package and add the
    PRAGMA AUTONOMOUS_TRANSACTION;
line in its declaration section.
Basically, when you call that proc from a transaction, the current transaction is suspended and a new embedded one is started. The new one ends when the proc ends and then the main one resumes. Inside the proc you can commit as you like without any immediate impact on the main transaction.

Setup that proc to make the logging and commit, then call it from the trigger.

F.
0
 
LVL 4

Expert Comment

by:fva
ID: 6490229
Sorry meikl for duplication of idea. It took me some time to write the comment between other things I'm doing concurrently :).

F.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Expert Comment

by:mona1974
ID: 6490262
The benefit of autonomous transactions for database triggers is that inside those triggers you can now issue COMMITs and ROLLBACKs, statements that are otherwise not allowed in database triggers. The changes you commit and roll back will not affect the main transaction that caused the database trigger to fire. They will only apply to DML activity taking place inside the trigger itself (or through stored program units called within the trigger).

CREATE OR REPLACE TRIGGER bef_ins_table1
BEFORE INSERT ON table FOR EACH ROW
DECLARE
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   INSERT INTO logtable VALUES (
      :new.name, 'BEFORE INSERT', SYSDATE);
   COMMIT;
END;

With this trigger in place, every insert attempt can be tracked, as shown in the steps below:

BEGIN
   INSERT INTO table VALUES ('Mael', 2700);
   INSERT INTO table VALUES ('Express', 3300);
   ROLLBACK;
END;
/

SELECT name, description,TO_CHAR (timestamp,
          'MM/DD/YYYY HH:MI:SS')
  FROM logtable;  
NAME         DESCRIPTION           TIMESTAMP
-----      --------------------- -------------------
Mael            BEFORE INSERT      03/17/1999 04:00:56
Express         BEFORE INSERT      03/17/1999 04:00:56


Pragma AUTONOMOUS_TRANSACTION do not exist in ORACLE7.
0
 
LVL 1

Expert Comment

by:mona1974
ID: 6490320
Sorry kretzschmar. Points should be yours.
0
 

Author Comment

by:keith_leung
ID: 6492266
Is there any way do the same thing on Ver 7.3.4 ?
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question