Skip to Main Content

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

Processing
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Nov 27 2009
Added on Oct 30 2009
20 comments
995 views