Solved

Partial Commit

Posted on 2001-09-18
6
1,636 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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle - Stored Procedure Privilge access 3 54
Schema creation in Oracle12c 6 48
ORA-04071: missing BEFORE, AFTER or INSTEAD OF keyword 2 48
Oracle collections 15 23
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…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
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.

803 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