Materiały dydaktyczne do zajęć o wyzwalaczach

Zadanie

Napisać wyzwalacze, które zapewnią realizację opcji ON DELETE SET NULL i ON UPDATE CASCADE na przykładowych tabelach dla klucza obcego b2 w tabeli b.

Przypominam, że obecnie w Oracle obie te opcje sa niedostępne.

Rozwiązanie

Oto możliwe kolejne fazy prac prowadzących do rozwiązania tego zadania.

trigger1.sql

Tu nie ma wyzwalaczy. DELETE nie powiedzie się, bo domyślnie jest ON DELETE RESTRICT. Moglibyśmy dodać ON DELETE CASCADE do więzów klucza obcego (tak jak w trigger1a.sql), ale nam przecież chodzi o ON DELETE SET NULL.

trigger2.sql

Tu jest już odpowiedni wyzwalacz dla ON DELETE SET NULL, ale nie będzie dzialał ze względu na bardzo zachłanny mechanizm zamków w Oracle. Wykonanie operacji na tabeli powoduje zablokowanie tej tabeli (tu A) i wszystkich tabel do niej się odwołujących (tu B). DELETE nie powiedzie się, bo w trakcie wykonywania wyzwalacza zablokowana jest również tabela B.

trigger3.sql

Żeby tego uniknąć należy usunąć więzy spójności referencyjnej (REFERENCES A). Teraz wyzwalacz działa, ale spójność referencyjna nie jest sprawdzana...

trigger4.sql

Dodaliśmy wyzwalacz pilnujący spójności referencyjnej. To działa, dlatego, że w tym wyzwalaczu sprawdzamy, czy :new.b2 nie jest przypadkiem NULL.

trigger5.sql

Naiwna próba dodania wyzwalacza realizującego ON UPDATE CASCADE. To nie działa, bo wykonanie UPDATE na A, wzbudza wyzwalacz OnUpdCascade, który wykonując UPDATE na B wzbudza wyzwalacz OnInsertOrUpdCheck, który z kolei próbuje wykonać SELECT na A, co kończy się błędem, bo A jest w tej chwili zablokowana.

trigger6.sql

Ostateczne rozwiązanie. Dodajemy pakiet ze zmienną enabled, którą odczytuje (przez wywołanie funkcji is_enabled) wyzwalacz OnInsertOrUpdCheck i z jej wartości wnioskuje czy ma wykonać sprawdzenie, czy nie. Nie wykonuje sprawdzenia jedynie w czasie działania OnUpdCascade, ale wtedy wiemy, że ten właśnie wyzwalacz dba o spójność bazy danych (jest wiarygodny i nie musimy sprawdzać wyników jego działania).