Solved

Partial Commit

Posted on 2001-09-18
6
1,672 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 100 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to recover a database from a user managed backup

861 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