@@ -4013,7 +4013,7 @@ CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
40134013 BEGIN
40144014 --
40154015 -- Create a row in emp_audit to reflect the operation performed on emp,
4016- --make use of the special variable TG_OP to work out the operation.
4016+ --making use of the special variable TG_OP to work out the operation.
40174017 --
40184018 IF (TG_OP = 'DELETE') THEN
40194019 INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*;
@@ -4265,6 +4265,82 @@ UPDATE sales_fact SET units_sold = units_sold * 2;
42654265SELECT * FROM sales_summary_bytime;
42664266</programlisting>
42674267 </example>
4268+
4269+ <para>
4270+ <literal>AFTER</> triggers can also make use of <firstterm>transition
4271+ tables</> to inspect the entire set of rows changed by the triggering
4272+ statement. The <command>CREATE TRIGGER</> command assigns names to one
4273+ or both transition tables, and then the function can refer to those names
4274+ as though they were read-only temporary tables.
4275+ <xref linkend="plpgsql-trigger-audit-transition-example"> shows an example.
4276+ </para>
4277+
4278+ <example id="plpgsql-trigger-audit-transition-example">
4279+ <title>Auditing with Transition Tables</title>
4280+
4281+ <para>
4282+ This example produces the same results as
4283+ <xref linkend="plpgsql-trigger-audit-example">, but instead of using a
4284+ trigger that fires for every row, it uses a trigger that fires once
4285+ per statement, after collecting the relevant information in a transition
4286+ table. This can be significantly faster than the row-trigger approach
4287+ when the invoking statement has modified many rows. Notice that we must
4288+ make a separate trigger declaration for each kind of event, since the
4289+ <literal>REFERENCING</> clauses must be different for each case. But
4290+ this does not stop us from using a single trigger function if we choose.
4291+ (In practice, it might be better to use three separate functions and
4292+ avoid the run-time tests on <varname>TG_OP</>.)
4293+ </para>
4294+
4295+ <programlisting>
4296+ CREATE TABLE emp (
4297+ empname text NOT NULL,
4298+ salary integer
4299+ );
4300+
4301+ CREATE TABLE emp_audit(
4302+ operation char(1) NOT NULL,
4303+ stamp timestamp NOT NULL,
4304+ userid text NOT NULL,
4305+ empname text NOT NULL,
4306+ salary integer
4307+ );
4308+
4309+ CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
4310+ BEGIN
4311+ --
4312+ -- Create rows in emp_audit to reflect the operations performed on emp,
4313+ -- making use of the special variable TG_OP to work out the operation.
4314+ --
4315+ IF (TG_OP = 'DELETE') THEN
4316+ INSERT INTO emp_audit
4317+ SELECT 'D', now(), user, o.* FROM old_table o;
4318+ ELSIF (TG_OP = 'UPDATE') THEN
4319+ INSERT INTO emp_audit
4320+ SELECT 'U', now(), user, n.* FROM new_table n;
4321+ ELSIF (TG_OP = 'INSERT') THEN
4322+ INSERT INTO emp_audit
4323+ SELECT 'I', now(), user, n.* FROM new_table n;
4324+ END IF;
4325+ RETURN NULL; -- result is ignored since this is an AFTER trigger
4326+ END;
4327+ $emp_audit$ LANGUAGE plpgsql;
4328+
4329+ CREATE TRIGGER emp_audit_ins
4330+ AFTER INSERT ON emp
4331+ REFERENCING NEW TABLE AS new_table
4332+ FOR EACH STATEMENT EXECUTE PROCEDURE process_emp_audit();
4333+ CREATE TRIGGER emp_audit_upd
4334+ AFTER UPDATE ON emp
4335+ REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
4336+ FOR EACH STATEMENT EXECUTE PROCEDURE process_emp_audit();
4337+ CREATE TRIGGER emp_audit_del
4338+ AFTER DELETE ON emp
4339+ REFERENCING OLD TABLE AS old_table
4340+ FOR EACH STATEMENT EXECUTE PROCEDURE process_emp_audit();
4341+ </programlisting>
4342+ </example>
4343+
42684344</sect2>
42694345
42704346 <sect2 id="plpgsql-event-trigger">