?
Solved

Partial Commit

Posted on 2001-09-18
6
Medium Priority
?
1,835 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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

Technology Partners: 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!

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

752 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