Partial Commit

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.
keith_leungAsked:
Who is Participating?
 
kretzschmarConnect With a Mentor Commented:
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
 
fvaCommented:
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
 
fvaCommented:
Sorry meikl for duplication of idea. It took me some time to write the comment between other things I'm doing concurrently :).

F.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
mona1974Commented:
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
 
mona1974Commented:
Sorry kretzschmar. Points should be yours.
0
 
keith_leungAuthor Commented:
Is there any way do the same thing on Ver 7.3.4 ?
0
All Courses

From novice to tech pro — start learning today.