PL/pgSQL 埇傖䫘庯垔幬蓥埏単誺䘋㔗婔婻蓥埏単誺䘋滇䫘 CREATE FUNCTION 变傴录傺䔇录傺䔇嘵嚟滇婔婻婉毖埖埗昄幽婫誫啂 trigger 䌂傋䔇庘昄㔗臙濘懟臖庘昄剿嘪婘 CREATE TRIGGER 弄滯麯弄滯婺庖崺毖埖埗昄垄幘媙驔弄滯婺方埗昄啹婺蓥埏単䔇埗昄滇锔誺 TG_ARGV 嚹锐䔇(婋麵橬柟誄)㔗
婘婔婻 PL/pgSQL 庘昄嘷啔蓥埏単脄䫘䔇施唍係䂘嚔婘釽北䔇弄滯枕麯躻媘录傺庹婻䬹枪埻麟㔗橬套婋認底
昄扞䌂傋滇 RECORD 臖埻麟婺臯亓蓥埏単婺䔇 INSERT/UPDATE 淉嘩庻嗘桄昄扞臯㔗婘臺埖亓彆䔇蓥埏単麯認婻埻麟滇 NULL 㔗
昄扞䌂傋滇 RECORD 臖埻麟婺臯亓蓥埏単婺䔇 UPDATE/DELETE 淉嘩庻嗘斓昄扞臯㔗婘臺埖亓彆䔇蓥埏単麯認婻埻麟滇 NULL 㔗
昄扞䌂傋滇 name 臖埻麟寙劆垂鍙蓥埏䔇蓥埏単劉㔗
昄扞䌂傋滇 text 滇婔婻䫌蓥埏単垔幬喿垔䔇庖严婾(BEFORE 潡 AFTER)㔗
昄扞䌂傋滇 text 滇婔婻䫌蓥埏単垔幬喿垔䔇庖严婾(ROW 潡 STATEMENT)㔗
昄扞䌂傋滇 text滇婔婻臘滯檔昂蓥埏単䔇淉嘩䔇庖严婾(INSERT, UPDATE, DELETE)㔗
昄扞䌂傋滇 oid 滇檔昂蓥埏単脄䫘䔇臘䔇凹茇湺臖(OID)㔗
昄扞䌂傋滇 name 滇檔昂蓥埏単脄䫘䔇臘䔇劉䓄㔗埉凹嘪䫘幽嚔婘儖準䔇䬽橸婺潽崌毘艊嘪䫘 TG_TABLE_NAME 㔗
昄扞䌂傋滇 name 滇檔昂蓥埏単脄䫘䔇臘䔇劉䓄㔗
昄扞䌂傋滇 name 滇檔昂蓥埏単脄䫘䔇臘䔇昇嚟㔗
昄扞䌂傋滇 integer 滇婘 CREATE TRIGGER 臺埖麯麵蕋庽蓥埏単誺䘋䔇埗昄䔇婻昄㔗
昄扞䌂傋滇 text 䔇昄䂇滇 CREATE TRIGGER 臺埖麯䔇埗昄㔗婋湺傯 0 嚔哋螄昄㔗麂濘婋湺(償庯 0 潡蔙崓庯京庯 tg_nargs)凚躘誫啂婔婻 NULL 唚㔗
婔婻蓥埏単庘昄媙釂誫啂 NULL 潡蔙滇婔婻婯檔昂蓥埏単誊臯䔇臘䔇螄嘘/臯䂷悇垯噘䕩劯䔇昄扞㔗
啹 BEFORE 蓥埏䔇臯亓彆蓥埏単埇傖誫啂婔婻 NULL 只臬蓥埏単䞇䊖単媘䘖凹臖臯嬷婋䔇淉嘩幘儌滇臘锟劯䔇蓥埏単儖婉喉欓臯幽婫婉嚔凹臖臯库䫘 INSERT/UPDATE/DELETE 媘嘩)㔗套悩誫啂庖婔婻麂 NULL 䔇臯闼幽儖䂓䂺凹臖臯昄唚誕臯崇䊖㔗臙濘懟誫啂婔婻启寘準䔇 NEW 婉劯䔇臯昄唚儖媞櫹闼婻儖某噖潡敘桄䔇臯(婉誺婘 DELETE 䔇愙喕婋方昽)㔗埇傖䫘婔婻唚䕘毖傼敪 NEW 麯䔇昊婻昄唚幽婫誫啂幋潡蔙幘埇傖悇傺婔婻噘桄䔇螄嘘/臯喉誫啂㔗
BEFORE 潡 AFTER 臺埖亓彆䔇蓥埏単潡蔙婔婻 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;