Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

help discovering what's wrong w/ my before update trigger

710634Oct 30 2009 — edited Oct 30 2009
Let me prefix this w/ the fact that I'm an idiot, so be kind.

I have one trigger that is not working (seemingly), and I am about googled out so I thought I'd ask and see if anyone couldn't provide thoughts, insults, suggestions or what have you.

Database being used is 10g XE and updates are being done transactionally.

I have a table that stores (among other things) the status of a particular type of (app specific) transaction. When that status changes to a particular value, I have a trigger that updates another table with the date that this status change took place.

<pre>Table: XTransaction
id (pk)
statusid (int)
...</pre>

and here is what my trigger looks like:

<pre>create or replace trigger x_transaction_update before update on xtransaction
for each row
declare
begin
if( :old.statusid = 1) then -- this is here because I got an error when I tried to use a when clause above
begin
-- I log before the update w/ some other info to tell whether I've been to this spot or not
update sometable set when_it_happened = sysdate where xtransid = :old.id;
-- I log after the update too
exception
when others then
-- I log sqlerrm
end;
end;
end if;
end;</pre>


here's what I've been able to gather through testing & viewing logs from the trigger:

1. there's no exception being logged,
2. the pre/post update logs with all the correct data (proving that the trigger is fired),
3. when I update xtransaction from visual studio (through the oracle addin which lets you run queries against the database), the status is changed, the trigger is fired, and the other table is updated.
4. when the application that normally updates the xtransaction table runs, xtransaction is updated, the trigger is fired, and sometable is not updated.



so I have absolutely no clue where to go with this one. Usually I could fire up sql server's query analyser and watch what's coming through, and I've tried to use toad's commercial tool to do this, but to no avail. I've tried changing it to an after update, but had the same results. I wrote this some months ago, and it was working then, but not now.
This post has been answered by Hoek on Oct 30 2009
Jump to Answer

Comments

728534
do one thing as trouble shooting create a audit table and insert the values in this table for testing.
Check the ID values that you are comparing.
Something is breaking some where.

Cheers!!!
Bhushan
Centinul
I would try and remove the EXCEPTION WHEN OTHERS THEN block from the code. It seems you may have left some of the actual code out so we don't know if there is actually a RAISE or RAISE_APPLICATION_ERROR there. If not, you may be getting an exception that is being silently ignored.
710634
@Buga
well that's what I am doing just before and just after the update (implemented in the actual trigger where the -- comments are).

I log a string that is the exact same as the line below it so I can paste it into my addin in visual studio and run it (to determine whether the statement itself has some error). and when I do that, it works just fine.

as I said before, I can update the xtransaction table (which has the before update trigger) and the trigger works fine (which is what puzzles me).

@Centinul
the only difference between what I posted and the actual trigger are table / column names, and i'm calling a sproc I wrote for the logging in place of the comments in the one I posted:

log_msg('update sometable set arrival = sysdate where transid = ' || :old.id);
Centinul
MadHatter wrote:
@Centinul
the only difference between what I posted and the actual trigger are table / column names, and a sproc I wrote for the logging in place of the comments I have:

log_msg('update sometable set arrival = sysdate where transid = ' || :old.id);
I know this doesn't answer the question you have now, you should seriously consider removing the WHEN OTHERS clause altogether OR ensure that there is a RAISE or RAISE_APPLICATION_ERROR there.

This trigger isn't syntactically correct either:
create or replace trigger x_transaction_update before update on xtransaction
for each row
declare
begin
    if( :old.statusid = 1) then -- this is here because I got an error when I tried to use a when clause above
        begin
            -- I log before the update w/ some other info to tell whether I've been to this spot or not
            update sometable set when_it_happened = sysdate where xtransid = :old.id;
            -- I log after the update too
        exception
            when others then
                -- I log sqlerrm
            end;
        end; /* Extra END here.... */
    end if;
end;
Edited by: Centinul on Oct 30, 2009 1:23 PM
1 person found this helpful
728534
you mean
when the application that normally updates the xtransaction table runs, xtransaction is updated, the trigger is fired, and sometable is not updated
your log tables are inserted with new and old values but your sometable is not updated.
Then the only condition where it cna fail is your id comaprison.
Make sure your application is nto changing the ID that is being passed/processed than the INPUT given.

Cheers!!!
Bhushan
710634
@Buga
yea... that's exactly what's happening. my log statements log a sql statement which I can execute independently. IT also correctly runs when I update the xtransaction table outside of the application (in both cases the same row is updated which fires the trigger and should update the secondary table).

@Centinul
again, this is the idiot factor here ;) but would you mind explaining why that would be important. I have about as much experience doing database work as a toenail clipping so be gentle. my intention was to swallow any exceptions in the trigger and hope that's what the exception handling does.

I think the reason I put that there was because the transaction (db transaction) would fail on update if there was something wrong w/ the trigger, and it was more important that the status be updated (as it would never be updated again if the first attempt failed) than that the secondary table be updated.

the transaction being updated is about as critical as an airplane being given clearance to land. Having that "you only get one chance to land" permission revoked because the guy who's job it was to record the exact time that clearance was given had his pencil break, doesn't bode to well for the 300 passengers of the airplane who will eventually fall out of the sky & die :D.
Hoek
Are you using autonomous transactions for any of your logging procedures?
It helps a lot to just paste the complete code (except posting 2000 lines of code, try to extract a smaller example from that then) instead of leaving parts out.

And, (elaborating on Centinul's remarks) I'd like to take it a step further while you're debugging this:
turn off (comment out) or remove your WHEN OTHERS completely.
Get rid of it until you know what's going on edit ->, captain Madhatter.
Until then I urge you to keep flying in circles, you're not cleared to land ;)

Edited by: hoek on Oct 30, 2009 6:38 PM
1 person found this helpful
710634
yea there is an extra end in the one I typed. here's a copy / paste / edit of the one in the db:


<pre>TRIGGER "XTRANSACTION_UPDATE_TRANSIT" BEFORE UPDATE ON "XTRANSACTION"
FOR EACH ROW
DECLARE
BEGIN
IF (:old.statusid = 1) THEN
for tag in (select * from r where r.docid = :old.docid) loop
begin
log_msg(tag.id || ' before update');
log_msg('update sometable set arrival = SYSDATE where tagid = ' || :old.id);
update transit set arrival = SYSDATE where tagid = tag.id;
log_msg(tag.id || ' after update');
exception
when others then
log_msg(sqlerrm); -- die silently?
end;
end loop;
END if;
END;</pre>




again, the thing I can't figure out (maybe it has to do with transactions?) is why the update does not happen when updated from the application, and why it is when I update it by hand using the same database / records / data.
710634
no, it throws some error if I attempt to use autonomous transaction.

here's the logger sproc:

<pre>procedure log_msg( message varchar2 )
is
begin
insert into log values( systimestamp, message );
end;</pre>


and when I remove the exception handling, nothing changes. it has the same behavior with and without.

Edited by: MadHatter on Oct 30, 2009 10:40 AM
Hoek
no, it throws some error
Remember to always post the full error message, captain Madhatter.
you must've been distracted by a stewardess
710634
well I tried setting autonomous transaction yesterday, and I've slept since then. I'll try it again and post it, but I think it was something about how having it set would not work if something something something ;) anyway, I'll try to post that later if it's pertinent.

Edited by: MadHatter on Oct 30, 2009 10:50 AM

here's what it said:

ERROR
ORA-06519: active autonomous transaction detected and rolled back
ORA-06512: at "U3.XTRANSACTION_UPDATE_SOMETABLE", line 13
ORA-04088: error during execution of trigger 'U3.XTRANSACTION_UPDATE_SOMETABLE'

line 13 is:

end loop;
Centinul
Check out these Google results: http://www.google.com/search?hl=en&client=firefox-a&rls=org.mozilla%3Aen-US%3Aofficial&hs=keG&q=site%3Atkyte.blogspot.com+WHEN+OTHERS&aq=f&oq=&aqi=

It's a number of blog entries by Tom Kyte about WHEN OTHERS. I think that will give you a good idea of how it can be dangerous.

Do you have the possibility of changing the code that updates the transaction table to also insert into this other table? That's what I would recommend.
Hoek
Yes, Centinul's got a solid point.

Rethink your logic and see if you can lose the trigger and replace it by a transactional API.
(a procedure or function that takes care of all needed DML)

While you're flying around, take the time to read a bit on it:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2212445691154

And since we're at it, one more regarding the WHEN OTHERS (not followed by a raise), here's how an expert thinks of it:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1509245700346768268#tom1535781400346575552
710634
well, I'm not trying to do if others then null, my intention was to log the error message for the update, and move on.

unfortunately, the application is a composite of about 20 different applications which all have complex dependencies (very tightly coupled puke), and going back and making that change would couple it even tighter since the table I'm trying to update in this trigger has absolutely nothing to do with the application that updates the transaction status. There is a vast DAL that would also need to change, then I'd have to rebuild, retest, and re-certifiy a lot of application (the government is fun to work with), so that's not an option.


Edited by: MadHatter on Oct 30, 2009 10:59 AM

I can understand not wanting to silently commit suicide (if others then null), at a software level (which is what I usually do, I'm no dba) this is the best practice, however, in this instance, the application that does the updates to this table was not written to handle an error here, and has been "functional" for quite some time. If the trigger causes errors (which lets be honest, shouldn't happen, so in theory it could be safely removed), it will put the app in a bad state, and as the app is an unmanaged application (it's a black box that sits in the middle of nowhere land), very very bad things will happen were that to happen.
Hoek
the government is fun to work with
Yea, I know ;)

Point is:

You need to/want to log the error in a separate transaction, and for that you'll need to commit it.
But: you cannot commit in a trigger.
Hence my autonomous transaction question regarding your logging procedure.
It would be helpful to post the error you got when trying to implement an autonomous logging procedure.

If you're after some quick fix, we'd need to debug first and see what options are available/left after that, after finding the root cause.
1 person found this helpful
710634
I edited my previous post w/ the error produced w/ the autonomous transaction.


what I'm really after is why two different applications executing the exact same update can produce two different results. one will update the table, fire the trigger, update the secondary table, and the other will update the table, fire the trigger, but not update the table. and neither one logs an error.
Centinul
Here is the description of the error:
ORA-06519: active autonomous transaction detected and rolled back
Cause: Before returning from an autonomous PL/SQL block, all autonomous transactions started within the block must be completed (either committed or rolled back). If not, the active autonomous transaction is implicitly rolled back and this error is raised.
Action: Ensure that before returning from an autonomous PL/SQL block, any active autonomous transactions are explicitly committed or rolled back. >

You could probably fix this by placing a commit in your logging procedure.
1 person found this helpful
Hoek
Answer
Thanks much for posting the actual error!

Are you trying to bypass the famous mutating table error here?
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:8323596621929

You should use the autonomous transaction only for an error logging procedure.
You use one logging procedure for different purposes.
You're being 'autonomous' on totally different levels.
Create a dedicated 'WHEN OTHERS'-logging procedure (being an autonomous transaction), or remove the WHEN OTHERS
(or: remove the other calls to your logging procedure).
This is just step one: get rid of 'distractions', and keep your code as clear and clean/crisp as possible.
Marked as Answer by 710634 · Sep 27 2020
710634
ok, if I add the auto_trans and a commit after my loop, everything works (currently removed the exception handling).

as for mutating tables and the like, I have no idea what any of that that is (but will read up on it), or why it would be problematic. My original premonition was that it had something to do with transactions. I couldn't find anything online describing how triggers played with transactions (which was why I attempted the auto_trans before, but when it errored, I had no idea how to fix it so I removed it), but knew that if something happened in the trigger it would foul up the update.


Thank you all so much for helping this work right! If you have time I'd like to try to understand the whole multiple transactions (because I'm pretty sure that's going on somewhere) and how it relates to triggers (since I'm using a few of them to integrate with this legacy system). Remember I'm an idiot may not understand the big kid words :P so any dumbing down that could happen would be appreciated.

Edited by: MadHatter on Oct 30, 2009 12:05 PM
Hoek
Captain Madhatter, I think your questions are easily explained by posting 2 links.

http://tahiti.oracle.com
http://asktom.oracle.com

The only effort you'll need to put in it is some time to read, and (most important) understand.
Both links allow you to do a search on keywords of your specific interest.
Link 1 = the books
Link 2 = how it works in real life, with lots of examples.

All I can say is:
"multiple transactions+triggers" = road to disaster.
been there, done that and burned the T-shirt
Remember I'm an idiot
Quit saying that, people who want to understand how things work, aren't idiots.
People who aren't afraid to ask, are not idiots.
All they have to do is: put time and effort into it.
There's nothing wrong with asking questions and and I think you're going to read up on some stuff ;)

Wishing you (and your passengers) a happy landing ;)
1 - 20
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Nov 27 2009
Added on Oct 30 2009
#trigger, #xe
20 comments
994 views