Lab08 - Wyzwalacze, PL/pgSQL [ ver. BD1.2025.11.25.003 ]

• Wyzwalacze w bazie PostgreSQL

Wyzwalacze są procedurami wykonywanymi automatycznie przy zajściu określonego zdarzenia, np. wstawieniu nowego rekordu do określonej tabeli. Pierwotnie wyzwalacze nie miały służyć do zapewnienia legalności stanów bazy (w ramach SQL do realizacji tego zagadnienia opracowano warunki integralności i asercje) lecz do zapewnienia legalności przejść między stanami. Jednak większość DBMS nie implementuje asercji a wyzwalacze używamy również do realizacji złożonych ograniczeń, których nie można wyrazić w poleceniach takich jak CREATE TABLE.

W strukturze wyzwalacza wyróżniami trzy elementy:

• Zdarzenie (Event): np. modyfikacja tabeli,
• Warunek (Condition) : wyrażenie Booleowskie w SQL,
• Akcje (Action): polecenia do wykonania, najczęsciej zapisywane w SQL lub PL/SQL.

Składnia wyzwalacza napisanego w SQL (mniej więcej zgodna ze standardem SQL ISO) przedstawiona została poniżej.

CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
    ON table_name
    [ FROM referenced_table_name ]
    [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    [ WHEN ( condition ) ]
    EXECUTE PROCEDURE function_name ( arguments )

where event can be one of:

    INSERT
    UPDATE [ OF column_name [, ... ] ]
    DELETE
    TRUNCATE

W ramach wyzwalacza określamy, czy ich akcje mają być wykonane przed czy po właściwej operacji (BEFORE lub AFTER). Ponadto dla wyzwalacza określony jest jeden z dwóch poziomów: wiersza lub zadanie. Wyzwalacz poziomu zadania jest realizowany tylko raz dla całego polecenia SQL, natomiast wyzwalacz poziomu wiersza jest realizowany niezależnie dla każdego modyfikowanego wiersza.

W tabeli poniżej zaprezentowano możliwości wykorzystania wyzwalaczy dla różnych zdarzeń.

Funkcja występująca w wyzwalaczu w Postgresie definiowana jest w PL/pgSQL. Jest to bezargumentowa funkcja zwracająca specjalny typ TRIGGER. Funkcja połączona z wyzwalaczem otrzymuje dane za pośrednictwem struktury TriggerData, a nie przez zwykłe parametry funkcyjne, dlatego procedur tych nie należy wywoływać bezpośrednio. Sa one wywoływane niejawnie przez wyzwalacz, ilekroć wystąpi zdarzenie z nim związane.

Poniżej przykładowa konstrukcja funkcji realizowanej przez wyzwalacz.

CREATE FUNCTION name () RETURNS TRIGGER AS $$
... ciało funkcji ...
$$ LANGUAGE 'plpgsql';

W ramach funkcji dostępne są specjalne zmienne.

• OLD – zmienna typu RECORD reprezentuje wiersz przed modyfikacją dla poleceń UPDATE i DELETE. Posiada wartość NULL w przypadku poleceń INSERT, TRUNCATE, znacznika FOR EACH STATEMENT oraz operacji wykonywanych w języku SQL.
• NEW – zmienna typu RECORD reprezentuje wiersz po modyfikacji dla poleceń INSERT i UPDATE. Posiada wartość NULL w przypadku poleceń DELETE, TRUNCATE, znacznika FOR EACH STATEMENT oraz operacji wykonywanych w języku SQL.
• TG_NAME – nazwa wykonywanego wyzwalacza;
• TG_WHEN – zwraca czas realizacji operacji: BEFORE lub AFTER;
• TG_LEVEL – zwraca poziom realizacji wyzwalacza: ROW lub STATEMENT;
• TG_OP – zwraca typ wykonanej operacji dla której wywołano wyzwalacz: INSERT, UPDATE, lub DELETE;
• TG_RELID, TG_RELNAME – OID i nazwa tabeli, dla której uruchomiono wyzwalacz;
• TGTG _NARGS, TG_ARGV[] – Liczba argumentów i ich kolekcja, przekazanych do funkcji wyzwalacza.

Wyzwalacze wykorzystujemy najcześciej w następujących sytuacjach:

• kaskadowa aktualizacja danych w powiązanych tabelach;
• sprawdzanie poprawności danych na podstawie wartości przechowywanych w dowolnych tabelach (w przeciwieństwie do zawężenia CHECK, za pomocą którego możemy odwołać się jedynie do bieżącej tabeli);
• równoczesne sprawdzanie zmodyfikowanych danych w dowolnej liczbie rekordów tabeli;
• modyfikacje danych w bazach niespełniających wymogów trzeciej postaci normalnej. W bazach tego typu przechowywane są informacje nadmiarowe (redundantne) i modyfikacja danych w jednej tabeli może wiązać się z koniecznością zmiany tych danych w innych tabelach;
• wywoływanie predefiniowanych lub zdefiniowanych przez użytkownika komunikatów błędu;
• monitorowanie aktywności użytkowników;

CREATE FUNCTION nazwa ([typ_danych [, ]])
  RETURNS typ_wyniku
  AS definicja
  LANGUAGE nazwa_jezyka

Polecenia SQL i interfejsu psql pomagające przetwarzać opracowane funkcje.

-- Wyświetlenie funkcji użytkownika - polecenie z interfejsu psql
\df
-- Wyświetlenie funkcji użytkownika - polecenie SQL - tablica pg_proc
\d pg_proc
-- Polecenie SQL wyświetlające zawartość procedury
SELECT prosrc FROM pg_proc WHERE proname='nazwa_funkcji' ;
-- Polecenie modyfikujące procedurę lub je tworzące
CREATE OR REPLASE FUNCTION ...
-- Polecenie usuwające procedurę
DROP FUNCTION nazwa_funkcji([lista typów])

Przykładowe skrypty wyzwalaczy.

Monitorowanie zmian w bazie danych.

-- Tworzyma nowy schemat
CREATE SCHEMA lab08;
SET SEARCH_PATH TO lab08;

-- Tabela person
CREATE TABLE person ( id int, groups char(2), fname varchar(15), lname varchar(15) ) ; 
ALTER TABLE person ADD PRIMARY KEY (id);

-- Tabela zapisujaca operacje wykonane w bazie danych
CREATE TABLE audit (
    table_name varchar(15) not null,
    operation varchar,
    time_at timestamp not null default now(),
    userid name not null default session_user
);

-- Funkcja na zapisujaca operacje do tabeli audit
CREATE OR REPLACE FUNCTION audit_log ()
    RETURNS TRIGGER
    LANGUAGE plpgsql
    AS $$
    BEGIN
 
    INSERT INTO audit (table_name, operation) 
        VALUES (TG_RELNAME, TG_OP);
 
    RETURN NEW;                                                          
    END;
    $$;

-- Wyzwalacz monitorujacy dzialania na tabeli person 
CREATE TRIGGER person_audit 
    AFTER INSERT OR UPDATE OR DELETE ON person
    FOR EACH ROW EXECUTE PROCEDURE audit_log();  
    
-- Sprawdzenie poprawnosci opracowanego wyzwalacza
INSERT INTO person VALUES ( 1, 'AA', 'Adam', 'Abacki' )  ;

SELECT * FROM person ;
SELECT * FROM audit ;

DROP TRIGGER person_audit ON person;  
  

Testowanie poprawności wprowadzonych danych.

-- Funkcja sprawdzajaca poprawnosc wprowadzonych danych
CREATE OR REPLACE FUNCTION valid_data ()
    RETURNS TRIGGER
    LANGUAGE plpgsql
    AS $$
    BEGIN
    IF LENGTH(NEW.lname) = 0 THEN
        RAISE EXCEPTION 'Nazwisko nie moze byc puste.';
    END IF;
  
    RETURN NEW;                                                          
    END;
    $$;

-- Wyzwalacz monitorujacy poprawnosc danych dla tabeli person 
CREATE TRIGGER person_valid 
    AFTER INSERT OR UPDATE OR DELETE ON person
    FOR EACH ROW EXECUTE PROCEDURE valid_data();  
    
-- Sprawdzenie poprawnosci opracowanych skryptów    
INSERT INTO person VALUES ( 2, 'AA', '','');    
    
DROP TRIGGER person_valid ON test; 
  

Modyfikacja wprowadzanych danych do tabeli.

-- Funkcja normalizujaca wprowadzone dane do bazy danych  
CREATE OR REPLACE FUNCTION norm_data () RETURNS TRIGGER AS $$
BEGIN
  IF NEW.lname IS NOT NULL THEN
     NEW.lname := lower(NEW.lname);
     NEW.lname := initcap(NEW.lname);
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';

-- Przypisanie kursora do tabeli person
CREATE TRIGGER person_norm
  BEFORE INSERT OR UPDATE ON person
  FOR EACH ROW
  EXECUTE PROCEDURE norm_data();
  
INSERT INTO person VALUES ( 2, 'bb', 'Adam','babacki'), ( 3, 'bb', 'Marek','cabacki'), 
  ( 4, 'cc', 'Adam','kabacki'), ( 5, 'dd', 'Teresa','Zak'); 
  

Testowanie danych na podstawie informacji z innych tabel.

CREATE TABLE person_group ( name varchar(15), nc int ) ;  

INSERT INTO person_group VALUES ( 'aa', 2), ( 'bb', 3 ), ( 'cc', 4 ) ;

    
CREATE OR REPLACE FUNCTION group_count() RETURNS TRIGGER AS $$
    BEGIN
        IF EXISTS(SELECT 1 FROM person_group WHERE name = New.groups 
           and nc > (SELECT count(*) FROM person WHERE groups = New.groups )) THEN
            -- rekord nie zostanie dodany ani zaktualizowany
            RETURN NEW;
        ELSE
            RETURN NULL;
        END IF;
    END;
$$ LANGUAGE 'plpgsql';  

CREATE TRIGGER person_test_insert 
    BEFORE INSERT OR UPDATE ON person
    FOR EACH ROW EXECUTE PROCEDURE group_count();  
    
    
INSERT INTO person VALUES ( 11, 'aa', 'Adam','Babacki'), 
    ( 12, 'aa', 'Marek','Cabacki'), ( 13, 'aa', 'Adam','Babacki'), 
    ( 14, 'aa', 'Teresa','Dadacka');     
    
DROP TRIGGER person_test_insert ON person;   
  

Testowanie danych na podstawie informacji z innych tabel.

CREATE TABLE person_group ( name varchar(15), nc int ) ;

INSERT INTO person_group VALUES ( 'aa', 2), ( 'bb', 3 ), ( 'cc', 4 ) ;


CREATE OR REPLACE FUNCTION group_count() RETURNS TRIGGER AS $$
    BEGIN
        IF EXISTS(SELECT 1 FROM person_group WHERE name = New.groups
           and nc > (SELECT count(*) FROM person WHERE groups = New.groups )) THEN
            -- rekord nie zostanie dodany ani zaktualizowany
            RETURN NEW;
        ELSE
            RETURN NULL;
        END IF;
    END;
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER person_test_insert
    BEFORE INSERT OR UPDATE ON person
    FOR EACH ROW EXECUTE PROCEDURE group_count();


INSERT INTO person VALUES ( 11, 'aa', 'Adam','Babacki'),
    ( 12, 'aa', 'Marek','Cabacki'), ( 13, 'aa', 'Adam','Babacki'),
    ( 14, 'aa', 'Teresa','Dadacka');

DROP TRIGGER person_test_insert ON person;
  

Wprowadzanie danych do tabel powiązanych.

CREATE TABLE person_data ( id int, city varchar(30), email varchar(30), telefon varchar(15) );
ALTER TABLE person_data ADD PRIMARY KEY (id);
INSERT INTO person_data (id) SELECT id FROM person;
ALTER TABLE person_data ADD FOREIGN KEY (id) REFERENCES person(id);

--
CREATE OR REPLACE FUNCTION insert_data () RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO person_data (id) VALUES (New.id) ;
  RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';

--

CREATE TRIGGER person_insert
    AFTER INSERT ON person
    FOR EACH ROW EXECUTE PROCEDURE insert_data();


INSERT INTO person VALUES ( 21, 'bb', 'Zygmunt','Bielecki');

SELECT * from person;
SELECT * FROM person_data;
SELECT * FROM audit;


  

Wprowadzanie danych na podstawie widoku.

create view person_v AS SELECT p.id,p.fname,p.lname,pd.city,pd.email,pd.phone FROM (person p JOIN person_data pd USING (id)) ;
--
CREATE OR REPLACE FUNCTION person_v_dml () RETURNS TRIGGER AS $$
   BEGIN                                                                                                      
    IF TG_OP = 'INSERT' THEN                                                                                  
      INSERT INTO  person (id, fname, lname) VALUES (NEW.id,NEW.fname,NEW.lname);                             
      INSERT INTO  person_data VALUES(NEW.id, NEW.city, NEW.email, NEW.phone);                                
      RETURN NEW;                                                                                             
    ELSIF TG_OP = 'UPDATE' THEN                                                                               
      UPDATE person SET id=NEW.id, fname=NEW.fname, lname=NEW.lname WHERE        id=OLD.id;                   
      UPDATE person_data SET id=NEW.id, city=NEW.city, email=NEW.email,     telefon=NEW.phone WHERE id=OLD.id;
      RETURN NEW;                                                                                             
    ELSIF TG_OP = 'DELETE' THEN                                                                               
      DELETE FROM person WHERE id=OLD.id;                                                                     
      DELETE FROM person_data WHERE id=OLD.id;                                                                
      RETURN NULL;                                                                                            
    END IF;                                                                                                   
    RETURN NEW;                                                                                               
  END; 
$$ LANGUAGE 'plpgsql';

-- 

CREATE TRIGGER person_v_dml_trig  
    INSTEAD OF INSERT OR UPDATE OR DELETE ON person_v
    FOR EACH ROW EXECUTE PROCEDURE person_v_dml(); 
    
  

Obsługa tabel statystycznych.

   Opracować wyzwalacz aktualizujący tabelę zawierającą liczbę wszystkich osób w danej grupie.
  

1. 41.9. Trigger Procedures
2. PostgreSQL Triggers
3. CREATE TRIGGER