PostgreSQL
42.10. Trigger Functions
PL/pgSQL can be used to define trigger functions on data changes or database events. A trigger function is created with the CREATE FUNCTION
command, declaring it as a function with no arguments and a return type of trigger
(for data change triggers) or event_trigger
(for database event triggers). Special local variables named TG_`
something` are automatically defined to describe the condition that triggered the call.
42.10.1. Triggers on Data Changes
A data change trigger is declared 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 some arguments specified in CREATE TRIGGER
— such arguments are passed via TG_ARGV
, as described below.
When a PL/pgSQL function is called as a trigger, several special variables are created automatically in the top-level block. They are:
NEW
-
Data type
RECORD
; variable holding the new database row forINSERT
/UPDATE
operations in row-level triggers. This variable is null in statement-level triggers and forDELETE
operations. OLD
-
Data type
RECORD
; variable holding the old database row forUPDATE
/DELETE
operations in row-level triggers. This variable is null in statement-level triggers and forINSERT
operations. TG_NAME
-
Data type
name
; variable that contains the name of the trigger actually fired. TG_WHEN
-
Data type
text
; a string ofBEFORE
,AFTER
, orINSTEAD OF
, depending on the trigger’s definition. TG_LEVEL
-
Data type
text
; a string of eitherROW
orSTATEMENT
depending on the trigger’s definition. TG_OP
-
Data type
text
; a string ofINSERT
,UPDATE
,DELETE
, orTRUNCATE
telling for which operation the trigger was fired. TG_RELID
-
Data type
oid
; the object ID of the table that caused the trigger invocation. TG_RELNAME
-
Data type
name
; the name of the table that caused the trigger invocation. This is now deprecated, and could disappear in a future release. UseTG_TABLE_NAME
instead. TG_TABLE_NAME
-
Data type
name
; the name of the table that caused the trigger invocation. TG_TABLE_SCHEMA
-
Data type
name
; the name of the schema of the table that caused the trigger invocation. TG_NARGS
-
Data type
integer
; the number of arguments given to the trigger function in theCREATE TRIGGER
statement. TG_ARGV[]
-
Data type array of
text
; the arguments from theCREATE TRIGGER
statement. The index counts from 0. Invalid indexes (less than 0 or greater than or equal totg_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
can 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 INSERT
/UPDATE
/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 of NEW
alters the row that will be inserted or updated. Thus, if the trigger function wants the triggering action to succeed normally without altering the row value, NEW
(or a value equal thereto) has to be returned. To alter the row to be stored, it is possible to replace single values directly in NEW
and return the modified NEW
, or to build a complete new record/row to return. In the case of a before-trigger on DELETE
, the returned value has no direct effect, but it has to be nonnull to allow the trigger action to proceed. Note that NEW
is null in DELETE
triggers, so returning that is usually not sensible. The usual idiom in DELETE
triggers is to return OLD
.
INSTEAD OF
triggers (which are always row-level triggers, and may only be used on views) can return null to signal that they did not perform any updates, and that the rest of the operation for this row should be skipped (i.e., subsequent triggers are not fired, and the row is not counted in the rows-affected status for the surrounding INSERT
/UPDATE
/DELETE
). Otherwise a nonnull value should be returned, to signal that the trigger performed the requested operation. For INSERT
and UPDATE
operations, the return value should be NEW
, which the trigger function may modify to support INSERT RETURNING
and UPDATE RETURNING
(this will also affect the row value passed to any subsequent triggers, or passed to a special EXCLUDED
alias reference within an INSERT
statement with an ON CONFLICT DO UPDATE
clause). For DELETE
operations, the return value should be OLD
.
The return value of a row-level trigger fired AFTER
or a statement-level trigger fired BEFORE
or AFTER
is always ignored; it might as well be null. However, any of these types of triggers might still abort the entire operation by raising an error.
Example 42.3 shows an example of a trigger function in PL/pgSQL.
Example 42.3. A PL/pgSQL Trigger Function
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 they 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 FUNCTION 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. Example 42.4 shows an example of an audit trigger function in PL/pgSQL.
Example 42.4. A PL/pgSQL Trigger Function for Auditing
This example trigger ensures that any insert, update or delete of a row in the emp
table is recorded (i.e., audited) in the emp_audit
table. 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,
-- making use of the special variable TG_OP to work out the operation.
--
IF (TG_OP = 'DELETE') THEN
INSERT INTO emp_audit SELECT 'D', now(), current_user, OLD.*;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO emp_audit SELECT 'U', now(), current_user, NEW.*;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO emp_audit SELECT 'I', now(), current_user, 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 FUNCTION process_emp_audit();
+
A variation of the previous example uses a view joining the main table to the audit table, to show when each entry was last modified. This approach still records the full audit trail of changes to the table, but also presents a simplified view of the audit trail, showing just the last modified timestamp derived from the audit trail for each entry. Example 42.5 shows an example of an audit trigger on a view in PL/pgSQL.
Example 42.5. A PL/pgSQL View Trigger Function for Auditing
This example uses a trigger on the view to make it updatable, and ensure that any insert, update or delete of a row in the view is recorded (i.e., audited) in the emp_audit
table. The current time and user name are recorded, together with the type of operation performed, and the view displays the last modified time of each row.
CREATE TABLE emp (
empname text PRIMARY KEY,
salary integer
);
CREATE TABLE emp_audit(
operation char(1) NOT NULL,
userid text NOT NULL,
empname text NOT NULL,
salary integer,
stamp timestamp NOT NULL
);
CREATE VIEW emp_view AS
SELECT e.empname,
e.salary,
max(ea.stamp) AS last_updated
FROM emp e
LEFT JOIN emp_audit ea ON ea.empname = e.empname
GROUP BY 1, 2;
CREATE OR REPLACE FUNCTION update_emp_view() RETURNS TRIGGER AS $$
BEGIN
--
-- Perform the required operation on emp, and create a row in emp_audit
-- to reflect the change made to emp.
--
IF (TG_OP = 'DELETE') THEN
DELETE FROM emp WHERE empname = OLD.empname;
IF NOT FOUND THEN RETURN NULL; END IF;
OLD.last_updated = now();
INSERT INTO emp_audit VALUES('D', current_user, OLD.*);
RETURN OLD;
ELSIF (TG_OP = 'UPDATE') THEN
UPDATE emp SET salary = NEW.salary WHERE empname = OLD.empname;
IF NOT FOUND THEN RETURN NULL; END IF;
NEW.last_updated = now();
INSERT INTO emp_audit VALUES('U', current_user, NEW.*);
RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO emp VALUES(NEW.empname, NEW.salary);
NEW.last_updated = now();
INSERT INTO emp_audit VALUES('I', current_user, NEW.*);
RETURN NEW;
END IF;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER emp_audit
INSTEAD OF INSERT OR UPDATE OR DELETE ON emp_view
FOR EACH ROW EXECUTE FUNCTION update_emp_view();
+
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) might be extremely large. Example 42.6 shows an example of a trigger function in PL/pgSQL that maintains a summary table for a fact table in a data warehouse.
Example 42.6. A PL/pgSQL Trigger Function 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 FUNCTION 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;
+
AFTER
triggers can also make use of transition tables to inspect the entire set of rows changed by the triggering statement. The CREATE TRIGGER
command assigns names to one or both transition tables, and then the function can refer to those names as though they were read-only temporary tables. Example 42.7 shows an example.
Example 42.7. Auditing with Transition Tables
This example produces the same results as Example 42.4, but instead of using a trigger that fires for every row, it uses a trigger that fires once per statement, after collecting the relevant information in a transition table. This can be significantly faster than the row-trigger approach when the invoking statement has modified many rows. Notice that we must make a separate trigger declaration for each kind of event, since the REFERENCING
clauses must be different for each case. But this does not stop us from using a single trigger function if we choose. (In practice, it might be better to use three separate functions and avoid the run-time tests on TG_OP
.)
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 rows in emp_audit to reflect the operations performed on emp,
-- making use of the special variable TG_OP to work out the operation.
--
IF (TG_OP = 'DELETE') THEN
INSERT INTO emp_audit
SELECT 'D', now(), current_user, o.* FROM old_table o;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO emp_audit
SELECT 'U', now(), current_user, n.* FROM new_table n;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO emp_audit
SELECT 'I', now(), current_user, n.* FROM new_table n;
END IF;
RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
$emp_audit$ LANGUAGE plpgsql;
CREATE TRIGGER emp_audit_ins
AFTER INSERT ON emp
REFERENCING NEW TABLE AS new_table
FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();
CREATE TRIGGER emp_audit_upd
AFTER UPDATE ON emp
REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();
CREATE TRIGGER emp_audit_del
AFTER DELETE ON emp
REFERENCING OLD TABLE AS old_table
FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();
+
42.10.2. Triggers on Events
PL/pgSQL can be used to define event triggers. PostgreSQL requires that a function that is to be called as an event trigger must be declared as a function with no arguments and a return type of event_trigger
.
When a PL/pgSQL function is called as an event trigger, several special variables are created automatically in the top-level block. They are:
TG_EVENT
-
Data type
text
; a string representing the event the trigger is fired for. TG_TAG
-
Data type
text
; variable that contains the command tag for which the trigger is fired.
Example 42.8 shows an example of an event trigger function in PL/pgSQL.
Example 42.8. A PL/pgSQL Event Trigger Function
This example trigger simply raises a NOTICE
message each time a supported command is executed.
CREATE OR REPLACE FUNCTION snitch() RETURNS event_trigger AS $$
BEGIN
RAISE NOTICE 'snitch: % %', tg_event, tg_tag;
END;
$$ LANGUAGE plpgsql;
CREATE EVENT TRIGGER snitch ON ddl_command_start EXECUTE FUNCTION snitch();
+
Prev | Up | Next |
---|---|---|
42.9. Errors and Messages |
42.11. PL/pgSQL under the Hood |
Submit correction
If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.
Copyright © 1996-2023 The PostgreSQL Global Development Group