Using Database Change Notification instead of After Insert Trigger

Hello guys! I have an after insert trigger that calls a procedure, which in turn is doing an update or insert on another table. Due to mutating table errors I declared the trigger and procedure as autonomously transactional. The problem is, that old values of my main tables are inserted into the subtable since the after insert/update trigger is fired before the commit.

My question is how can I solve that and how could I use the change notification package to call my procedure? I now that this notification is only started after a DML/DDL action has been commited on a table.

If you could show me how to carry out the following code with a Database Change Notification I'd be delighted. Furthermore I need to know if it suffices to set up this notification only once or for each client seperately?

Many thanks for your help and expertise!

Regards,

Seb
declare 
cnumber number (6);
        
begin
 
select count(*) into cnumber from (
select case when (select date_datum 
    from
      (select f.date_datum,
        row_number() over (order by f.objectid desc) rn
      from borki.fangzahlen f
      where lng_falle      = :new.lng_falle
      and int_fallennummer = :new.int_fallennummer
      and lng_schaedling   = :new.lng_schaedling
      and date_datum       > '31.03.2010'
      )
    where rn=1) < (select date_datum 
    from
      (select f.date_datum,
        row_number() over (order by f.objectid desc) rn
      from borki.fangzahlen f
      where lng_falle      = :new.lng_falle
      and int_fallennummer = :new.int_fallennummer
      and lng_schaedling   = :new.lng_schaedling
      and date_datum       > '31.03.2010'
      )
    where rn=2) then 1 end as action from borki.fangzahlen
        where lng_falle      = :new.lng_falle
        and int_fallennummer = :new.int_fallennummer
        and lng_schaedling   = :new.lng_schaedling
        and date_datum       > '31.03.2010') where action = 1; 
 
if cnumber != 0 then 
 
delete from borki.tbl_test where lng_falle = :new.lng_falle
and int_fallennummer = :new.int_fallennummer
and lng_schaedling   = :new.lng_schaedling
and date_datum       > '31.03.2010';
commit;      
 
 pr_fangzahlen_tw_sync_sk(:new.lng_falle, :new.int_fallennummer, :new.lng_schaedling);

Open in new window

skahlert2010Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

schwertnerCommented:
Sofar i know 'mutating table' error arises if you try to select or insert into the table the trigger belongs.
but you say 'is doing an update or insert on another table'
Why have you to use complex mechanism if you can do it simple.

Meine Gruesse, aber mache Sie es ein bischen einfacher!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
skahlert2010Author Commented:
Regarding your statement about the mutating table error:
Yep that's correct! I need to select from the table that owns the trigger in order to run the procedure conditionally!

I have a MRU (Apex) process that inserts or updates the main table, which has the trigger I mentioned above. Depending on the :new values inserted into the main table the procedure is launched. One would think that an after insert/update trigger would fire only after the update on that very same table has been committed. But unfortunately that's not the case!

The only workaround is the change notification I guess, since it detects changes only after commit.
Maybe you have another idea or a hint how to realize that? I have an alternative threat were I focus on a more sophisticated query that would make this concept right here irrelevant! But again, this one is wicked too.

Meine besten Grüsse zurück! How did you know I was German? ;-)
0
skahlert2010Author Commented:
Well forget about my last sentence! The query tells it all! It's too late today! :-)
0
skahlert2010Author Commented:
Unfortunately not the correct solution but pointing at the right direction! Make it a little easier! Thanks a lot for your effort!

Brgds,

skahlert2010
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.