codekingpro/portable-devtools
115k
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!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.3 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>3 <span class="application">PL/pgSQL</span> can be used to define trigger4 functions on data changes or database events.5 A trigger function is created with the <code class="command">CREATE FUNCTION</code>6 command, declaring it as a function with no arguments and a return type of7 <code class="type">trigger</code> (for data change triggers) or8 <code class="type">event_trigger</code> (for database event triggers).9 Special local variables named <code class="varname">TG_<em class="replaceable"><code>something</code></em></code> are10 automatically defined to describe the condition that triggered the call.11 </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>12 A <a class="link" href="triggers.html" title="Chapter 39. Triggers">data change trigger</a> is declared as a13 function with no arguments and a return type of <code class="type">trigger</code>.14 Note that the function must be declared with no arguments even if it15 expects to receive some arguments specified in <code class="command">CREATE TRIGGER</code>16 — such arguments are passed via <code class="varname">TG_ARGV</code>, as described17 below.18 </p><p>19 When a <span class="application">PL/pgSQL</span> function is called as a20 trigger, several special variables are created automatically in the21 top-level block. They are:22 23 </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>24 new database row for <code class="command">INSERT</code>/<code class="command">UPDATE</code> operations in row-level25 triggers. This variable is null in statement-level triggers26 and for <code class="command">DELETE</code> operations.27 </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>28 old database row for <code class="command">UPDATE</code>/<code class="command">DELETE</code> operations in row-level29 triggers. This variable is null in statement-level triggers30 and for <code class="command">INSERT</code> operations.31 </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>32 name of the trigger which fired.33 </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>34 <code class="literal">BEFORE</code>, <code class="literal">AFTER</code>, or35 <code class="literal">INSTEAD OF</code>, depending on the trigger's definition.36 </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>37 <code class="literal">ROW</code> or <code class="literal">STATEMENT</code>,38 depending on the trigger's definition.39 </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>40 operation for which the trigger was fired:41 <code class="literal">INSERT</code>, <code class="literal">UPDATE</code>,42 <code class="literal">DELETE</code>, or <code class="literal">TRUNCATE</code>.43 </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>44 object ID of the table that caused the trigger invocation.45 </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>46 table that caused the trigger47 invocation. This is now deprecated, and could disappear in a future48 release. Use <code class="literal">TG_TABLE_NAME</code> instead.49 </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>50 table that caused the trigger invocation.51 </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>52 schema of the table that caused the trigger invocation.53 </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>54 number of arguments given to the trigger55 function in the <code class="command">CREATE TRIGGER</code> statement.56 </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>57 arguments from58 the <code class="command">CREATE TRIGGER</code> statement.59 The index counts from 0. Invalid60 indexes (less than 0 or greater than or equal to <code class="varname">tg_nargs</code>)61 result in a null value.62 </p></dd></dl></div><p>63 </p><p>64 A trigger function must return either <code class="symbol">NULL</code> or a65 record/row value having exactly the structure of the table the66 trigger was fired for.67 </p><p>68 Row-level triggers fired <code class="literal">BEFORE</code> can return null to signal the69 trigger manager to skip the rest of the operation for this row70 (i.e., subsequent triggers are not fired, and the71 <code class="command">INSERT</code>/<code class="command">UPDATE</code>/<code class="command">DELETE</code> does not occur72 for this row). If a nonnull73 value is returned then the operation proceeds with that row value.74 Returning a row value different from the original value75 of <code class="varname">NEW</code> alters the row that will be inserted or76 updated. Thus, if the trigger function wants the triggering77 action to succeed normally without altering the row78 value, <code class="varname">NEW</code> (or a value equal thereto) has to be79 returned. To alter the row to be stored, it is possible to80 replace single values directly in <code class="varname">NEW</code> and return the81 modified <code class="varname">NEW</code>, or to build a complete new record/row to82 return. In the case of a before-trigger83 on <code class="command">DELETE</code>, the returned value has no direct84 effect, but it has to be nonnull to allow the trigger action to85 proceed. Note that <code class="varname">NEW</code> is null86 in <code class="command">DELETE</code> triggers, so returning that is87 usually not sensible. The usual idiom in <code class="command">DELETE</code>88 triggers is to return <code class="varname">OLD</code>.89 </p><p>90 <code class="literal">INSTEAD OF</code> triggers (which are always row-level triggers,91 and may only be used on views) can return null to signal that they did92 not perform any updates, and that the rest of the operation for this93 row should be skipped (i.e., subsequent triggers are not fired, and the94 row is not counted in the rows-affected status for the surrounding95 <code class="command">INSERT</code>/<code class="command">UPDATE</code>/<code class="command">DELETE</code>).96 Otherwise a nonnull value should be returned, to signal97 that the trigger performed the requested operation. For98 <code class="command">INSERT</code> and <code class="command">UPDATE</code> operations, the return value99 should be <code class="varname">NEW</code>, which the trigger function may modify to100 support <code class="command">INSERT RETURNING</code> and <code class="command">UPDATE RETURNING</code>101 (this will also affect the row value passed to any subsequent triggers,102 or passed to a special <code class="varname">EXCLUDED</code> alias reference within103 an <code class="command">INSERT</code> statement with an <code class="literal">ON CONFLICT DO104 UPDATE</code> clause). For <code class="command">DELETE</code> operations, the return105 value should be <code class="varname">OLD</code>.106 </p><p>107 The return value of a row-level trigger108 fired <code class="literal">AFTER</code> or a statement-level trigger109 fired <code class="literal">BEFORE</code> or <code class="literal">AFTER</code> is110 always ignored; it might as well be null. However, any of these types of111 triggers might still abort the entire operation by raising an error.112 </p><p>113 <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 a114 trigger function in <span class="application">PL/pgSQL</span>.115 </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>116 This example trigger ensures that any time a row is inserted or updated117 in the table, the current user name and time are stamped into the118 row. And it checks that an employee's name is given and that the119 salary is a positive value.120 </p><pre class="programlisting">121CREATE TABLE emp (122 empname text,123 salary integer,124 last_date timestamp,125 last_user text126);127 128CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$129 BEGIN130 -- Check that empname and salary are given131 IF NEW.empname IS NULL THEN132 RAISE EXCEPTION 'empname cannot be null';133 END IF;134 IF NEW.salary IS NULL THEN135 RAISE EXCEPTION '% cannot have null salary', NEW.empname;136 END IF;137 138 -- Who works for us when they must pay for it?139 IF NEW.salary < 0 THEN140 RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;141 END IF;142 143 -- Remember who changed the payroll when144 NEW.last_date := current_timestamp;145 NEW.last_user := current_user;146 RETURN NEW;147 END;148$emp_stamp$ LANGUAGE plpgsql;149 150CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp151 FOR EACH ROW EXECUTE FUNCTION emp_stamp();152</pre></div></div><br class="example-break" /><p>153 Another way to log changes to a table involves creating a new table that154 holds a row for each insert, update, or delete that occurs. This approach155 can be thought of as auditing changes to a table.156 <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 an157 audit trigger function in <span class="application">PL/pgSQL</span>.158 </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>159 This example trigger ensures that any insert, update or delete of a row160 in the <code class="literal">emp</code> table is recorded (i.e., audited) in the <code class="literal">emp_audit</code> table.161 The current time and user name are stamped into the row, together with162 the type of operation performed on it.163 </p><pre class="programlisting">164CREATE TABLE emp (165 empname text NOT NULL,166 salary integer167);168 169CREATE TABLE emp_audit(170 operation char(1) NOT NULL,171 stamp timestamp NOT NULL,172 userid text NOT NULL,173 empname text NOT NULL,174 salary integer175);176 177CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$178 BEGIN179 --180 -- Create a row in emp_audit to reflect the operation performed on emp,181 -- making use of the special variable TG_OP to work out the operation.182 --183 IF (TG_OP = 'DELETE') THEN184 INSERT INTO emp_audit SELECT 'D', now(), current_user, OLD.*;185 ELSIF (TG_OP = 'UPDATE') THEN186 INSERT INTO emp_audit SELECT 'U', now(), current_user, NEW.*;187 ELSIF (TG_OP = 'INSERT') THEN188 INSERT INTO emp_audit SELECT 'I', now(), current_user, NEW.*;189 END IF;190 RETURN NULL; -- result is ignored since this is an AFTER trigger191 END;192$emp_audit$ LANGUAGE plpgsql;193 194CREATE TRIGGER emp_audit195AFTER INSERT OR UPDATE OR DELETE ON emp196 FOR EACH ROW EXECUTE FUNCTION process_emp_audit();197</pre></div></div><br class="example-break" /><p>198 A variation of the previous example uses a view joining the main table199 to the audit table, to show when each entry was last modified. This200 approach still records the full audit trail of changes to the table,201 but also presents a simplified view of the audit trail, showing just202 the last modified timestamp derived from the audit trail for each entry.203 <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 example204 of an audit trigger on a view in <span class="application">PL/pgSQL</span>.205 </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>206 This example uses a trigger on the view to make it updatable, and207 ensure that any insert, update or delete of a row in the view is208 recorded (i.e., audited) in the <code class="literal">emp_audit</code> table. The current time209 and user name are recorded, together with the type of operation210 performed, and the view displays the last modified time of each row.211 </p><pre class="programlisting">212CREATE TABLE emp (213 empname text PRIMARY KEY,214 salary integer215);216 217CREATE TABLE emp_audit(218 operation char(1) NOT NULL,219 userid text NOT NULL,220 empname text NOT NULL,221 salary integer,222 stamp timestamp NOT NULL223);224 225CREATE VIEW emp_view AS226 SELECT e.empname,227 e.salary,228 max(ea.stamp) AS last_updated229 FROM emp e230 LEFT JOIN emp_audit ea ON ea.empname = e.empname231 GROUP BY 1, 2;232 233CREATE OR REPLACE FUNCTION update_emp_view() RETURNS TRIGGER AS $$234 BEGIN235 --236 -- Perform the required operation on emp, and create a row in emp_audit237 -- to reflect the change made to emp.238 --239 IF (TG_OP = 'DELETE') THEN240 DELETE FROM emp WHERE empname = OLD.empname;241 IF NOT FOUND THEN RETURN NULL; END IF;242 243 OLD.last_updated = now();244 INSERT INTO emp_audit VALUES('D', current_user, OLD.*);245 RETURN OLD;246 ELSIF (TG_OP = 'UPDATE') THEN247 UPDATE emp SET salary = NEW.salary WHERE empname = OLD.empname;248 IF NOT FOUND THEN RETURN NULL; END IF;249 250 NEW.last_updated = now();251 INSERT INTO emp_audit VALUES('U', current_user, NEW.*);252 RETURN NEW;253 ELSIF (TG_OP = 'INSERT') THEN254 INSERT INTO emp VALUES(NEW.empname, NEW.salary);255 256 NEW.last_updated = now();257 INSERT INTO emp_audit VALUES('I', current_user, NEW.*);258 RETURN NEW;259 END IF;260 END;261$$ LANGUAGE plpgsql;262 263CREATE TRIGGER emp_audit264INSTEAD OF INSERT OR UPDATE OR DELETE ON emp_view265 FOR EACH ROW EXECUTE FUNCTION update_emp_view();266</pre></div></div><br class="example-break" /><p>267 One use of triggers is to maintain a summary table268 of another table. The resulting summary can be used in place of the269 original table for certain queries — often with vastly reduced run270 times.271 This technique is commonly used in Data Warehousing, where the tables272 of measured or observed data (called fact tables) might be extremely large.273 <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 a274 trigger function in <span class="application">PL/pgSQL</span> that maintains275 a summary table for a fact table in a data warehouse.276 </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>277 The schema detailed here is partly based on the <span class="emphasis"><em>Grocery Store278 </em></span> example from <span class="emphasis"><em>The Data Warehouse Toolkit</em></span>279 by Ralph Kimball.280 </p><pre class="programlisting">281--282-- Main tables - time dimension and sales fact.283--284CREATE TABLE time_dimension (285 time_key integer NOT NULL,286 day_of_week integer NOT NULL,287 day_of_month integer NOT NULL,288 month integer NOT NULL,289 quarter integer NOT NULL,290 year integer NOT NULL291);292CREATE UNIQUE INDEX time_dimension_key ON time_dimension(time_key);293 294CREATE TABLE sales_fact (295 time_key integer NOT NULL,296 product_key integer NOT NULL,297 store_key integer NOT NULL,298 amount_sold numeric(12,2) NOT NULL,299 units_sold integer NOT NULL,300 amount_cost numeric(12,2) NOT NULL301);302CREATE INDEX sales_fact_time ON sales_fact(time_key);303 304--305-- Summary table - sales by time.306--307CREATE TABLE sales_summary_bytime (308 time_key integer NOT NULL,309 amount_sold numeric(15,2) NOT NULL,310 units_sold numeric(12) NOT NULL,311 amount_cost numeric(15,2) NOT NULL312);313CREATE UNIQUE INDEX sales_summary_bytime_key ON sales_summary_bytime(time_key);314 315--316-- Function and trigger to amend summarized column(s) on UPDATE, INSERT, DELETE.317--318CREATE OR REPLACE FUNCTION maint_sales_summary_bytime() RETURNS TRIGGER319AS $maint_sales_summary_bytime$320 DECLARE321 delta_time_key integer;322 delta_amount_sold numeric(15,2);323 delta_units_sold numeric(12);324 delta_amount_cost numeric(15,2);325 BEGIN326 327 -- Work out the increment/decrement amount(s).328 IF (TG_OP = 'DELETE') THEN329 330 delta_time_key = OLD.time_key;331 delta_amount_sold = -1 * OLD.amount_sold;332 delta_units_sold = -1 * OLD.units_sold;333 delta_amount_cost = -1 * OLD.amount_cost;334 335 ELSIF (TG_OP = 'UPDATE') THEN336 337 -- forbid updates that change the time_key -338 -- (probably not too onerous, as DELETE + INSERT is how most339 -- changes will be made).340 IF ( OLD.time_key != NEW.time_key) THEN341 RAISE EXCEPTION 'Update of time_key : % -> % not allowed',342 OLD.time_key, NEW.time_key;343 END IF;344 345 delta_time_key = OLD.time_key;346 delta_amount_sold = NEW.amount_sold - OLD.amount_sold;347 delta_units_sold = NEW.units_sold - OLD.units_sold;348 delta_amount_cost = NEW.amount_cost - OLD.amount_cost;349 350 ELSIF (TG_OP = 'INSERT') THEN351 352 delta_time_key = NEW.time_key;353 delta_amount_sold = NEW.amount_sold;354 delta_units_sold = NEW.units_sold;355 delta_amount_cost = NEW.amount_cost;356 357 END IF;358 359 360 -- Insert or update the summary row with the new values.361 <<insert_update>>362 LOOP363 UPDATE sales_summary_bytime364 SET amount_sold = amount_sold + delta_amount_sold,365 units_sold = units_sold + delta_units_sold,366 amount_cost = amount_cost + delta_amount_cost367 WHERE time_key = delta_time_key;368 369 EXIT insert_update WHEN found;370 371 BEGIN372 INSERT INTO sales_summary_bytime (373 time_key,374 amount_sold,375 units_sold,376 amount_cost)377 VALUES (378 delta_time_key,379 delta_amount_sold,380 delta_units_sold,381 delta_amount_cost382 );383 384 EXIT insert_update;385 386 EXCEPTION387 WHEN UNIQUE_VIOLATION THEN388 -- do nothing389 END;390 END LOOP insert_update;391 392 RETURN NULL;393 394 END;395$maint_sales_summary_bytime$ LANGUAGE plpgsql;396 397CREATE TRIGGER maint_sales_summary_bytime398AFTER INSERT OR UPDATE OR DELETE ON sales_fact399 FOR EACH ROW EXECUTE FUNCTION maint_sales_summary_bytime();400 401INSERT INTO sales_fact VALUES(1,1,1,10,3,15);402INSERT INTO sales_fact VALUES(1,2,1,20,5,35);403INSERT INTO sales_fact VALUES(2,2,1,40,15,135);404INSERT INTO sales_fact VALUES(2,3,1,10,1,13);405SELECT * FROM sales_summary_bytime;406DELETE FROM sales_fact WHERE product_key = 1;407SELECT * FROM sales_summary_bytime;408UPDATE sales_fact SET units_sold = units_sold * 2;409SELECT * FROM sales_summary_bytime;410</pre></div></div><br class="example-break" /><p>411 <code class="literal">AFTER</code> triggers can also make use of <em class="firstterm">transition412 tables</em> to inspect the entire set of rows changed by the triggering413 statement. The <code class="command">CREATE TRIGGER</code> command assigns names to one414 or both transition tables, and then the function can refer to those names415 as though they were read-only temporary tables.416 <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.417 </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>418 This example produces the same results as419 <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 a420 trigger that fires for every row, it uses a trigger that fires once421 per statement, after collecting the relevant information in a transition422 table. This can be significantly faster than the row-trigger approach423 when the invoking statement has modified many rows. Notice that we must424 make a separate trigger declaration for each kind of event, since the425 <code class="literal">REFERENCING</code> clauses must be different for each case. But426 this does not stop us from using a single trigger function if we choose.427 (In practice, it might be better to use three separate functions and428 avoid the run-time tests on <code class="varname">TG_OP</code>.)429 </p><pre class="programlisting">430CREATE TABLE emp (431 empname text NOT NULL,432 salary integer433);434 435CREATE TABLE emp_audit(436 operation char(1) NOT NULL,437 stamp timestamp NOT NULL,438 userid text NOT NULL,439 empname text NOT NULL,440 salary integer441);442 443CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$444 BEGIN445 --446 -- Create rows in emp_audit to reflect the operations performed on emp,447 -- making use of the special variable TG_OP to work out the operation.448 --449 IF (TG_OP = 'DELETE') THEN450 INSERT INTO emp_audit451 SELECT 'D', now(), current_user, o.* FROM old_table o;452 ELSIF (TG_OP = 'UPDATE') THEN453 INSERT INTO emp_audit454 SELECT 'U', now(), current_user, n.* FROM new_table n;455 ELSIF (TG_OP = 'INSERT') THEN456 INSERT INTO emp_audit457 SELECT 'I', now(), current_user, n.* FROM new_table n;458 END IF;459 RETURN NULL; -- result is ignored since this is an AFTER trigger460 END;461$emp_audit$ LANGUAGE plpgsql;462 463CREATE TRIGGER emp_audit_ins464 AFTER INSERT ON emp465 REFERENCING NEW TABLE AS new_table466 FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();467CREATE TRIGGER emp_audit_upd468 AFTER UPDATE ON emp469 REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table470 FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();471CREATE TRIGGER emp_audit_del472 AFTER DELETE ON emp473 REFERENCING OLD TABLE AS old_table474 FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();475</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>476 <span class="application">PL/pgSQL</span> can be used to define477 <a class="link" href="event-triggers.html" title="Chapter 40. Event Triggers">event triggers</a>.478 <span class="productname">PostgreSQL</span> requires that a function that479 is to be called as an event trigger must be declared as a function with480 no arguments and a return type of <code class="literal">event_trigger</code>.481 </p><p>482 When a <span class="application">PL/pgSQL</span> function is called as an483 event trigger, several special variables are created automatically484 in the top-level block. They are:485 486 </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>487 event the trigger is fired for.488 </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>489 command tag for which the trigger is fired.490 </p></dd></dl></div><p>491 </p><p>492 <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 an493 event trigger function in <span class="application">PL/pgSQL</span>.494 </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>495 This example trigger simply raises a <code class="literal">NOTICE</code> message496 each time a supported command is executed.497 </p><pre class="programlisting">498CREATE OR REPLACE FUNCTION snitch() RETURNS event_trigger AS $$499BEGIN500 RAISE NOTICE 'snitch: % %', tg_event, tg_tag;501END;502$$ LANGUAGE plpgsql;503 504CREATE EVENT TRIGGER snitch ON ddl_command_start EXECUTE FUNCTION snitch();505</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.3 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>