Oracle - mutating trigger :: 4programmers.net

0

Witam wszystkich.

Mam problem z jednym triggerem w bazie Oracle.

Baza składa się z dwóch tabel:

plany_przedmiotow - przechowuje informacje o ocenach z danych przedmiotów dla każdego studenta.
rejestracje_semestrow - przechowuje informacje o zarejestrowanych semestrach dla danego studenta. Najważniejszą kolumną jest "zaliczony", która powinna się zmienić na "tak" w momencie gdy w tabeli plany_przedmiotow wszystkie przedmioty dla danego studenta są zaliczone pozytywnie.

 
drop table rejestracje_semestrow;
CREATE TABLE rejestracje_semestrow(
numer_albumu INTEGER,
numer_semestru INTEGER,
rok_akademicki INTEGER,
zaliczony CHARACTER(3),
data_uzyskania_zaliczenia DATE);

drop table plany_przedmiotow;
CREATE TABLE plany_przedmiotow(
symbol_przedmiotu CHARACTER(5),
data_zaliczenia DATE,
rej_numer_albumu INTEGER,
rej_numer_semestru INTEGER,
rej_rok_akademicki INTEGER,
ocena NUMBER(2,1));   

drop trigger zaliczenie;

create or replace trigger zaliczenie
after insert or update on plany_przedmiotow
for each row
declare
  v_zaliczenie rejestracje_semestrow.zaliczony%TYPE := 'tak';
  v_ocena number(1);
  cursor v_zaliczak is select ocena from plany_przedmiotow where rej_numer_albumu = :NEW.rej_numer_albumu;
begin
  open v_zaliczak;
    loop 
      fetch v_zaliczak into v_ocena;
      exit when v_zaliczak%NOTFOUND;
      if (v_ocena = 2 or v_ocena is null) then v_zaliczenie := 'nie';
      end if;
    end loop;
  close v_zaliczak;
    
if (v_zaliczenie = 'tak') then update rejestracje_semestrow set zaliczony = v_zaliczenie, data_uzyskania_zaliczenia = sysdate where numer_albumu = :NEW.rej_numer_albumu;
end if;
  
end;
/
show errors

-- przykładowe dane:
INSERT INTO rejestracje_semestrow VALUES(1,5,201011,'nie',NULL);
INSERT INTO plany_przedmiotow VALUES('PLS',TO_DATE('10-02-2011','DD-MM-YYYY'),1,5,201011,3);
INSERT INTO plany_przedmiotow VALUES('MGB',TO_DATE('15-02-2011','DD-MM-YYYY'),1,5,201011,4.5);
INSERT INTO plany_przedmiotow VALUES('FTRAN',NULL,1,5,201011,NULL);
INSERT INTO plany_przedmiotow VALUES('ANG',NULL,1,5,201011,NULL);
INSERT INTO plany_przedmiotow VALUES('PSS',NULL,1,5,201011,NULL);
INSERT INTO plany_przedmiotow VALUES('SQL',NULL,1,5,201011,NULL);
UPDATE plany_przedmiotow SET ocena = 3, data_zaliczenia = TO_DATE('23-03-2011','DD-MM-YYYY') WHERE rej_numer_albumu = 1 AND symbol_przedmiotu = 'FTRAN'; -- jeszcze nie zaliczył semestru
INSERT INTO rejestracje_semestrow VALUES(2,5,201011,'nie',NULL);
INSERT INTO plany_przedmiotow VALUES('PLS',TO_DATE('10-02-2011','DD-MM-YYYY'),2,5,201011,3);
INSERT INTO plany_przedmiotow VALUES('MGB',TO_DATE('15-02-2011','DD-MM-YYYY'),2,5,201011,4.5);
INSERT INTO plany_przedmiotow VALUES('FTRAN',NULL,2,5,201011,NULL);
INSERT INTO plany_przedmiotow VALUES('ANG',TO_DATE('15-02-2011','DD-MM-YYYY'),2,5,201011,5);
INSERT INTO plany_przedmiotow VALUES('PSS',TO_DATE('15-02-2011','DD-MM-YYYY'),2,5,201011,5);
INSERT INTO plany_przedmiotow VALUES('SQL',TO_DATE('15-02-2011','DD-MM-YYYY'),2,5,201011,5);
UPDATE plany_przedmiotow SET ocena = 4, data_zaliczenia = TO_DATE('23-03-2011','DD-MM-YYYY') WHERE rej_numer_albumu = 2 AND symbol_przedmiotu = 'FTRAN'; -- WŁAŚNIE ZALICZYŁ SEMESTR
COMMIT;

Trigger kompiluje się prawidłowo:

 
TRIGGER ZALICZENIE compiled
No Errors.

Ale w momencie gdy jest wyzwalany pojawia się komunikat:

 
Error starting at line 46 in command:
INSERT INTO plany_przedmiotow VALUES('PLS',TO_DATE('10-02-2011','DD-MM-YYYY'),1,5,201011,3)
Error report:
SQL Error: ORA-04091: table KRZYSIEK.PLANY_PRZEDMIOTOW is mutating, trigger/function may not see it
ORA-06512: at "KRZYSIEK.ZALICZENIE", line 4
ORA-06512: at "KRZYSIEK.ZALICZENIE", line 6
ORA-04088: error during execution of trigger 'KRZYSIEK.ZALICZENIE'
04091. 00000 -  "table %s.%s is mutating, trigger/function may not see it"
*Cause:    A trigger (or a user defined plsql function that is referenced in
           this statement) attempted to look at (or modify) a table that was
           in the middle of being modified by the statement which fired it.
*Action:   Rewrite the trigger (or function) so it does not read that table.

Próbowałem rozwiązać ten problem wczoraj przez cały dzień ale nie znalazłem rozwiązania.
Myślałem, że pomoże select w select ale to też nic nie dało...

Możecie pomóc?

Z góry dzięki za wszystkie podpowiedzi.

0

Zamień "after insert or update" na "before insert or update"

edit: no tak, nie doczytałem Twojego przypadku (brak kawy). Tutaj problem polega na tym, że robisz select z tabeli na której robisz insert/update. Tutaj http://www.club-oracle.com/forums/oracle-after-instert-trigger-mutation-problem-t184/ jest przykładowe obejście problemu.

0

nie możesz w trigerze robić zapytania na tabeli plany_przedmiotow ponieważ ona się właśnie zmienia
jedyne rozwiązanie to kombinacja dwóch trigerów i paczki, coś jak tu http://forums.oracle.com/forums/message.jspa?messageID=1233090#1233090

0

Dzięki za podpowiedzi.
Ogólnie nie mogę robić zapytania w triggerze kiedy robię na niej insert. Poczytałem o sposobach jakie mi podaliście, zmodyfikowałem trochę i zrobiłem coś takiego:

 
drop table rejestracje_semestrow;
CREATE TABLE rejestracje_semestrow(
numer_albumu INTEGER,
numer_semestru INTEGER,
rok_akademicki INTEGER,
zaliczony CHARACTER(3),
data_uzyskania_zaliczenia DATE);

drop table plany_przedmiotow;
CREATE TABLE plany_przedmiotow(
symbol_przedmiotu CHARACTER(5),
data_zaliczenia DATE,
rej_numer_albumu INTEGER,
rej_numer_semestru INTEGER,
rej_rok_akademicki INTEGER,
ocena NUMBER(2,1)); 

drop table bufor;
create table bufor(
album integer,
przed CHARACTER(5),
ocena number (2,1));

drop trigger zaliczenie1;
create or replace trigger zaliczenie1
before insert or update on plany_przedmiotow
for each row
begin
  if inserting then insert into bufor values (:NEW.rej_numer_albumu, :NEW.symbol_przedmiotu, :NEW.ocena);
  end if;
  if updating then update bufor set ocena = :NEW.ocena where album = :NEW.rej_numer_albumu and przed = :NEW.symbol_przedmiotu;
  end if;
end;
/
show errors

drop trigger zaliczenie2;
create or replace trigger zaliczenie2
after insert or update on plany_przedmiotow
for each row
declare 
  v_zaliczenie rejestracje_semestrow.zaliczony%TYPE := 'tak';
  v_ocena number(2,1);
  cursor v_zaliczak is select ocena from bufor where album = :NEW.rej_numer_albumu;
begin
  open v_zaliczak;
    loop 
      fetch v_zaliczak into v_ocena;
      exit when v_zaliczak%NOTFOUND;
      if (v_ocena = 2 or v_ocena is null) then v_zaliczenie := 'nie';
      end if;
    end loop;
  close v_zaliczak;
    
  update rejestracje_semestrow set zaliczony = v_zaliczenie, data_uzyskania_zaliczenia = sysdate where numer_albumu = :NEW.rej_numer_albumu;
  
end;
/
show errors

INSERT INTO rejestracje_semestrow VALUES(1,5,201011,'nie',NULL);
INSERT INTO plany_przedmiotow VALUES('PLS',TO_DATE('10-02-2011','DD-MM-YYYY'),1,5,201011,3);
INSERT INTO plany_przedmiotow VALUES('MGB',TO_DATE('15-02-2011','DD-MM-YYYY'),1,5,201011,4.5);
INSERT INTO plany_przedmiotow VALUES('FTRAN',NULL,1,5,201011,NULL);
INSERT INTO plany_przedmiotow VALUES('ANG',NULL,1,5,201011,NULL);
INSERT INTO plany_przedmiotow VALUES('PSS',NULL,1,5,201011,NULL);
INSERT INTO plany_przedmiotow VALUES('SQL',NULL,1,5,201011,NULL);
UPDATE plany_przedmiotow SET ocena = 3, data_zaliczenia = TO_DATE('23-03-2011','DD-MM-YYYY') WHERE rej_numer_albumu = 1 AND symbol_przedmiotu = 'FTRAN'; -- jeszcze nie zaliczył semestru
INSERT INTO rejestracje_semestrow VALUES(2,5,201011,'nie',NULL);
INSERT INTO plany_przedmiotow VALUES('PLS',TO_DATE('10-02-2011','DD-MM-YYYY'),2,5,201011,3);
INSERT INTO plany_przedmiotow VALUES('MGB',TO_DATE('15-02-2011','DD-MM-YYYY'),2,5,201011,4.5);
INSERT INTO plany_przedmiotow VALUES('FTRAN',NULL,2,5,201011,NULL);
INSERT INTO plany_przedmiotow VALUES('ANG',TO_DATE('15-02-2011','DD-MM-YYYY'),2,5,201011,5);
INSERT INTO plany_przedmiotow VALUES('PSS',TO_DATE('15-02-2011','DD-MM-YYYY'),2,5,201011,5);
INSERT INTO plany_przedmiotow VALUES('SQL',TO_DATE('15-02-2011','DD-MM-YYYY'),2,5,201011,5);
UPDATE plany_przedmiotow SET ocena = 3, data_zaliczenia = TO_DATE('23-03-2011','DD-MM-YYYY') WHERE rej_numer_albumu = 2 AND symbol_przedmiotu = 'FTRAN'; -- WŁAŚNIE ZALICZYŁ SEMESTR
COMMIT;

select * from plany_przedmiotow;
select * from rejestracje_semestrow;
select * from bufor;

I teraz działa :-)
Mam "mały problem z "datą zaliczenia" ale to już kosmetyka.

Jeszcze raz dzięki za pomoc.

Oracle - mutating trigger

1 użytkowników online, w tym zalogowanych: 0, gości: 1

Praca dla programistów

Forum dyskusyjne

Sprawy administracyjne

O nas

Skontaktuj się z nami