Solved

Oracle Trigger executing stored procedure after insert

Posted on 2006-06-29
40
12,456 Views
Last Modified: 2007-12-19
Hello Gurus!
I am working with an application that updates a FACT table in an oracle schema.  I want to add a trigger to that FACT table that executes a stored procedure that moves the data from the preliminary FACT table to the final table in my star schema after the INSERT is complete.  I am expecting millions of records.  My stored procedure works correctly when executed by itself.  The trigger appears to be causing the application to 'hang' when inserted records into the preliminary FACT table.  I have tested the process without the trigger and it is successful and fast.  Since I am only an Oracle coder 'wannabe', I'm sure that I am making a rookie mistake.  Your assistance is greatly appreciated!

Trigger code is as follows:

CREATE OR REPLACE TRIGGER TR_TEST
AFTER INSERT OR UPDATE
ON MCCCH01.PER_FACT
REFERENCING NEW AS NEW OLD AS OLD
declare
 pragma autonomous_transaction;
 
 
BEGIN
  SP_INSERT_FACT_HFM_ACT_PER_FIN;
END;
/
0
Comment
Question by:hypermac
  • 14
  • 13
  • 8
  • +1
40 Comments
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17011371
Does your stored procedure modify any data in PER_FACT table?
0
 
LVL 15

Expert Comment

by:dave4dl
ID: 17011377
make  sure you are not creating an infinite loop
0
 
LVL 15

Expert Comment

by:dave4dl
ID: 17011388
if SP_INSERT_FACT_HFM_ACT_PER_FIN modifies MCCCH01.PER_FACT it will trip the trigger recursively (and loop infinitely)
0
 

Author Comment

by:hypermac
ID: 17011869
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
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17011913
Can you post your Stored procedures logic here?

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

Author Comment

by:hypermac
ID: 17011959
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;
/
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17012058
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;
/
0
 
LVL 15

Expert Comment

by:dave4dl
ID: 17012065
what is your insert into or update of MCCCH01.PER_FACT?
0
 
LVL 15

Expert Comment

by:dave4dl
ID: 17012083
i mean please post the sql that does the insert or update
0
 
LVL 15

Expert Comment

by:dave4dl
ID: 17012179
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
0
 

Author Comment

by:hypermac
ID: 17012344
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?
0
 
LVL 15

Expert Comment

by:dave4dl
ID: 17012482
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).
0
 

Author Comment

by:hypermac
ID: 17012860
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?
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17012892
Did you try changing your trigger definition to include FOR EACH ROW?
0
 

Author Comment

by:hypermac
ID: 17012930
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!  
0
 

Author Comment

by:hypermac
ID: 17013013
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!
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17013320
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.
0
 

Author Comment

by:hypermac
ID: 17013375
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.
0
 
LVL 15

Expert Comment

by:dave4dl
ID: 17013407
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
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:hypermac
ID: 17013481
The stored procedure executes correctly without the trigger.  
0
 
LVL 15

Expert Comment

by:dave4dl
ID: 17013494
yeah but it doesnt with the trigger
0
 
LVL 15

Expert Comment

by:dave4dl
ID: 17013512
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
0
 

Author Comment

by:hypermac
ID: 17013528
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
0
 
LVL 15

Expert Comment

by:dave4dl
ID: 17013530
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;
.
/
0
 
LVL 15

Expert Comment

by:dave4dl
ID: 17013544
it is doable so you are not wasting your time
0
 

Author Comment

by:hypermac
ID: 17013583
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!
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17013605
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?



0
 
LVL 15

Expert Comment

by:dave4dl
ID: 17013611
Which version of Oracle are you using?
0
 

Author Comment

by:hypermac
ID: 17013674
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!
0
 
LVL 15

Expert Comment

by:dave4dl
ID: 17013744
check the content of those records, do all the records have the same values?
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17013777
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
0
 
LVL 16

Expert Comment

by:MohanKNair
ID: 17015800
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.
0
 

Author Comment

by:hypermac
ID: 17017380
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.
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17017485
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.

0
 

Author Comment

by:hypermac
ID: 17018066
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!
0
 
LVL 16

Expert Comment

by:MohanKNair
ID: 17034770
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.
0
 

Author Comment

by:hypermac
ID: 17149114
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
0
 
LVL 16

Accepted Solution

by:
MohanKNair earned 500 total points
ID: 17151460
>> testvar.cnt := testvar.cnt+1;
>> if testvar.cnt=1000
>> then

The package variable testvar.cnt is initialized to 0 in package specification. This means when the package variable is used for the first time in a session its value will be 0.
Inside the trigger the package variable is incremented by 1
If the value of the package variable becomes 1000 THEN
                                      Re-initialize the value to 0
                                      Process that data for 1000 records in MCCCH01.PER_FACT table
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In today’s complex data management environments, it is not unusual for UNIX servers to be dedicated to a particular department, purpose, or database.  As a result, a SAS® data analyst often works with multiple servers, each with its own data storage…
APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now