Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes15kdownloads
plpgsql-trigger.html505 linesDownload Raw Back to html
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 &lt; 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 : % -&gt; % 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        &lt;&lt;insert_update&gt;&gt;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>
codekingpro/portable-devtools · Team Ai