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

710634

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.

Hoek

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.

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

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 .

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

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