Documentazione di PostgreSQL 9.0 > Programmazione del server > PL/pgSQL - Linguaggio procedurale SQL > Procedure Trigger
PrecedenteErrori e messaggiPL/pgSQL Under the HoodSuccessivo

39.9. Procedure Trigger

PL/pgSQL può essere usato per definire procedure trigger. Una procedura trigger viene creata con il comando CREATE FUNCTION, dichiarandola come una funzione senza argomenti e trigger come tipo di ritorno. Notare che la funzione deve essere dichiarata senza argomenti anche se si aspetta di ricevere argomenti specificati in CREATE TRIGGER - gli argomenti del trigger sono passati via TG_ARGV, come descritto sotto.

Quando una funzione PL/pgSQL è chiamata come trigger, diverse variabili speciali sono create automaticamente nel blocco superiore. Esse sono:

NEW

Tipo di dato RECORD; variabile che tiene la nuova riga del database per operazioni INSERT/UPDATE in trigger a livello di riga. Questa variabile è NULL in trigger a livello di istruzione e per operazioni DELETE.

OLD

Tipo di dato RECORD; variabile che tiene la vecchia riga del database per operazioni UPDATE/DELETE in trigger a livello di riga. Questa variabile è NULL in trigger a livello di istruzione e per operazioni INSERT.

TG_NAME

Tipo di dato name; variabile che contiene il nome del trigger attualmente attivato.

TG_WHEN

Data type text; a string of either Tipo di dato text; una stringa BEFORE o AFTER a seconda della definizione del trigger.

TG_LEVEL

Tipo di dato text; una stringa ROW o STATEMENT a seconda della definizione del trigger.

TG_OP

Data type text; a string of Tipo di dato text; una stringa INSERT, UPDATE, DELETE, o TRUNCATE che dice per quale operazione è stato attivato il trigger.

TG_RELID

Tipo di dato oid; l'ID dell'oggetto della tabella che ha causato l'invocazione del trigger.

TG_RELNAME

Tipo di dato name; il nome della tabella che ha causato l'invocazione del trigger. Questo ora è deprecato, e può sparire in una versione futura. Usare invece TG_TABLE_NAME.

TG_TABLE_NAME

Tipo di dato name; il nome della tabella che ha causato l'invocazione del trigger.

TG_TABLE_SCHEMA

Tipo di dato name; il nome dello schema della tabella che ha causato l'invocazione del trigger.

TG_NARGS

Tipo di dato integer; il numero di argomenti forniti alla procedura trigger nell'istruzione CREATE TRIGGER.

TG_ARGV[]

Tipo di dato text; gli argomenti dell'istruzione CREATE TRIGGER. L'indice conta da 0. Gli indici invalidi (minori di 0 o maggiori di, o uguali, a tg_nargs) risultano in un valore null.

Una funzione trigger deve restituire o NULL o un valore record/riga avente esattamente la struttura della tabelle per cui è stato attivato il trigger.

Trigger a livello di riga attivati prima (BEFORE) possono restituire null per segnalare al gestore dei trigger di saltare il resto dell'operazione per questa riga. (per es., i trigger successivi non vengono attivati, e le INSERT/UPDATE/DELETE non accadono per questa riga). Se viene restituito un valore nonnull allora l'operazione procede con quel valore della riga. Restituire un valore rida diverso dal valore originale di NEW altera la riga che sarà inserita o aggiornata. Così, se la funzione trigger vuole che l'azione di trigger riesca normalmente senza alterare il valore della riga, deve essere restituito NEW (o un valore ad esso equivalente). Per modificare la riga da salvare, è possibile sostituire singoli valori direttamente in NEW e restituire la NEW modificata, o costruire un record/riga completamente nuovo da restituire. Nel caso di un trigger before su DELETE, il valore restituito non ha un effetto diretto, ma deve essere non null per permettere all'azione trigger di procedere. Notare che NEW è null nei trigger DELETE, quindi restituirlo di solito non è sensato. Un'espressione utile nei trigger DELETE potrebbe restituire OLD.

Il valore di ritorno di un trigger a livello di riga attivato AFTER o un trigger a livello di instruzione attivato BEFORE o AFTER è sempre ignorato; potrebbe anch'esso essere null. Comunque, uno qualsiasi di questi tipi di trigger potrebbe ancora annullare l'intera operazione sollevando un errore.

Esempio 39.3, «Una procedura trigger PL/pgSQL» mostra un esempio di una procedura trigger in PL/pgSQL.

Esempio 39.3. Una procedura trigger PL/pgSQL

Questo trigger di esempio assicura che ogni volta una riga venga inserita o aggiornata nella tabella, il nome utente corrente e l'orario siano salvati nella riga. E controlla che oil nome di un impiegato sua fornito e che il salario sia un valore positivo.

CREATE TABLE emp (
    empname text,
    salary integer,
    last_date timestamp,
    last_user text
);

CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$
    BEgin
        -- Check that empname and salary are given
        IF NEW.empname IS NULL THEN
            RAISE EXCEPTION 'empname cannot be null';
        END IF;
        IF NEW.salary IS NULL THEN
            RAISE EXCEPTION '% cannot have null salary', NEW.empname;
        END IF;

        -- Who works for us when she must pay for it?
        IF NEW.salary < 0 THEN
            RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
        END IF;

        -- Remember who changed the payroll when
        NEW.last_date := current_timestamp;
        NEW.last_user := current_user;
        RETURN NEW;
    END;
$emp_stamp$ LANGUAGE plpgsql;

CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
    FOR EACH ROW EXECUTE PROCEDURE emp_stamp();

Un altro modo di annotare i cambiamenti a una tabella implica la creazione di una nuova tabella che salva una riga per ogni insert, update, o delete che accade. Questo approccio può essere pensato come una revisione dei cambiamenti a una tabella. Esempio 39.4, «Una procedura trigger PL/pgSQL per la revisione» mostra un esempio di una procedura trigger di revisione in PL/pgSQL.

Esempio 39.4. Una procedura trigger PL/pgSQL per la revisione

Questo trigger di esempio assicura che qualsiasi inserimento, aggiornamento o cancellazione di una riga nella tabella emp sia registrata nella tabella emp_audit. L'orario e il nome utente corrente sono salvati nella riga, insieme al tipo di operazione svolta.

CREATE TABLE emp (
    empname           text NOT NULL,
    salary            integer
);

CREATE TABLE emp_audit(
    operation         char(1)   NOT NULL,
    stamp             timestamp NOT NULL,
    userid            text      NOT NULL,
    empname           text      NOT NULL,
    salary integer
);

CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
    BEGIN
        --
        -- Crea una riga un emp_audit per riflettere l'operazione eseguita su emp, 
        -- usa la variabile speciale TG_OP per portare a termine l'operazione.
        --
        IF (TG_OP = 'DELETE') THEN
            INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*;
            RETURN OLD;
        ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO emp_audit SELECT 'U', now(), user, NEW.*;
            RETURN NEW;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO emp_audit SELECT 'I', now(), user, NEW.*;
            RETURN NEW;
        END IF;
        RETURN NULL; -- il risultato viene ignorato dato che questo è un trigger AFTER
    END;
$emp_audit$ LANGUAGE plpgsql;

CREATE TRIGGER emp_audit
AFTER INSERT OR UPDATE OR DELETE ON emp
    FOR EACH ROW EXECUTE PROCEDURE process_emp_audit();

Un uso dei trigger è di mantenere una tabella di sommario di un'altra tabella. Il sommario risultante può essere usato al posto della tabella originale per certe query - spesso con tempi di esecuzione enormemente ridotti. Questa tecnica è usata comunemente nel Data Warehousing, dove le tabelle dei dati misurati o osservati (chiamate tabelle dei fatti) potrebbero essere estremamente grandi. Esempio 39.5, «Una procedura trigger PL/pgSQL per mantenere una tabella di sommario» mostra un esempio di una procedura trigger in PL/pgSQL che mantiene una tabella di sommario per una tabella di fatti in un data warehouse.

Esempio 39.5. Una procedura trigger PL/pgSQL per mantenere una tabella di sommario

Lo schema presentato in dettaglio qui è basato parzialmente sull'esempio Grocery Store preso da The Data Warehouse Toolkit di Ralph Kimball.

--
-- Main tables - time dimension and sales fact.
-- Tabelle principali - time dimension e sales fact.
--
CREATE TABLE time_dimension (
    time_key                    integer NOT NULL,
    day_of_week                 integer NOT NULL,
    day_of_month                integer NOT NULL,
    month                       integer NOT NULL,
    quarter                     integer NOT NULL,
    year                        integer NOT NULL
);
CREATE UNIQUE INDEX time_dimension_key ON time_dimension(time_key);

CREATE TABLE sales_fact (
    time_key                    integer NOT NULL,
    product_key                 integer NOT NULL,
    store_key                   integer NOT NULL,
    amount_sold                 numeric(12,2) NOT NULL,
    units_sold                  integer NOT NULL,
    amount_cost                 numeric(12,2) NOT NULL
);
CREATE INDEX sales_fact_time ON sales_fact(time_key);

--
-- Tabella di sommario - sales by time.
--
CREATE TABLE sales_summary_bytime (
    time_key                    integer NOT NULL,
    amount_sold                 numeric(15,2) NOT NULL,
    units_sold                  numeric(12) NOT NULL,
    amount_cost                 numeric(15,2) NOT NULL
);
CREATE UNIQUE INDEX sales_summary_bytime_key ON sales_summary_bytime(time_key);

--
-- Funzioni e trigger per rettificare le colonne sommarizate sulle UPDATE, INSERT, DELETE.
--
CREATE OR REPLACE FUNCTION maint_sales_summary_bytime() RETURNS TRIGGER
AS $maint_sales_summary_bytime$
    DECLARE
        delta_time_key          integer;
        delta_amount_sold       numeric(15,2);
        delta_units_sold        numeric(12);
        delta_amount_cost       numeric(15,2);
    BEgin

        -- Mette a posto l'ammontare degli incrementi/decrementi.
        IF (TG_OP = 'DELETE') THEN

            delta_time_key = OLD.time_key;
            delta_amount_sold = -1 * OLD.amount_sold;
            delta_units_sold = -1 * OLD.units_sold;
            delta_amount_cost = -1 * OLD.amount_cost;

        ELSIF (TG_OP = 'UPDATE') THEN

            -- forbid updates that change the time_key -
            -- (probably not too onerous, as DELETE + INSERT is how most
            -- changes will be made).
            IF ( OLD.time_key != NEW.time_key) THEN
                RAISE EXCEPTION 'Update of time_key : % -> % not allowed',
                                                      OLD.time_key, NEW.time_key;
            END IF;

            delta_time_key = OLD.time_key;
            delta_amount_sold = NEW.amount_sold - OLD.amount_sold;
            delta_units_sold = NEW.units_sold - OLD.units_sold;
            delta_amount_cost = NEW.amount_cost - OLD.amount_cost;

        ELSIF (TG_OP = 'INSERT') THEN

            delta_time_key = NEW.time_key;
            delta_amount_sold = NEW.amount_sold;
            delta_units_sold = NEW.units_sold;
            delta_amount_cost = NEW.amount_cost;

        END IF;


        -- Inserisce o aggiorna la riga di sommario con i nuovi valori.
        <<insert_update>>
        LOOP
            UPDATE sales_summary_bytime
                SET amount_sold = amount_sold + delta_amount_sold,
                    units_sold = units_sold + delta_units_sold,
                    amount_cost = amount_cost + delta_amount_cost
                WHERE time_key = delta_time_key;

            EXIT insert_update WHEN found;

            BEGIN
                INSERT INTO sales_summary_bytime (
                            time_key,
                            amount_sold,
                            units_sold,
                            amount_cost)
                    VALUES (
                            delta_time_key,
                            delta_amount_sold,
                            delta_units_sold,
                            delta_amount_cost
                           );

                EXIT insert_update;

            EXCEPTION
                WHEN UNIQUE_VIOLATION THEN
                    -- non fare niente
            END;
        END LOOP insert_update;

        RETURN NULL;

    END;
$maint_sales_summary_bytime$ LANGUAGE plpgsql;

CREATE TRIGGER maint_sales_summary_bytime
AFTER INSERT OR UPDATE OR DELETE ON sales_fact
    FOR EACH ROW EXECUTE PROCEDURE maint_sales_summary_bytime();

INSERT INTO sales_fact VALUES(1,1,1,10,3,15);
INSERT INTO sales_fact VALUES(1,2,1,20,5,35);
INSERT INTO sales_fact VALUES(2,2,1,40,15,135);
INSERT INTO sales_fact VALUES(2,3,1,10,1,13);
SELECT * FROM sales_summary_bytime;
DELETE FROM sales_fact WHERE product_key = 1;
SELECT * FROM sales_summary_bytime;
UPDATE sales_fact SET units_sold = units_sold * 2;
SELECT * FROM sales_summary_bytime;

Documentazione di PostgreSQL 9.0 > Programmazione del server > PL/pgSQL - Linguaggio procedurale SQL > Procedure Trigger
PrecedenteErrori e messaggiPL/pgSQL Under the HoodSuccessivo