Oracle Trigger executing stored procedure after insert

make  sure you are not creating an infinite loop

if SP_INSERT_FACT_HFM_ACT_PER_FIN modifies MCCCH01.PER_FACT it will trip the trigger recursively (and loop infinitely)

The INSERTlooks up the keys in the PER_FACT in the associated dimension table and does one CASE statement during the INSERT.  The final FACT table is loaded with natural dimension names instead of keys and a three char month is changed to the numeric equivalent.  

Yes, I have apparently created an infinite loop.  So...is it possible to accomplish what I am trying to do.  Basically, execute the SP after the INSERT has completed?

It would be great if someone could explain the infinite loop?  I've got to learn somewhere and don't hesitate to point me to a good ref book.

Thanks

Can you post your Stored procedures logic here?

Also refer to http://asktom.oracle.com/~tkyte/Mutate/index.html

I'm right in the middle of some changes but here it is....

CREATE OR REPLACE PROCEDURE SP_INSERT_FACT_HFM_ACT_PER_FIN IS
tmpVar NUMBER;
/******************************************************************************
   NAME:       SP_INSERT_FACT_HFM_ACTUALS_PER_FIN
   PURPOSE:    

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        5/16/2006          1. Created this procedure.

   NOTES:

   Automatically available Auto Replace Keywords:
      Object Name:     FDM_INSERT_FACT_ACTUALS_PER
      Sysdate:         5/16/2006
      Date and Time:   5/16/2006, 2:20:37 PM, and 5/16/2006 2:20:37 PM
      Username:         (set in TOAD Options, Procedure Editor)
      Table Name:       (set in the "New PL/SQL Object" dialog)

******************************************************************************/
PROGRAM_ID  VARCHAR2(30) := 'SP_INSERT_FACT_HFM_ACT_PER_FIN';

BEGIN
   

       CREATE_LOG(PROGRAM_ID,0,NULL,'Load SP_INSERT_FACT_HFM_ACT_PER_FIN - Program Started', SYSDATE, NULL);
             

             
        UPDATE FACT_HFM_ACTUALS_PER_FIN
        SET ACTIVE_FLAG = 'N',
        CURRENT_PERIOD = 'N'
        WHERE FACT_HFM_ACTUALS_PER_FIN.TIME_PERIOD = (SELECT DISTINCT LABEL FROM PER_PERIOD aa, PER_FACT bb
                                                                           WHERE bb.PERIODID = aa.ID)
        AND FACT_HFM_ACTUALS_PER_FIN.YEARS =  (SELECT DISTINCT LABEL FROM PER_YEAR aa, PER_FACT bb
                                                               WHERE bb.YEARID = aa.ID);
                                                             
        COMMIT;
        
        UPDATE FACT_HFM_ACTUALS_PER_FIN
        SET CURRENT_PERIOD = 'N'
        WHERE CURRENT_PERIOD = 'Y';
        
        COMMIT;
        
        
        INSERT INTO FACT_HFM_ACTUALS_PER_FIN
        (current_period,
        entity,
        account,
        cost_center,
        product_line,
        data_type,
        scenario,
        hfm_value,
        hfm_view,
        ICP,
        time_period,
        years,
        project,
        fy_period,
        account_type,
        default_currency,
        active_flag,
        amount)
        (select 'Y' AS CURRENT_PERIOD,
        af.label AS Entity,
        ah.label AS Account,
        al.label AS COST_CENTER,
        aj.label AS PRODUCT_LINE,
        am.label AS DATA_TYPE,
        ab.label AS Scenario,  
        ag.label AS Value,
        ae.label AS HFM_View,
        ai.label AS ICP,
        ad.label AS Period,  
        ac.label AS Year,
        ak.label AS PROJECT,
        CAST('20' || SUBSTR(ac.label,3,2) || CASE WHEN ad.label = 'Jan' THEN '01'
                                                              WHEN ad.label = 'Feb' THEN '02'
                                                              WHEN ad.label = 'Mar' THEN '03'
                                                        WHEN ad.label = 'Apr' THEN '04'
                                                        WHEN ad.label = 'May' THEN '05'
                                                        WHEN ad.label = 'Jun' THEN '06'
                                                        WHEN ad.label = 'Jul' THEN '07'
                                                        WHEN ad.label = 'Aug' THEN '08'
                                                        WHEN ad.label = 'Sep' THEN '09'
                                                        WHEN ad.label = 'Oct' THEN '10'
                                                        WHEN ad.label = 'Nov' THEN '11'
                                                        WHEN ad.label = 'Dec' THEN '12'
                                                               ELSE NULL
                                                                  END AS NUMBER) as FY_YEAR,
        ao.accounttype AS Account_Type,
        an.DEFAULT_CURR__NAME,
        'Y',
        aa.ddata AS AMOUNT
        FROM PER_FACT aa,
        PER_scenario ab,
        PER_year ac,
        PER_period ad,
        PER_view ae,
        PER_entity af,
        PER_value ag,
        PER_account ah,
        PER_icp ai,
        PER_custom1 aj,
        PER_custom2 ak,
        PER_custom3 al,
        PER_custom4 am,
        MD_ENTITY an,
        PER_ACCOUNT ao
        WHERE aa.scenarioid = ab.id
        AND aa.yearid = ac.id
        AND aa.periodid = ad.id
        AND aa.viewid = ae.id
        AND aa.entityid = af.id
        AND aa.valueid = ag.id
        AND aa.ACCOUNTID = ah.id
        AND aa.ICPID = ai.id
        AND aa.CUSTOM1ID = aj.id
        AND aa.CUSTOM2ID = ak.id
        AND aa.CUSTOM3ID = al.id
        AND aa.CUSTOM4ID = am.id
        AND aa.ENTITYID = an.id
        AND aa.accountid = ao.id
        );

        COMMIT;
        

CREATE_LOG(PROGRAM_ID,0,NULL,'SP_INSERT_FACT_HFM_ACT_PER_FIN - Completed Successfully', NULL, SYSDATE);      
      
      EXCEPTION
         WHEN OTHERS THEN
            CREATE_LOG('SP_INSERT_FACT_HFM_ACT_PER_FIN',SQLCODE,SQLERRM,'Raise Application Error - 20013');
       
            RAISE_APPLICATION_ERROR(-20013, SQLERRM);
      
END;
/

Can you change your trigger definition to this and try

CREATE OR REPLACE TRIGGER TR_TEST
AFTER INSERT OR UPDATE
ON MCCCH01.PER_FACT
FOR EACH ROW
REFERENCING NEW AS NEW OLD AS OLD
declare
 pragma autonomous_transaction;
 
 
BEGIN
  SP_INSERT_FACT_HFM_ACT_PER_FIN;
END;
/

what is your insert into or update of MCCCH01.PER_FACT?

i mean please post the sql that does the insert or update

you may not need a trigger if you just want to always run this stored procedure after a specific insert or update statement you run.

Just make a script or stored procedure that executes the insert or update and then executes SP_INSERT_FACT_HFM_ACT_PER_FIN

Thanks for the input.  Unfortunately a canned application generates and executes the INSERT script and I can not edit the SQL.  I need to ececute the SP after the INSERT is complete that's why I was hoping to make the trigger work!  Thoughts?

is FACT_HFM_ACTUALS_PER_FIN linked in any way to MCCCH01.PER_FACT?

from the code you posted for your stored proc i dont see how it could be creating an infinite loop (unless those tables were tied in some way, triggers,  etc).

I would take a step back and just try to make a very simple stored proc that inserts into some other temp table (created just for debugging purposes) with an insert statement that is as simple as possible.  If this works, slowly start changing this stored procedure (testing frequently) until it breaks.  This way you can tell which part of the procedure is causing the problems with the trigger.  If the trigger doesnt work right off with the simple stored proc then you can focus on modifying the trigger so it works (and let us know this so we might be able to zero in on the problem more quickly).

PER_FACT and FACT_HFM_ACTUALS_PER_FIN are not linked in any way.  This is how I tested....

1.  Execute application that inserts into PER_FACT for small entity.  This creates 3637 records.
2.  Execute stored procedure and it inserts 3637 records into the FACT_HFM_ACTUALS_PER_FIN table.

Based on this I think the sp is fine.  But the trigger does appear to be looping!  Thoughts?

Did you try changing your trigger definition to include FOR EACH ROW?

No...I was afraid it would execute the sp after each row insert.  Wouldn't that be a major performance drain?  I'll give it a try using the recommended SQL above!  

OK.... tried that and it continually loops through the data.  For exery record that is loaded into per_fact, that record and all previous records are loaded into FACT_HFM_ACTUALS_PER_FIN.  I would expect it needs to be a AFTER Statement trigger and now a row trigger.  Is there a way to determine when the insert is complete?  so I could use that as my event?  Thanks!

I think your problem is with the SELECT statement from the PER_FACT table. This is the table on which you trigger is.

Try moving that update out of the SP. Should hopefully work.

I'm lost...The update sets the current flag / active flag to 'N' which is needed.  The update has no impact on PER_FACT.  The insert happens after the update in the SP... there is no loop in the sp.  The INSERT scripts pulls the exact number of records that I expect so I don't see how it could be the Select or the Insert.  Since the SP executes successfully, I would think it's in how / when the trigger is executing the SP.

Hyper, have you followed the methodology I outlined above when i said:

""

I would take a step back and just try to make a very simple stored proc that inserts into some other temp table (created just for debugging purposes) with an insert statement that is as simple as possible.  If this works, slowly start changing this stored procedure (testing frequently) until it breaks.  This way you can tell which part of the procedure is causing the problems with the trigger.  If the trigger doesnt work right off with the simple stored proc then you can focus on modifying the trigger so it works (and let us know this so we might be able to zero in on the problem more quickly).

If so, let us know the results

The stored procedure executes correctly without the trigger.  

yeah but it doesnt with the trigger

so i am suggesting that you start with a very simple stored proc.

Piece by piece build it into the procedure you want (testing after each piece) so you can tell exactly which piece of the stored proc is hanging

OK... it doesn't seem like this will be solved quickly or easily today.  Can sometime at least tell me if this should be do-able?  I don't have a lot of time to waste on something that may never work.

Thanks

also put a period in before your slash

so the last three lines of your stored proc would become

  SP_INSERT_FACT_HFM_ACT_PER_FIN;
END;
.
/

it is doable so you are not wasting your time

First... thanks... I don't mind doing the grunt work if people tell me I 'should' be able to do it.

Second...I added the period as recommended above and the SP doesn't compile...

Error =

PLS-00103: Encountered the symbol "." The symbol "." was ignored.

I'll be leaving this client until the 10th so I won't be able to test again until then!  Thanks for the assistance!

Ok one last thing for today, after your insert into PER_FACT table, do you see any records at all being created in the
 FACT_HFM_ACTUALS_PER_FIN  table?

Which version of Oracle are you using?

Oracle 10G

With the trigger above, using the 'Per Each Row', an insert was happening...PER_FACT had only 3700 records or so... when I killed it, FACT_HFM had over 78K records!  The loop!

check the content of those records, do all the records have the same values?

Ok if you are using the for each row, modify your insert so  that it inserts only the modified row in to the FACT_HFM table (i.e) your select statement in the insert should select only one record from the PER_FACT table. You could use the :old or :new on  your where clause

Remember a signle SQL statement that works on N records is much faster than N statements.

ROW LEVEL trigger is fired each time a row is inserted into table. The solution is to create a log table. This is a flat table, same structure as the original fact table, with no triggers or constraints or indexes. Periodically run the SP to move records from log table to fact table.

I don't want to insert row by row.  I want to insert the entire table AFTER it has been commited to the PER_FACT table.   The SP needs to be executed immediately after the PER_FACT table has been loaded.  So... I need a trigger that exec the SP after the PER_FACT table has been successfully loaded.  So the question is how do I create a trigger that 1) 'knows' when the load to the PER_FACT is complete and 2) can use that event to trigger the execution of the SP.

Ok then let's try debugging the SP.

Comment out the insert statement and just do a select count(1) into cnt from the per_fact with whatever where clause you are using for inserting.

Then do a DBMS_OUTPUT.PUT_LINE('Count is' || cnt);

Now perform the action that fires the trigger (table level)

See if the cnt displayed is the number you are expecting.

OK.... so I don't understand why I need to debug the stored procedure when it works properly without the trigger.  The variable here is the trigger.  I have to put this on hold... as stated earlier I will not be back on-site until July 10th so I don't have the ability to do any testing until then.  Thanks!

Declare a package variable and increment the variable

SQL> create package testvar is
  2  cnt number := 0;
  3  end;
  4  /

Package created.

CREATE OR REPLACE TRIGGER TR_TEST AFTER INSERT OR UPDATE ON MCCCH01.PER_FACT FOR EACH ROW
declare
pragma autonomous_transaction;
BEGIN
testvar.cnt := testvar.cnt+1;
if testvar.cnt=1000
then
testvar.cnt := 0;
--  Process data
end if;
END;
/

The package variable will be reset to 0 when a new Oracle session begins.

Thanks... this looks promising.  Forgive my ignorance, but could you please explain setting the testvar.cnt = 1000 in the trigger?  I'll test this out as soon as I can get back into Oracle [client doing maintenance in DEV environment.....]

Thanks