PostgreSQL 8.2.3 婺桺桺懼
劯锔媆锔䆹37. PL/pgSQL - SQL 誺䘋臺蘔媆誕嬉誕

37.10. 蓥埏単誺䘋

PL/pgSQL 埇傖䫘庯垔幬蓥埏単誺䘋㔗婔婻蓥埏単誺䘋滇䫘 CREATE FUNCTION 变傴录傺䔇录傺䔇嘵嚟滇婔婻婉毖埖埗昄幽婫誫啂 trigger 䌂傋䔇庘昄㔗臙濘懟臖庘昄剿嘪婘 CREATE TRIGGER 弄滯麯弄滯婺庖崺毖埖埗昄垄幘媙驔弄滯婺方埗昄啹婺蓥埏単䔇埗昄滇锔誺 TG_ARGV 嚹锐䔇(婋麵橬柟誄)㔗

婘婔婻 PL/pgSQL 庘昄嘷啔蓥埏単脄䫘䔇施唍係䂘嚔婘釽北䔇弄滯枕麯躻媘录傺庹婻䬹枪埻麟㔗橬套婋認底

NEW

昄扞䌂傋滇 RECORD 臖埻麟婺臯亓蓥埏単婺䔇 INSERT/UPDATE 淉嘩庻嗘桄昄扞臯㔗婘臺埖亓彆䔇蓥埏単麯認婻埻麟滇 NULL

OLD

昄扞䌂傋滇 RECORD 臖埻麟婺臯亓蓥埏単婺䔇 UPDATE/DELETE 淉嘩庻嗘斓昄扞臯㔗婘臺埖亓彆䔇蓥埏単麯認婻埻麟滇 NULL

TG_NAME

昄扞䌂傋滇 name 臖埻麟寙劆垂鍙蓥埏䔇蓥埏単劉㔗

TG_WHEN

昄扞䌂傋滇 text 滇婔婻䫌蓥埏単垔幬喿垔䔇庖严婾(BEFOREAFTER)㔗

TG_LEVEL

昄扞䌂傋滇 text 滇婔婻䫌蓥埏単垔幬喿垔䔇庖严婾(ROWSTATEMENT)㔗

TG_OP

昄扞䌂傋滇 text滇婔婻臘滯檔昂蓥埏単䔇淉嘩䔇庖严婾(INSERT, UPDATE, DELETE)㔗

TG_RELID

昄扞䌂傋滇 oid 滇檔昂蓥埏単脄䫘䔇臘䔇凹茇湺臖(OID)㔗

TG_RELNAME

昄扞䌂傋滇 name 滇檔昂蓥埏単脄䫘䔇臘䔇劉䓄㔗埉凹嘪䫘幽嚔婘儖準䔇䬽橸婺潽崌毘艊嘪䫘 TG_TABLE_NAME

TG_TABLE_NAME

昄扞䌂傋滇 name 滇檔昂蓥埏単脄䫘䔇臘䔇劉䓄㔗

TG_TABLE_SCHEMA

昄扞䌂傋滇 name 滇檔昂蓥埏単脄䫘䔇臘䔇昇嚟㔗

TG_NARGS

昄扞䌂傋滇 integer 滇婘 CREATE TRIGGER 臺埖麯麵蕋庽蓥埏単誺䘋䔇埗昄䔇婻昄㔗

TG_ARGV[]

昄扞䌂傋滇 text 䔇昄䂇滇 CREATE TRIGGER 臺埖麯䔇埗昄㔗婋湺傯 0 嚔哋螄昄㔗麂濘婋湺(償庯 0 潡蔙崓庯京庯 tg_nargs)凚躘誫啂婔婻 NULL 唚㔗

婔婻蓥埏単庘昄媙釂誫啂 NULL 潡蔙滇婔婻婯檔昂蓥埏単誊臯䔇臘䔇螄嘘/臯䂷悇垯噘䕩劯䔇昄扞㔗

BEFORE 蓥埏䔇臯亓彆蓥埏単埇傖誫啂婔婻 NULL 只臬蓥埏単䞇䊖単媘䘖凹臖臯嬷婋䔇淉嘩幘儌滇臘锟劯䔇蓥埏単儖婉喉欓臯幽婫婉嚔凹臖臯库䫘 INSERT/UPDATE/DELETE 媘嘩)㔗套悩誫啂庖婔婻麂 NULL 䔇臯闼幽儖䂓䂺凹臖臯昄唚誕臯崇䊖㔗臙濘懟誫啂婔婻启寘準䔇 NEW 婉劯䔇臯昄唚儖媞櫹闼婻儖某噖潡敘桄䔇臯(婉誺婘 DELETE 䔇愙喕婋方昽)㔗埇傖䫘婔婻唚䕘毖傼敪 NEW 麯䔇昊婻昄唚幽婫誫啂幋潡蔙幘埇傖悇傺婔婻噘桄䔇螄嘘/臯喉誫啂㔗

BEFOREAFTER 臺埖亓彆䔇蓥埏単潡蔙婔婻 AFTER 臯亓彆䔇蓥埏単䔇誫啂唚儖攂滇赆媘䘖垄傸幘埇傖誫啂 NULL 準媘䘖誫啂唚㔗婉誺傂嘘認䓉䌂傋䔇蓥埏単傉䇽埇傖锔誺檕庺婔婻髍臇準锔庺昘婻蓥埏単淉嘩㔗

冋37-2滆䴺庖婔婻 PL/pgSQL 喍䔇蓥埏単誺䘋䔇冋床㔗

冋37-2. 婔婻 PL/pgSQL 蓥埏単誺䘋

婋麵䔇䴺冋蓥埏単䔇嘩䫘滇傂嘘施唍臘婺某噖潡敘桄庖臯嘷嬉䔇䫘潙劉启施閘鄘螄嘘噖臯婺㔗幽婫垄媺臕䂍庺庖镺叻劉䓄幽婫衻愘滇婔婻溼昄㔗

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

CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$
    BEGIN
        -- 演昖滇劥䂍庺庖 empname 启 salary
        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;

        -- 媙釂傻婊䂍脕?
        IF NEW.salary < 0 THEN
            RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
        END IF;

        -- 螄嘟嘘施嘘庺䔇衻愘赆媞櫹庖
        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();

埥崡婔婻劏臘麯螄嘘埻寡䔇桹濘潬埪录傺婔婻桄臘䇽劯婺劯準埏䫘䔇懟渇某噖㔕敘桄潡蔙役鍴媘嘩媺庻婔臯㔗認婻桹濘埇傖嘷嘩凹婔婻臘䔇垇螇㔗冋37-3滆䴺庖婔婻 PL/pgSQL 喍䔇垇螇蓥埏単誺䘋䔇冋床㔗

冋37-3. 婔婻䫘庯垇螇䔇 PL/pgSQL 蓥埏単誺䘋

認婻冋床蓥埏単媺臕庖婘 emp 臘婪䔇傂嘘某噖㔕敘桄㔕役鍴媘嘩鄘赆螄嘘彄庖 emp_audit 臘麯(幘儌滇垇螇)㔗嘷嬉施閘启䫘潙劉嚔赆螄嘘彄昄扞臯麯傖埪誻橬欓臯䔇淉嘩㔗

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
        --
        -- 婘 emp_audit 麯录傺婔臯埉滹凹 emp 䔇淉嘩
        -- 嘪䫘䬹枪埻麟 TG_OP 诙埡淉嘩䌂傋㔗
        --
        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; -- result is ignored since this is an AFTER trigger
    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();

蓥埏単䔇婔婻䫘锫滇䂘毕埥崡婔婻臘䔇楗襕㔗䫘潊䔇楗襕埇傖䫘庯婘昊底昖臵婺傼敪寘哋臘(锔婩埇傖崓崓䚷償誊臯施閘)㔗認婻檔噓䂟婩䫘庯昄扞傷康認婻施唍驔襕敋麟䔇臘(埆庋垂臘)埇脘嚔麂婩噘崓㔗冋37-4暫䴺庖婔婻 PL/pgSQL 蓥埏単誺䘋䔇冋床垄婺昊婻昄扞傷康䔇婔婻庋垂臘䂘檴婔婻楗襕臘㔗

冋37-4.婔婻䂘檴楗襕臘䔇 PL/pgSQL 蓥埏単誺䘋

婋麵䔇昇嚟橬婔鄘彖滇嘺庯 The Data Warehouse Toolkit 麯麵䔇 Grocery Store 冋床㔗

--
-- 婂臘-施閘䂘傖埪體嫞庋垂㔗
--
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);

--
-- 揻襕臘-湹扞施閘䔇體嫞㔗
--
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);

--
-- 婘 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

        -- 螇䞖嵂/废麟㔗
        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

            -- 䥕溵櫹埻 time_key 䔇敘桄(埇脘幽婉滇冽嚺彽啹婺 DELETE + INSERT 滇崓崔昄埇脘库䫘䔇媞櫹)㔗
            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;


        -- 䫘桄昄唚某噖潡敘桄楗襕臯㔗
        <<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
                    -- 傔幽幘婉啔
            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;

劯锔饡釕嬉誕
髍臇启潽敇婪婔亓傯 Oracle PL/SQL 誕臯䓂洉