Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.4K Development
- 17 Developer Projects
- 139 Programming Languages
- 293.1K Development Tools
- 111 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 161 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 475 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
help discovering what's wrong w/ my before update trigger

710634
Member Posts: 12
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.
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.
Best 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.
Answers
-
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 -
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.
-
@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); -
MadHatter wrote: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.
@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);
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 -
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 -
@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 herebut 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.
-
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 -
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. -
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 -
no, it throws some errorRemember to always post the full error message, captain Madhatter.
you must've been distracted by a stewardess
This discussion has been closed.