Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Getting around Oracle's mutating table problem (Read 6122 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Getting around Oracle's mutating table problem
15.08.06 at 07:55:15
 
Getting around Oracle's mutating table problem
 
The mutating table problem is something that has plagued Oracle developers for a long time. In short the problem restricts a trigger from looking at the table that is changing. For example in a after update row trigger you cannot query the same table.
 
Using the Oracle Designer TAPI (Table API) you can get around this problem. The TAPI will populate a PL/SQL table with the changed data. You can use this data in a statement trigger and thus bypassing the mutating table problem.  
 
In statement triggers the mutating table problem does not occur. You may query the table that is changing. However you do not know which data changed. In the row triggers you can reference the :new and :old values and these are not available in statement triggers. The good thing about the TAPI is that the changed data is put into a PL/SQL table that you can reference.  
 
When you put a TAPI trigger on a table not only a trigger is produced by designer but also a TAPI package. In the specification of the table package - typically called cg$table_name - the PL/SQL table is declared. It is best referenced from a PRE after statement trigger. In the POST after statement triggers the PL/SQL table is cleared in the case of inserts - not for updates curiously. If you add a PRE after statement trigger, the PL/SQL table is filled with the data. Note that in case of an update trigger the table will contain 2 rows per change. One row with the old data and one row with the new data.
 
In Oracle designer you can add the TAPI trigger in the design editor. When you open a table node you find an item called: Table API Trigger Logic. You can add your TAPI triggers here. To generate the PL/SQL code, go to menu Generate | Generate Table API...
Back to top
 
« Last Edit: 15.08.06 at 07:55:28 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Getting around Oracle's mutating table problem
Reply #1 - 29.11.06 at 13:39:51
 
The first row in the PL/SQL table contains the old values, the second row in the table contains the new values. This is in case of an update.
For insertes there is only 1 row with the new values per new row.
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1