?
Solved

Basic question of transaction

Posted on 2011-10-18
9
Medium Priority
?
381 Views
Last Modified: 2013-12-18
Hello,
I have to optimize some stored procedures and have a basic question about transactions.

Scenario:
- There are 2 tables: tbl_Father and tbl_Child.
- Field of Foreing key for both tables: cod_op
- tbl_Child has 2 triggers: trg_Update (fires when updating table) and trg_Delete (fires when deleting  rows on table)

My question is how to handle transaction in a stored procedure for deleting rows on tbl_Father.
The comments were made from the programmer.

PROCEDURE Delete_Rows_Father (i_cod_op        IN     INTEGER,
                i_user          IN     INTEGER,
                o_return        OUT INTEGER)
   IS
   BEGIN

      -- Update field "x_user_del" because trigger "trg_Delete" need it for filling some
      -- audit table
      UPDATE tbl_Child
         SET x_user_del = i_user
       WHERE     cod_op = i_cod_op;

      COMMIT; -- commit is needed so when trigger trg_Delete be fired,
                   -- then it will find field "x_user_del" not null.

      -- with this delete command, trg_Delete is fired and audit table is filled
      -- it's needed to delete this rows in order to delete rows in tbl_Father.
       DELETE tbl_Child
       WHERE     cod_op = i_cod_op;

      -- Now I can delete rows in tbl_Father
      DELETE tbl_Father
       WHERE     cod_op = i_cod_op;

      COMMIT;
      o_return := 1;
   EXCEPTION
      WHEN OTHERS
      THEN
         BEGIN
            ROLLBACK;
            o_return := 0;
         END;

   END Delete_Rows_Father;

There are two commits in the stored procedure. If there's a rollback, it will get back only after first commit.

Programmer says that he realized (after trial & error) that he needs to commit after Update tbl_Child, because if not, then trigger "trg_delete" will find field "x_user_del" empty and will give problems.

What's the best way to deal with triggers and transaction?
0
Comment
Question by:miyahira
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 36987132
triggers fired within the same session as the delete do not need the commit  - your developer's comment is wrong


remove the commits, or at most, use only the last one
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36987137
if that doesn't work, please provide sample data and trigger code to replicate the problem
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36987155
>>triggers fired within the same session as the delete do not need the commit

Unless the trigger is autonomous.

Please verify this with the programmer.  They might have made it autonomous to get around the mutating trigger 'issue'.

0
Industry Leaders: 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 74

Expert Comment

by:sdstuber
ID: 36987274
>>> Unless the trigger is autonomous.


Actually,  it's still true,  as the autonomous transaction will create a sub-session,
so it will still be "different" and hence not able to see your main session's changes until commit.

However,  IF THIS IS THE CASE,  it's very likely the autonomous transaction is being used incorrectly.

0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 36987829
I agree with an expanded version of sdstuber's first comment, that is: "triggers or procedures fired within the same session as the delete do not need a commit".  Not only do they not need a commit, they should not contain a commit.  In fact, if you call a procedure from a trigger and that procedure includes a commit, Oracle will return an error, unless that procedure is an "autonomous transaction" but those have limitations, and should only be used when necessary.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36987906
I should amend my sloppy wording above.


>>> Actually,  it's still true,  as the autonomous transaction will create a sub-session,

The idea is still true,  the transactions of the update/delete are distinct from those of an autonomous transaction.
However that autonomous transaction is still a child of the session as the original transaction.

It merely "behaves" as if it were a different session for the purposes of isolating the transaction.
That's why the autonomous can't see the update/delete without a commit.
0
 
LVL 3

Expert Comment

by:gajmp
ID: 36990175
if not, then trigger "trg_delete" will find field "x_user_del" empty and will give problems.

This is not true. Expert already comment about that.

one case we need commit after update, that is if any problem in DELETE then also update should happen and should not rollback. In this case we have to use that COMMIT.

So cross check with programmer and business requirement and then decide.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36990215
gajmp,

The perception is the commit is necessary for the columns to be properly processed by the delete.

This lends itself to an autonomous trigger not on a necessary update that should not roll back.
0
 
LVL 3

Expert Comment

by:gajmp
ID: 36990822
slightwv:
I have already said "This is not true. Expert already comment about that. " 

I have mentioned "So cross check with programmer and business requirement and then decide."

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

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…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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 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

839 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