Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2003
    Posts
    2,296

    Unanswered: Trigger to Refresh MV after insert/update

    Oracle 9.2.0.4 on Solaris

    I have an MV that counts a bunch of items in the db.
    I need the MV refreshed every time a column is inserted or updated.
    I cannot use REFRESH ON COMMIT since it is a complicated query in the
    MV. I have to use REFRESH ON DEMAND.

    FOR THE LIFE OF ME THIS CRAP IS NOT WORKING!!

    I thought I could use a trigger like this:
    PHP Code:
    create or replace trigger DEVICE_COUNT_MV_TRG
        after insert 
    or update of inst_status_cd on CUSTOMER

    DECLARE
    /*-----------------------------------------------------*/
    /* This trigger refreshes device_count_mv whenever     */
    /* customer.inst_status_cd is inserted or updated      */
    /*-----------------------------------------------------*/

        
    pragma AUTONOMOUS_TRANSACTION;

    BEGIN

        dbms_mview
    .refresh('DEVICE_COUNT_MV''C');
        
    END;

    after running many tests, it is not refreshing the frickin MV!!

    Please help a frustrated Duck.

    Do I HAVE to use FOR EACH ROW clause? Is there a better way to do this? My only REAL requirement is that if there is a COMMIT on the customer table I want to REFRESH this MV. There is no trigger for AFTER COMMIT however
    Last edited by The_Duck; 04-29-04 at 11:44.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Do you know if it is erroring out in the trigger ???
    Round about way but, could you make this a proc instead of a trigger, then
    in the trigger submit a job to run in the job queue 1 second later ???

    ie,
    create or replace trigger DEVICE_COUNT_MV_TRG
    after insert or update of inst_status_cd on CUSTOMER

    DECLARE
    /*-----------------------------------------------------*/
    /* This trigger refreshes device_count_mv whenever */
    /* customer.inst_status_cd is inserted or updated */
    /*-----------------------------------------------------*/

    pragma AUTONOMOUS_TRANSACTION;

    BEGIN

    dbms_job.submit(JOBx,'begin procedurename( '||mview_or_whatever_parameter_you_need_to_pass||' ); end;',sysdate + 1/(60*60*24));

    END;
    /



    HTH
    Gregg

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    interesting ... I'll try that.

    The trigger doesn't error out, it completes.
    The problem is that the MV is not refreshing properly since the data remains the same in the MV when I attempt to insert/update.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Let me know how that works ... I had to get around something similar a while back and did not have time to research it or try and find and good solution ...

    Gregg

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    The funny thing is that the trigger doesn't refresh the MV I think.

    If I update the table, then issue:
    exec dbms_mview.refresh('DEVICE_COUNT_MV', 'C');

    It works great.
    SO, how the hell can I always issue that line after any commit on the CUSTOMER table?

    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I am asking this a rhetorical question, because I'm not sure of the answer myself.

    Has the change really been commited while the trigger is executing?

    What happens if you add the following line at the start of your autonomous procedure?
    DBMS_LOCK.SLEEP(2);
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296

    Angry

    The funny thing is that the trigger doesn't refresh the MV I think.

    If I update the table, then issue:
    exec dbms_mview.refresh('DEVICE_COUNT_MV', 'C');

    It works great.
    SO, how the hell can I always issue that line after any commit on the CUSTOMER table?

    - The_Duck
    you can lead someone to something but they will never learn anything ...

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    hrm .... didn't work:
    PHP Code:
    platform@kod1update customer set inst_status_cd 'Not Installed' where org_id IN ('SOS','4');
    update customer set inst_status_cd 'Not Installed' where org_id IN ('SOS','4')
           *
    ERROR at line 1:
    ORA-06519active autonomous transaction detected and rolled back
    ORA
    -06512at "PLATFORM.SP_DEVICE_COUNT_REF"line 9
    ORA
    -06512at "PLATFORM.DEVICE_COUNT_MV_TRG"line 7
    ORA
    -04088error during execution of trigger 'PLATFORM.DEVICE_COUNT_MV_TRG' 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  9. #9
    Join Date
    Jul 2003
    Posts
    2,296
    I got it.
    I needed to add a COMMIT after creating the job.
    Now it works fairly well. I ran the job 10 seconds into the future and that seems to work pretty well.

    Thanks for the help.

    code:
    PHP Code:
    create or replace trigger DEVICE_COUNT_MV_TRG
        after insert 
    or update of inst_status_cd on CUSTOMER

    declare

        
    X number;
        
    pragma AUTONOMOUS_TRANSACTION;

    begin
    /*-----------------------------------------------------*/
    /* This trigger refreshes device_count_mv whenever     */
    /* customer.inst_status_cd is inserted or updated      */
    /*-----------------------------------------------------*/

      
    SYS.DBMS_JOB.SUBMIT
        
    job       => 
         
    ,what      => 'dbms_mview.refresh(''DEVICE_COUNT_MV'', ''C'');'
         
    ,next_date => sysdate + (5/(60*60*24))
         ,
    no_parse  => TRUE
        
    );
        
      
    commit;
      
    end;

    - The_Duck
    you can lead someone to something but they will never learn anything ...

  10. #10
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Sorry, I've been away for a bit ... Glad to know it worked ... One of these days, I'm gonna get a little time and look at that ...

    Gregg

  11. #11
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by gbrabham
    Sorry, I've been away for a bit ... Glad to know it worked ... One of these days, I'm gonna get a little time and look at that ...

    Gregg
    The main problem I was having is that I was not realizing what AUTONOMOUS_TRANSACTION actually does. Since it creates a whole new session, it was refreshing the MV before the commit on the table/data. From a different session it could not see the new update/insert and that is why it SEEMED as if the refresh was not working.

    Setting up a job as you suggested allowed enough time for the transaction to commit, and the lag/job on the refresh then could "see" the updated data on the table.

    Tricky stuff, but that was a good work-around.

    Oracle should create functionality for triggers based on an AFTER COMMIT clause. It would be helpful since complicated MVs cannot refresh on commit.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  12. #12
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Man ... I HIGHLY agree with that last statement !!!!

    Gregg

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •