Modifica logo joomla

cbg

Procedure Trigger, function trigger

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;

Confronti di soluzioni etl

Integration Vendors Comparison

Detailed Comparison Matrix of Integration Solutions

Feature	Adeptia Integration Suite	IBM, TIBCO, WebMethods	Sterling Commerce Gentran IS	Microsoft BizTalk	Pervasive Data Integrator	Cast Iron Systems (Appliance)	Boomi	JitterBit Enterprise
Easy to Use	Yes	No	No	No	OK	Yes	Yes	Yes
Web-based UI	Yes	No	No	No	No	No	Yes	No
Multi-role Team Collaboration	Yes	Yes	Yes	No	No	No	No	Yes
Graphical Process Orchestration	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes
Graphical Data Mapper	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes
Process Centric Approach	Yes	Yes	Yes	Yes	No	No	No	No
Enables SOA	Yes	Yes	Yes	No	No	No	No	Yes
Reusable Services Repository	Yes	Yes	No	No	No	No	No	Yes
Single-install,Light Footprint	Yes	No	No	No	Yes	Yes	Yes	Yes
Large Volume Performance	Yes	Yes	OK	OK	OK	No	No	Yes
Clustering and Job Distribution	Yes	Yes	Yes	Yes	No	No	Yes	Yes
Data Partitioning	Yes	Yes	Yes	Yes	Yes	No	No	No
Automatic Recovery of Flows	Yes	Yes	No	OK	No	No	No	No
XA-Transaction Rollbacks	Yes	Yes	Yes	OK	No	No	No	No
Meta-Driven Approach vs.Code	Yes	Yes	No	No	No	No	No	Yes
Complex Flows (Fork/Join etc.)	Yes	Yes	Yes	Yes	Yes	No	No	Yes
Sub-Processes	Yes	Yes	Yes	OK	Yes	No	No	Yes
Built-in Scheduler	Yes	Yes	Yes	No	No	Yes	Yes	Yes
Real-time Triggers	Yes	Yes	Yes	Yes	Yes	OK	Yes	Yes
Event Driven Processing	Yes	Yes	Yes	Yes	Yes	No	Yes	Yes
RDBMS Connections	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes
Non-RDBMS Connections	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes
Message Queues (JMS etc.)	Yes	Yes	Yes	OK	Yes	No	No	Yes
B2B Standards - EDI, ACORD	Yes	Yes	Yes	OK	Yes	No	No	Yes
SaaS App Connections (SF, NS)	Yes	Yes	No	OK	Yes	Yes	Yes	Yes
Web Services Client	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes
Publish Flows as Web Services	Yes	Yes	Yes	Yes	No	No	No	Yes
Join Multiple Sources	Yes	Yes	Yes	Yes	Yes	No	No	Yes
Split Data Streams	Yes	Yes	Yes	Yes	Yes	No	No	No
Complex Transformations	Yes	Yes	Yes	Yes	Yes	No	No	Yes
Data Validations	Yes	Yes	Yes	Yes	Yes	No	No	Yes
Preview Source Data In-Design	Yes	Yes	Yes	No	No	No	No	Yes
Run Mapping Rules In-Design	Yes	Yes	Yes	No	No	No	No	Yes
Library of New Mapping Functions	Yes	Yes	OK	No	No	No	Yes	Yes
Complex Lookups in Mappings	Yes	Yes	OK	Yes	Yes	No	No	Yes
Automatic Documentation	Yes	Yes	No	No	No	No	No	No
Human Workflow for Error Handling	Yes	Yes	Yes	OK	No	No	No	No
Workflow Task Manager	Yes	Yes	No	No	No	No	No	No
Plug-in External Programs	Yes	Yes	No	No	Yes	No	No	Yes
Alerts and Notifications	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes
Rich Logging and Tracking	Yes	Yes	Yes	Yes	OK	OK	OK	OK
Version Control	No	Yes	No	No	No	No	No	No
Deploy in Cloud Option	Yes	No	No	No	Yes	Yes	Yes	Yes
Commerical or Open Source	C	C	C	C	C	C	C	C
License Pricing	$	$$$$	$$$	$$	$$	$$$	$	$

Note:

• OK implies limited functionality or that the functionality is available thru third-parties.
• The content in this comparison matrix has been collected by reviewing publicly available information. Some of the products may have newer versions with updated features. If you have information to help us correct and update the data in this table, please send us an email at marketing@adeptia.com.
• License Pricing estimate for first year cost (basic configuration):
  

  $$$$	$150,000+
  $$$	$60,000 to $150,000
  $$	$20,000 to $60,000
  $	Less than $20,000