|PostgreSQL Reference Manual - Volume 2 - Programming Guide|
by The PostgreSQL Global Development Group
Paperback (6"x9"), 408 pages
RRP £19.95 ($34.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
9.10 Trigger Procedures
PL/pgSQL can be used to define trigger
procedures. A trigger procedure is created with the
CREATE FUNCTION command, declaring it as a function with
no arguments and a return type of
trigger. Note that
the function must be declared with no arguments even if it expects
to receive arguments specified in
CREATE TRIGGER---trigger arguments are passed via
TG_ARGV, as described
When a PL/pgSQL function is called as a trigger, several special variables are created automatically in the top-level block. They are:
RECORD; variable holding the new database row for
UPDATEoperations in row-level triggers. This variable is
NULLin statement-level triggers.
RECORD; variable holding the old database row for
DELETEoperations in row-level triggers. This variable is
NULLin statement-level triggers.
name; variable that contains the name of the trigger actually fired.
text; a string of either
AFTERdepending on the trigger's definition.
text; a string of either
STATEMENTdepending on the trigger's definition.
text; a string of
DELETEtelling for which operation the trigger was fired.
oid; the object ID of the table that caused the trigger invocation.
name; the name of the table that caused the trigger invocation. This is now deprecated, and could disappear in a future release. Use
name; the name of the table that caused the trigger invocation.
name; the name of the schema of the table that caused the trigger invocation.
integer; the number of arguments given to the trigger procedure in the
Data type array of
text; the arguments from the
CREATE TRIGGERstatement. The index counts from 0. Invalid indices (less than 0 or greater than or equal to
tg_nargs) result in a null value.
A trigger function must return either
NULL or a
record/row value having exactly the structure of the table the
trigger was fired for.
Row-level triggers fired
BEFORE may return null to signal the
trigger manager to skip the rest of the operation for this row
(i.e., subsequent triggers are not fired, and the
DELETE does not occur
for this row). If a nonnull
value is returned then the operation proceeds with that row value.
Returning a row value different from the original value
NEW alters the row that will be inserted or updated
(but has no direct effect in the
To alter the row to be stored, it is possible to replace single values
NEW and return the modified
or to build a complete new record/row to return.
The return value of a
statement-level trigger or an
AFTER row-level trigger is
always ignored; it may as well be null. However, any of these types of
triggers can still abort the entire operation by raising an error.
section 9.10 Trigger Procedures shows an example of a trigger procedure in PL/pgSQL.
A PL/pgSQL Trigger Procedure:
This example trigger ensures that any time a row is inserted or updated in the table, the current user name and time are stamped into the row. And it checks that an employee's name is given and that the salary is a positive value.
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();
Another way to log changes to a table involves creating a new table that holds a row for each insert, update, or delete that occurs. This approach can be thought of as auditing changes to a table. section 9.10 Trigger Procedures shows an example of an audit trigger procedure in PL/pgSQL.
A PL/pgSQL Trigger Procedure For Auditing:
This example trigger ensures that any insert, update or delete of a row
emp table is recorded (i.e., audited) in the
The current time and user name are stamped into the row, together with
the type of operation performed on it.
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 -- -- Create a row in emp_audit to reflect the -- operation performed on emp, make use of the -- special variable TG_OP to work out the -- operation. -- 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();
One use of triggers is to maintain a summary table of another table. The resulting summary can be used in place of the original table for certain queries--often with vastly reduced run times. This technique is commonly used in Data Warehousing, where the tables of measured or observed data (called fact tables) can be extremely large. section 9.10 Trigger Procedures shows an example of a trigger procedure in PL/pgSQL that maintains a summary table for a fact table in a data warehouse.
A PL/pgSQL Trigger Procedure For Maintaining A Summary Table:
The schema detailed here is partly based on the Grocery Store example from The Data Warehouse Toolkit by Ralph Kimball.
-- -- Main tables - time dimension and 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); -- -- Summary table - 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); -- -- Function and trigger to amend summarized column(s) on 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 -- Work out the increment/decrement amount(s). 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; -- Insert or update the summary row with the new values. <<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 -- do nothing 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;
|ISBN 0954612035||PostgreSQL Reference Manual - Volume 2 - Programming Guide||See the print edition|