Førnævnte poster er nogle jeg SKAL hente informationer fra når en rowtrigger fyrer. Problemet er bare at informationerne hentes fra samme tabel som triggeren er tilknyttet og så har vi 'Mutating table'. Antallet af poster kan være fra 1 til 10.
Triggere har en tendens til at give en "Mutating tables" bedsked når der er en select involveret.
Forklaring og løsning fra Metalink:
ORACLE MUTATING TABLE PROBLEM =============================
This paper and the associated programs are offered to the public domain for enhancement and research by an Oracle customer, Arup Nanda. No warranty is either expressed or implied. The end user is solely responsible for results of the execution of the programs.
PROBLEM: -------
It is a pretty common problem in Oracle that happens and frustrates many application developers and DBAs as the application developers bug them to find a solution to it. It happens when a trigger on a table tries to insert, update, or even select the table of whose trigger is being executed. Sometimes the inability to see the table causes standstill in the development. This paper and the associated scripts attempt to present a work around for the problem. The work around is not the best one nor the author claims it to be an elegant one, but it provides a solution that will be applicable in most cases. The approach is described by a case study.
SCENARIO --------
The mutating table problem comes when the trigger tries to select or modify any row of the same table. This situation comes when a table preserves some effective date.
To describe this method, I will be using the table SALGRADE.
The table SALGRADE contains information on salary limits for each grade. The salary limits are also based on a time factor, i.e. the employees' salary is determined by checking which grade level was effective when they joined or reviewed, not necessarily the grade effective now.
So the table looks like this:
SQL> desc salgrade; Name Null? Type ------------------------------- -------- ---- GRADE NUMBER LOSAL NUMBER HISAL NUMBER START_DATE DATE END_DATE DATE
This means the effective salary range of Grade 1 now is (1200-2200) nut the employees who had review between 1-APR-94 to 3-AUG-95 will be in the range (1000-2000). This is a purely hypothetical scenario. Our objective is to devise a trigger that does the following when a new record is inserted:
(1) Integrity checking for overlapping dates, i.e. the new record can't have a start date that is already covered.
(2) Update the record for the current grade to make the end_date equal to the start date of the new record (the new record's end_date must be null as that is the current record).
In both cases the table SALGRADE has to be selected and updated on the after insert row trigger on the same table. But the table will be mutating when the trigger fires and thus a run-time error will occur.
For the first requirement, consider the following trigger:
create or replace trigger taiudr_salgrade after insert on salgrade for each row declare hold_found varchar2(1); begin select 'Y' into hold_found from salgrade where grade = :new.grade and end_date is null and start_date > :new.start_date;
exception when NO_DATA_FOUND then raise_application_error(-20000,'Overlapping Dates'); end; /
Although the trigger can be created with no errors, when a user tries to insert into the table he will receive the mutating table error:
SQL> insert into salgrade values (2, 9000, 100000, '25-dec-95', null); insert into salgrade values (2, 9000, 100000, '25-dec-93', null) * ERROR at line 1: ORA-04091: table JACK.SALGRADE is mutating, trigger/function may not see it ORA-06512: at line 4 ORA-04088: error during execution of trigger 'JACK.TAIUDR_SALGRADE'
SOLUTION --------
The following approach is another possibility for the task:
1. Create a package "SALGRADE_PKG" that contains PL/SQL tables for holding the SALGRADE data. Here we create 3 tables - one for holding start_dates, one for end_dates, and one for holding the change_flag that identifies the updated row.
2. Create a BEFORE INSERT STATEMENT trigger that populates the PL/SQL table with the start dates, end_dates and changed_grades flag ('N').
3. Create an AFTER INSERT ROW trigger that compares the newly inserted row against this PL/SQL table not the Database table. This way the integrity check can be done. The same trigger should assign the new end_date value to the PL/SQL table and update the value of the flag to indicate that this has to be changed.
4. Create a AFTER INSERT STATEMENT trigger to update the SALGRADE table with the values in the PL/SQL table after looking at the change flag.
All these programs can be created by the sources found below. I urge you to test them and make any enhancements to them as you find necessary.
CODE: ----
Code to create test table and populate it with data:
INSERT INTO SALGRADE VALUES (1,1000,2000, '1-apr-94', '3-aug-95'); INSERT INTO SALGRADE VALUES (1,1200,2200, '3-aug-95', null); INSERT INTO SALGRADE VALUES (2,1500,3000, '23-Jul-92', '12-dec-93'); INSERT INTO SALGRADE VALUES (2,1600,3200, '12-dec-93', '11-jan-95'); INSERT INTO SALGRADE VALUES (2,1800,3400, '11-jan-95', null);
Code for package STEP 1 above:
create or replace package salgrade_pkg as type datetabtype is table of date index by binary_integer; type chartabtype is table of char(1) index by binary_integer; type rowidtabtype is table of rowid index by binary_integer; start_date_tab datetabtype; end_date_tab datetabtype; rowid_tab rowidtabtype; changed_grade chartabtype; start_date_tab_size binary_integer; end; /
Code for before insert statement trigger STEP 2 above:
create or replace trigger tbiuds_salgrade before insert on salgrade declare hold_start_date date; hold_end_date date; hold_rowid rowid; hold_grade binary_integer; cursor start_date_cur is select rowid, grade, start_date from salgrade where end_date is null order by grade; begin open start_date_cur; loop fetch start_date_cur into hold_rowid, hold_grade, hold_start_date; exit when start_date_cur%notfound; salgrade_pkg.start_date_tab(hold_grade) := hold_start_date; salgrade_pkg.end_date_tab(hold_grade) := hold_end_date; salgrade_pkg.rowid_tab(hold_grade) := hold_rowid; salgrade_pkg.changed_grade(hold_grade) := 'N'; end loop; salgrade_pkg.start_date_tab_size := hold_grade; close start_date_cur; end; /
Code for after insert row trigger STEP 3 above:
create or replace trigger taiudr_salgrade after insert on salgrade for each row begin if (:new.grade <= salgrade_pkg.start_date_tab_size) then if salgrade_pkg.start_date_tab(:new.grade) > :new.start_date then raise_application_error(-20001,'Overlapping Dates'); end if; salgrade_pkg.end_date_tab(:new.grade) := :new.start_date; salgrade_pkg.changed_grade(:new.grade) := 'Y'; else salgrade_pkg.start_date_tab(:new.grade) := :new.start_date; salgrade_pkg.end_date_tab(:new.grade) := :new.end_date; salgrade_pkg.changed_grade(:new.grade) := 'N'; salgrade_pkg.start_date_tab_size := salgrade_pkg.start_date_tab_size + 1; end if; end; /
Code for after insert statement trigger STEP 4 above:
create or replace trigger taiuds_salgrade after insert on salgrade begin for i in 1..(salgrade_pkg.start_date_tab_size) loop if (salgrade_pkg.changed_grade(i) = 'Y') then update salgrade set end_date = salgrade_pkg.end_date_tab(i) where rowid = salgrade_pkg.rowid_tab(i); end if; end loop; end; /
Once all the package and triggers are created, the same insert doesn't succeed but gives the user the a more appropriate error:
SQL> insert into salgrade values (2, 9000, 100000, '25-dec-93', null); insert into salgrade values (2, 9000, 100000, '25-dec-93', null) * ERROR at line 1: ORA-20001: Overlapping Dates ORA-06512: at line 5 ORA-04088: error during execution of trigger 'JACK.TAIUDR_SALGRADE'
CONCLUSION: ----------
This method is not submitted as a solution to all cases, however, it is provided as one possibility. The test and code are provided so modifications can be made to suit the individual cases. Be sure to test this completely before relying on it's functionality.
Keywords: =========
MUTATING TABLE, TRIGGER, PROCEDURE, ORA-4091
--------------------------------------------------------------------------- Oracle WorldWide Customer Support .
Her er et ex. på en trigger der ikke fejler med mutating table
drop table test; create table test (nr1 number, nr2 number);
create trigger test_in before insert on test for each row declare Mutating_table EXCEPTION; PRAGMA EXCEPTION_INIT (Mutating_table, -4091); begin if :new.nr2 is null then insert into test (nr1, nr2) values (:new.nr1+1,:new.nr1); end if; EXCEPTION WHEN Mutating_table THEN NULL; end; /
insert into test (nr1, nr2) values (1,null);
denne vil give to rækker i tabellen
1,NULL og 2,1
insert i triggeren kan også være et select statement
coily => Hvordan kan dette afhjælpe at muteringen sker? Du fanger jo bare situationen og sørger for at intet bliver opdateret.
Synes godt om
Ny brugerNybegynder
Din løsning...
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.