diff options
| author | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-04-13 13:44:03 +0000 |
|---|---|---|
| committer | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-04-13 13:44:03 +0000 |
| commit | 293913568e6a7a86fd1479e1cff8e2ecb58d6568 (patch) | |
| tree | fc3b469a3ec5ab71b36ea97cc7aaddb838423a0c /doc/src/sgml/html/plpgsql-trigger.html | |
| parent | Initial commit. (diff) | |
| download | postgresql-16-293913568e6a7a86fd1479e1cff8e2ecb58d6568.tar.xz postgresql-16-293913568e6a7a86fd1479e1cff8e2ecb58d6568.zip | |
Adding upstream version 16.2.upstream/16.2
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'doc/src/sgml/html/plpgsql-trigger.html')
| -rw-r--r-- | doc/src/sgml/html/plpgsql-trigger.html | 505 |
1 files changed, 505 insertions, 0 deletions
diff --git a/doc/src/sgml/html/plpgsql-trigger.html b/doc/src/sgml/html/plpgsql-trigger.html new file mode 100644 index 0000000..6997982 --- /dev/null +++ b/doc/src/sgml/html/plpgsql-trigger.html @@ -0,0 +1,505 @@ +<?xml version="1.0" encoding="UTF-8" standalone="no"?> +<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>43.10. Trigger Functions</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="plpgsql-errors-and-messages.html" title="43.9. Errors and Messages" /><link rel="next" href="plpgsql-implementation.html" title="43.11. PL/pgSQL under the Hood" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">43.10. Trigger Functions</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="plpgsql-errors-and-messages.html" title="43.9. Errors and Messages">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="plpgsql.html" title="Chapter 43. PL/pgSQL — SQL Procedural Language">Up</a></td><th width="60%" align="center">Chapter 43. <span class="application">PL/pgSQL</span> — <acronym class="acronym">SQL</acronym> Procedural Language</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.2 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="plpgsql-implementation.html" title="43.11. PL/pgSQL under the Hood">Next</a></td></tr></table><hr /></div><div class="sect1" id="PLPGSQL-TRIGGER"><div class="titlepage"><div><div><h2 class="title" style="clear: both">43.10. Trigger Functions <a href="#PLPGSQL-TRIGGER" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="plpgsql-trigger.html#PLPGSQL-DML-TRIGGER">43.10.1. Triggers on Data Changes</a></span></dt><dt><span class="sect2"><a href="plpgsql-trigger.html#PLPGSQL-EVENT-TRIGGER">43.10.2. Triggers on Events</a></span></dt></dl></div><a id="id-1.8.8.12.2" class="indexterm"></a><p> + <span class="application">PL/pgSQL</span> can be used to define trigger + functions on data changes or database events. + A trigger function is created with the <code class="command">CREATE FUNCTION</code> + command, declaring it as a function with no arguments and a return type of + <code class="type">trigger</code> (for data change triggers) or + <code class="type">event_trigger</code> (for database event triggers). + Special local variables named <code class="varname">TG_<em class="replaceable"><code>something</code></em></code> are + automatically defined to describe the condition that triggered the call. + </p><div class="sect2" id="PLPGSQL-DML-TRIGGER"><div class="titlepage"><div><div><h3 class="title">43.10.1. Triggers on Data Changes <a href="#PLPGSQL-DML-TRIGGER" class="id_link">#</a></h3></div></div></div><p> + A <a class="link" href="triggers.html" title="Chapter 39. Triggers">data change trigger</a> is declared as a + function with no arguments and a return type of <code class="type">trigger</code>. + Note that the function must be declared with no arguments even if it + expects to receive some arguments specified in <code class="command">CREATE TRIGGER</code> + — such arguments are passed via <code class="varname">TG_ARGV</code>, as described + below. + </p><p> + When a <span class="application">PL/pgSQL</span> function is called as a + trigger, several special variables are created automatically in the + top-level block. They are: + + </p><div class="variablelist"><dl class="variablelist"><dt id="PLPGSQL-DML-TRIGGER-NEW"><span class="term"><code class="varname">NEW</code> <code class="type">record</code></span> <a href="#PLPGSQL-DML-TRIGGER-NEW" class="id_link">#</a></dt><dd><p> + new database row for <code class="command">INSERT</code>/<code class="command">UPDATE</code> operations in row-level + triggers. This variable is null in statement-level triggers + and for <code class="command">DELETE</code> operations. + </p></dd><dt id="PLPGSQL-DML-TRIGGER-OLD"><span class="term"><code class="varname">OLD</code> <code class="type">record</code></span> <a href="#PLPGSQL-DML-TRIGGER-OLD" class="id_link">#</a></dt><dd><p> + old database row for <code class="command">UPDATE</code>/<code class="command">DELETE</code> operations in row-level + triggers. This variable is null in statement-level triggers + and for <code class="command">INSERT</code> operations. + </p></dd><dt id="PLPGSQL-DML-TRIGGER-TG-NAME"><span class="term"><code class="varname">TG_NAME</code> <code class="type">name</code></span> <a href="#PLPGSQL-DML-TRIGGER-TG-NAME" class="id_link">#</a></dt><dd><p> + name of the trigger which fired. + </p></dd><dt id="PLPGSQL-DML-TRIGGER-TG-WHEN"><span class="term"><code class="varname">TG_WHEN</code> <code class="type">text</code></span> <a href="#PLPGSQL-DML-TRIGGER-TG-WHEN" class="id_link">#</a></dt><dd><p> + <code class="literal">BEFORE</code>, <code class="literal">AFTER</code>, or + <code class="literal">INSTEAD OF</code>, depending on the trigger's definition. + </p></dd><dt id="PLPGSQL-DML-TRIGGER-TG-LEVEL"><span class="term"><code class="varname">TG_LEVEL</code> <code class="type">text</code></span> <a href="#PLPGSQL-DML-TRIGGER-TG-LEVEL" class="id_link">#</a></dt><dd><p> + <code class="literal">ROW</code> or <code class="literal">STATEMENT</code>, + depending on the trigger's definition. + </p></dd><dt id="PLPGSQL-DML-TRIGGER-TG-OP"><span class="term"><code class="varname">TG_OP</code> <code class="type">text</code></span> <a href="#PLPGSQL-DML-TRIGGER-TG-OP" class="id_link">#</a></dt><dd><p> + operation for which the trigger was fired: + <code class="literal">INSERT</code>, <code class="literal">UPDATE</code>, + <code class="literal">DELETE</code>, or <code class="literal">TRUNCATE</code>. + </p></dd><dt id="PLPGSQL-DML-TRIGGER-TG-RELID"><span class="term"><code class="varname">TG_RELID</code> <code class="type">oid</code> (references <a class="link" href="catalog-pg-class.html" title="53.11. pg_class"><code class="structname">pg_class</code></a>.<code class="structfield">oid</code>)</span> <a href="#PLPGSQL-DML-TRIGGER-TG-RELID" class="id_link">#</a></dt><dd><p> + object ID of the table that caused the trigger invocation. + </p></dd><dt id="PLPGSQL-DML-TRIGGER-TG-RELNAME"><span class="term"><code class="varname">TG_RELNAME</code> <code class="type">name</code></span> <a href="#PLPGSQL-DML-TRIGGER-TG-RELNAME" class="id_link">#</a></dt><dd><p> + table that caused the trigger + invocation. This is now deprecated, and could disappear in a future + release. Use <code class="literal">TG_TABLE_NAME</code> instead. + </p></dd><dt id="PLPGSQL-DML-TRIGGER-TG-TABLE-NAME"><span class="term"><code class="varname">TG_TABLE_NAME</code> <code class="type">name</code></span> <a href="#PLPGSQL-DML-TRIGGER-TG-TABLE-NAME" class="id_link">#</a></dt><dd><p> + table that caused the trigger invocation. + </p></dd><dt id="PLPGSQL-DML-TRIGGER-TG-TABLE-SCHEMA"><span class="term"><code class="varname">TG_TABLE_SCHEMA</code> <code class="type">name</code></span> <a href="#PLPGSQL-DML-TRIGGER-TG-TABLE-SCHEMA" class="id_link">#</a></dt><dd><p> + schema of the table that caused the trigger invocation. + </p></dd><dt id="PLPGSQL-DML-TRIGGER-TG-NARGS"><span class="term"><code class="varname">TG_NARGS</code> <code class="type">integer</code></span> <a href="#PLPGSQL-DML-TRIGGER-TG-NARGS" class="id_link">#</a></dt><dd><p> + number of arguments given to the trigger + function in the <code class="command">CREATE TRIGGER</code> statement. + </p></dd><dt id="PLPGSQL-DML-TRIGGER-TG-ARGV"><span class="term"><code class="varname">TG_ARGV</code> <code class="type">text[]</code></span> <a href="#PLPGSQL-DML-TRIGGER-TG-ARGV" class="id_link">#</a></dt><dd><p> + arguments from + the <code class="command">CREATE TRIGGER</code> statement. + The index counts from 0. Invalid + indexes (less than 0 or greater than or equal to <code class="varname">tg_nargs</code>) + result in a null value. + </p></dd></dl></div><p> + </p><p> + A trigger function must return either <code class="symbol">NULL</code> or a + record/row value having exactly the structure of the table the + trigger was fired for. + </p><p> + Row-level triggers fired <code class="literal">BEFORE</code> 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 + <code class="command">INSERT</code>/<code class="command">UPDATE</code>/<code class="command">DELETE</code> 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 <code class="varname">NEW</code> 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, <code class="varname">NEW</code> (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 <code class="varname">NEW</code> and return the + modified <code class="varname">NEW</code>, or to build a complete new record/row to + return. In the case of a before-trigger + on <code class="command">DELETE</code>, the returned value has no direct + effect, but it has to be nonnull to allow the trigger action to + proceed. Note that <code class="varname">NEW</code> is null + in <code class="command">DELETE</code> triggers, so returning that is + usually not sensible. The usual idiom in <code class="command">DELETE</code> + triggers is to return <code class="varname">OLD</code>. + </p><p> + <code class="literal">INSTEAD OF</code> 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 + <code class="command">INSERT</code>/<code class="command">UPDATE</code>/<code class="command">DELETE</code>). + Otherwise a nonnull value should be returned, to signal + that the trigger performed the requested operation. For + <code class="command">INSERT</code> and <code class="command">UPDATE</code> operations, the return value + should be <code class="varname">NEW</code>, which the trigger function may modify to + support <code class="command">INSERT RETURNING</code> and <code class="command">UPDATE RETURNING</code> + (this will also affect the row value passed to any subsequent triggers, + or passed to a special <code class="varname">EXCLUDED</code> alias reference within + an <code class="command">INSERT</code> statement with an <code class="literal">ON CONFLICT DO + UPDATE</code> clause). For <code class="command">DELETE</code> operations, the return + value should be <code class="varname">OLD</code>. + </p><p> + The return value of a row-level trigger + fired <code class="literal">AFTER</code> or a statement-level trigger + fired <code class="literal">BEFORE</code> or <code class="literal">AFTER</code> 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. + </p><p> + <a class="xref" href="plpgsql-trigger.html#PLPGSQL-TRIGGER-EXAMPLE" title="Example 43.3. A PL/pgSQL Trigger Function">Example 43.3</a> shows an example of a + trigger function in <span class="application">PL/pgSQL</span>. + </p><div class="example" id="PLPGSQL-TRIGGER-EXAMPLE"><p class="title"><strong>Example 43.3. A <span class="application">PL/pgSQL</span> Trigger Function</strong></p><div class="example-contents"><p> + 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. + </p><pre class="programlisting"> +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(); +</pre></div></div><br class="example-break" /><p> + 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. + <a class="xref" href="plpgsql-trigger.html#PLPGSQL-TRIGGER-AUDIT-EXAMPLE" title="Example 43.4. A PL/pgSQL Trigger Function for Auditing">Example 43.4</a> shows an example of an + audit trigger function in <span class="application">PL/pgSQL</span>. + </p><div class="example" id="PLPGSQL-TRIGGER-AUDIT-EXAMPLE"><p class="title"><strong>Example 43.4. A <span class="application">PL/pgSQL</span> Trigger Function for Auditing</strong></p><div class="example-contents"><p> + This example trigger ensures that any insert, update or delete of a row + in the <code class="literal">emp</code> table is recorded (i.e., audited) in the <code class="literal">emp_audit</code> table. + The current time and user name are stamped into the row, together with + the type of operation performed on it. + </p><pre class="programlisting"> +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(); +</pre></div></div><br class="example-break" /><p> + 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. + <a class="xref" href="plpgsql-trigger.html#PLPGSQL-VIEW-TRIGGER-AUDIT-EXAMPLE" title="Example 43.5. A PL/pgSQL View Trigger Function for Auditing">Example 43.5</a> shows an example + of an audit trigger on a view in <span class="application">PL/pgSQL</span>. + </p><div class="example" id="PLPGSQL-VIEW-TRIGGER-AUDIT-EXAMPLE"><p class="title"><strong>Example 43.5. A <span class="application">PL/pgSQL</span> View Trigger Function for Auditing</strong></p><div class="example-contents"><p> + 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 <code class="literal">emp_audit</code> 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. + </p><pre class="programlisting"> +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(); +</pre></div></div><br class="example-break" /><p> + 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. + <a class="xref" href="plpgsql-trigger.html#PLPGSQL-TRIGGER-SUMMARY-EXAMPLE" title="Example 43.6. A PL/pgSQL Trigger Function for Maintaining a Summary Table">Example 43.6</a> shows an example of a + trigger function in <span class="application">PL/pgSQL</span> that maintains + a summary table for a fact table in a data warehouse. + </p><div class="example" id="PLPGSQL-TRIGGER-SUMMARY-EXAMPLE"><p class="title"><strong>Example 43.6. A <span class="application">PL/pgSQL</span> Trigger Function for Maintaining a Summary Table</strong></p><div class="example-contents"><p> + The schema detailed here is partly based on the <span class="emphasis"><em>Grocery Store + </em></span> example from <span class="emphasis"><em>The Data Warehouse Toolkit</em></span> + by Ralph Kimball. + </p><pre class="programlisting"> +-- +-- 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; +</pre></div></div><br class="example-break" /><p> + <code class="literal">AFTER</code> triggers can also make use of <em class="firstterm">transition + tables</em> to inspect the entire set of rows changed by the triggering + statement. The <code class="command">CREATE TRIGGER</code> 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. + <a class="xref" href="plpgsql-trigger.html#PLPGSQL-TRIGGER-AUDIT-TRANSITION-EXAMPLE" title="Example 43.7. Auditing with Transition Tables">Example 43.7</a> shows an example. + </p><div class="example" id="PLPGSQL-TRIGGER-AUDIT-TRANSITION-EXAMPLE"><p class="title"><strong>Example 43.7. Auditing with Transition Tables</strong></p><div class="example-contents"><p> + This example produces the same results as + <a class="xref" href="plpgsql-trigger.html#PLPGSQL-TRIGGER-AUDIT-EXAMPLE" title="Example 43.4. A PL/pgSQL Trigger Function for Auditing">Example 43.4</a>, 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 + <code class="literal">REFERENCING</code> 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 <code class="varname">TG_OP</code>.) + </p><pre class="programlisting"> +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(); +</pre></div></div><br class="example-break" /></div><div class="sect2" id="PLPGSQL-EVENT-TRIGGER"><div class="titlepage"><div><div><h3 class="title">43.10.2. Triggers on Events <a href="#PLPGSQL-EVENT-TRIGGER" class="id_link">#</a></h3></div></div></div><p> + <span class="application">PL/pgSQL</span> can be used to define + <a class="link" href="event-triggers.html" title="Chapter 40. Event Triggers">event triggers</a>. + <span class="productname">PostgreSQL</span> 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 <code class="literal">event_trigger</code>. + </p><p> + When a <span class="application">PL/pgSQL</span> function is called as an + event trigger, several special variables are created automatically + in the top-level block. They are: + + </p><div class="variablelist"><dl class="variablelist"><dt id="PLPGSQL-EVENT-TRIGGER-TG-EVENT"><span class="term"><code class="varname">TG_EVENT</code> <code class="type">text</code></span> <a href="#PLPGSQL-EVENT-TRIGGER-TG-EVENT" class="id_link">#</a></dt><dd><p> + event the trigger is fired for. + </p></dd><dt id="PLPGSQL-EVENT-TRIGGER-TG-TAG"><span class="term"><code class="varname">TG_TAG</code> <code class="type">text</code></span> <a href="#PLPGSQL-EVENT-TRIGGER-TG-TAG" class="id_link">#</a></dt><dd><p> + command tag for which the trigger is fired. + </p></dd></dl></div><p> + </p><p> + <a class="xref" href="plpgsql-trigger.html#PLPGSQL-EVENT-TRIGGER-EXAMPLE" title="Example 43.8. A PL/pgSQL Event Trigger Function">Example 43.8</a> shows an example of an + event trigger function in <span class="application">PL/pgSQL</span>. + </p><div class="example" id="PLPGSQL-EVENT-TRIGGER-EXAMPLE"><p class="title"><strong>Example 43.8. A <span class="application">PL/pgSQL</span> Event Trigger Function</strong></p><div class="example-contents"><p> + This example trigger simply raises a <code class="literal">NOTICE</code> message + each time a supported command is executed. + </p><pre class="programlisting"> +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(); +</pre></div></div><br class="example-break" /></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="plpgsql-errors-and-messages.html" title="43.9. Errors and Messages">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="plpgsql.html" title="Chapter 43. PL/pgSQL — SQL Procedural Language">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="plpgsql-implementation.html" title="43.11. PL/pgSQL under the Hood">Next</a></td></tr><tr><td width="40%" align="left" valign="top">43.9. Errors and Messages </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.2 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 43.11. <span class="application">PL/pgSQL</span> under the Hood</td></tr></table></div></body></html>
\ No newline at end of file |