trigger.sgml 15.6 KB
Newer Older
1 2 3 4
 <chapter id="triggers">
  <title>Triggers</title>

  <para>
Bruce Momjian's avatar
Bruce Momjian committed
5 6 7
   <productname>Postgres</productname> has various server-side function
   interfaces. Server-side functions can be written in SQL, PLPGSQL,
   TCL, or C. Trigger functions can be written in any of these
Bruce Momjian's avatar
Bruce Momjian committed
8 9 10
   languages except SQL. Note that STATEMENT-level trigger events are not
   supported in the current version. You can currently specify BEFORE or
   AFTER on INSERT, DELETE or UPDATE of a tuple as a trigger event.
11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
  </para>

  <sect1>
   <title>Trigger Creation</title>

   <para>
    If a trigger event occurs, the trigger manager (called by the Executor)
    initializes the global structure TriggerData *CurrentTriggerData (described
    below) and calls the trigger function to handle the event.
   </para>

   <para>
    The trigger function must be created before the trigger is created as a
    function taking no arguments and returns opaque.
   </para>

   <para>
    The syntax for creating triggers is as follows:

    <programlisting>
CREATE TRIGGER <replaceable>trigger</replaceable> [ BEFORE | AFTER ] [ INSERT | DELETE | UPDATE [ OR ... ] ]
    ON <replaceable>relation</replaceable> FOR EACH [ ROW | STATEMENT ]
    EXECUTE PROCEDURE <replaceable>procedure</replaceable>
     (<replaceable>args</replaceable>);
    </programlisting>

    where the arguments are:

    <variablelist>
     <varlistentry>
      <term>
       <replaceable>trigger</replaceable>
      </term>
      <listitem>
       <para>
	The name of the trigger is
	used if you ever have to delete the trigger.
	It is used as an argument to the <command>DROP TRIGGER</command> command.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>BEFORE</term>
      <term>AFTER</term>
      <listitem>
       <para>
	Determines whether the function is called before or after
	the event.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>INSERT</term>
      <term>DELETE</term>
      <term>UPDATE</term>
      <listitem>
       <para>
	The next element of the command determines on what event(s) will trigger
	the function.  Multiple events can be specified separated by OR.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable>relation</replaceable></term>
      <listitem>
       <para>
	The relation name determines which table the event applies to.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>ROW</term>
      <term>STATEMENT</term>
      <listitem>
       <para>
	The FOR EACH clause determines whether the trigger is fired for each
	affected row or before (or after) the entire statement has completed.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable>procedure</replaceable></term>
      <listitem>
       <para>
Bruce Momjian's avatar
Bruce Momjian committed
100
	The procedure name is the function called.
101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable>args</replaceable></term>
      <listitem>
       <para>
	The arguments passed to the function in the CurrentTriggerData structure.
	The purpose of passing arguments to the function is to allow different
	triggers with similar requirements to call the same function.
       </para>

       <para>
	Also, <replaceable>procedure</replaceable>
	may be used for triggering different relations (these
	functions are named as "general trigger functions").
       </para>

       <para>
	As example of using both features above, there could be a general
	function that takes as its arguments two field names and puts the current
	user in one and the current timestamp in the other. This allows triggers to
	be written on INSERT events to automatically track creation of records in a
	transaction table for example. It could also be used as a "last updated"
	function if used in an UPDATE event.
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
   </para>

   <para>
    Trigger functions return HeapTuple to the calling Executor.  This
    is ignored for triggers fired after an INSERT, DELETE or UPDATE operation
    but it allows BEFORE triggers to:

    <itemizedlist>
     <listitem>
      <para>
       Return NULL to skip the operation for the current tuple (and so the
       tuple will not be inserted/updated/deleted).
      </para>
     </listitem>

     <listitem>
      <para>
       Return a pointer to another tuple (INSERT and UPDATE only) which will
       be inserted (as the new version of the updated tuple if UPDATE) instead
       of original tuple.
      </para>
     </listitem>
    </itemizedlist>
   </para>

   <para>
    Note that there is no initialization performed by the CREATE TRIGGER
    handler.  This will be changed in the future.  Also, if more than one trigger
    is defined for the same event on the same relation, the order of trigger
    firing is unpredictable. This may be changed in the future.
   </para>

   <para>
    If a trigger function executes SQL-queries (using SPI) then these queries
    may fire triggers again. This is known as cascading triggers.  There is no
    explicit limitation on the number of cascade levels.
   </para>

   <para>
    If a trigger is fired by INSERT and inserts a new tuple in the same
    relation then this trigger will be fired again.  Currently, there is nothing
    provided for synchronization (etc) of these cases but this may change.  At
    the moment, there is function funny_dup17() in the regress tests which uses
    some techniques to stop recursion (cascading) on itself...
   </para>
  </sect1>

  <sect1>
   <title>Interaction with the Trigger Manager</title>

   <para>
    As mentioned above, when function is called by the trigger manager,
    structure TriggerData *CurrentTriggerData is NOT NULL and initialized.  So
    it is better to check CurrentTriggerData against being NULL at the start
    and set it to NULL just after fetching the information to prevent calls to
    a trigger function not from the trigger manager.
   </para>

   <para>
    struct TriggerData is defined in src/include/commands/trigger.h:

    <programlisting>
193 194
typedef struct TriggerData
{
195 196 197 198 199
    TriggerEvent  tg_event;
    Relation      tg_relation;
    HeapTuple     tg_trigtuple;
    HeapTuple     tg_newtuple;
    Trigger      *tg_trigger;
200
} TriggerData;
201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328
    </programlisting>

    where the members are defined as follows:

    <variablelist>
     <varlistentry>
      <term>tg_event</term>
      <listitem>
       <para>
	describes the event for which the function is called. You may use the
	following macros to examine <literal>tg_event</literal>:

	<variablelist>
	 <varlistentry>
	  <term>TRIGGER_FIRED_BEFORE(tg_event)</term>
	  <listitem>
	   <para>
	    returns TRUE if trigger fired BEFORE.
	   </para>
	  </listitem>
	 </varlistentry>

	 <varlistentry>
	  <term>TRIGGER_FIRED_AFTER(tg_event)</term>
	  <listitem>
	   <para>
	    Returns TRUE if trigger fired AFTER.
	   </para>
	  </listitem>
	 </varlistentry>

	 <varlistentry>
	  <term>TRIGGER_FIRED_FOR_ROW(event)</term>
	  <listitem>
	   <para>
	    Returns TRUE if trigger fired for
	    a ROW-level event.
	   </para>
	  </listitem>
	 </varlistentry>

	 <varlistentry>
	  <term>TRIGGER_FIRED_FOR_STATEMENT(event)</term>
	  <listitem>
	   <para>
	    Returns TRUE if trigger fired for
	    STATEMENT-level event.
	   </para>
	  </listitem>
	 </varlistentry>

	 <varlistentry>
	  <term>TRIGGER_FIRED_BY_INSERT(event)</term>
	  <listitem>
	   <para>
	    Returns TRUE if trigger fired by INSERT.
	   </para>
	  </listitem>
	 </varlistentry>

	 <varlistentry>
	  <term>TRIGGER_FIRED_BY_DELETE(event)</term>
	  <listitem>
	   <para>
	    Returns TRUE if trigger fired by DELETE.
	   </para>
	  </listitem>
	 </varlistentry>

	 <varlistentry>
	  <term>TRIGGER_FIRED_BY_UPDATE(event)</term>
	  <listitem>
	   <para>
	    Returns TRUE if trigger fired by UPDATE.
	   </para>
	  </listitem>
	 </varlistentry>
	</variablelist>
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>tg_relation</term>
      <listitem>
       <para>
	is a pointer to structure describing the triggered relation. Look at
	src/include/utils/rel.h for details about this structure.  The most
	interest things are tg_relation->rd_att (descriptor of the relation
	tuples) and tg_relation->rd_rel->relname (relation's name. This is not
	char*, but NameData.  Use SPI_getrelname(tg_relation) to get char* if
	you need a copy of name).
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>tg_trigtuple</term>
      <listitem>
       <para>
	is a pointer to the tuple for which the trigger is fired. This is the tuple
	being inserted (if INSERT), deleted (if DELETE) or updated (if UPDATE).
	If INSERT/DELETE then this is what you are to return to Executor if 
	you don't want to replace tuple with another one (INSERT) or skip the
	operation.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>tg_newtuple</term>
      <listitem>
       <para>
	is a pointer to the new version of tuple if UPDATE and NULL if this is
	for an INSERT or a DELETE. This is what you are to return to Executor if
	UPDATE and you don't want to replace this tuple with another one or skip
	the operation.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>tg_trigger</term>
      <listitem>
       <para>
	is pointer to structure Trigger defined in src/include/utils/rel.h:

	<programlisting>
329 330
typedef struct Trigger
{
Tom Lane's avatar
Tom Lane committed
331 332 333 334 335 336 337 338 339 340 341 342
    Oid         tgoid;
    char       *tgname;
    Oid         tgfoid;
    FmgrInfo    tgfunc;
    int16       tgtype;
    bool        tgenabled;
    bool        tgisconstraint;
    bool        tgdeferrable;
    bool        tginitdeferred;
    int16       tgnargs;
    int16       tgattr[FUNC_MAX_ARGS];
    char      **tgargs;
343
} Trigger;
344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409
	</programlisting>

	where
	tgname is the trigger's name, tgnargs is number of arguments in tgargs,
	tgargs is an array of pointers to the arguments specified in the CREATE
	TRIGGER statement. Other members are for internal use only.
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
   </para>
  </sect1>

  <sect1>
   <title>Visibility of Data Changes</title>

   <para>
    <productname>Postgres</productname> data changes visibility rule: during a query execution, data
    changes made by the query itself (via SQL-function, SPI-function, triggers)
    are invisible to the query scan.  For example, in query

    <programlisting>
INSERT INTO a SELECT * FROM a;
    </programlisting>

    tuples inserted are invisible for SELECT scan.  In effect, this
    duplicates the database table within itself (subject to unique index
    rules, of course) without recursing.
   </para>

   <para>
    But keep in mind this notice about visibility in the SPI documentation:

    <blockquote>
     <para>
Changes made by query Q are visible by queries which are started after
query Q, no matter whether they are started inside Q (during the
execution of Q) or after Q is done.
     </para>
    </blockquote>
   </para>

   <para>
    This is true for triggers as well so, though a tuple being inserted
    (tg_trigtuple) is not visible to queries in a BEFORE trigger, this tuple
    (just inserted) is visible to queries in an AFTER trigger, and to queries
    in BEFORE/AFTER triggers fired after this!
   </para>
  </sect1>

  <sect1>
   <title>Examples</title>

   <para>
    There are more complex examples in
    <filename>src/test/regress/regress.c</filename> and
    in <filename>contrib/spi</filename>.
   </para>

   <para>
    Here is a very simple example of trigger usage.  Function trigf reports
    the number of tuples in the triggered relation ttest and skips the
    operation if the query attempts to insert NULL into x (i.e - it acts as a
    NOT NULL constraint but doesn't abort the transaction).

    <programlisting>
410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471
#include "executor/spi.h"	/* this is what you need to work with SPI */
#include "commands/trigger.h"	/* -"- and triggers */

HeapTuple		trigf(void);

HeapTuple
trigf()
{
	TupleDesc	tupdesc;
	HeapTuple	rettuple;
	char		*when;
	bool		checknull = false;
	bool		isnull;
	int		ret, i;

	if (!CurrentTriggerData)
		elog(WARN, "trigf: triggers are not initialized");
	
	/* tuple to return to Executor */
	if (TRIGGER_FIRED_BY_UPDATE(CurrentTriggerData->tg_event))
		rettuple = CurrentTriggerData->tg_newtuple;
	else
		rettuple = CurrentTriggerData->tg_trigtuple;
	
	/* check for NULLs ? */
	if (!TRIGGER_FIRED_BY_DELETE(CurrentTriggerData->tg_event) &&
		TRIGGER_FIRED_BEFORE(CurrentTriggerData->tg_event))
		checknull = true;
	
	if (TRIGGER_FIRED_BEFORE(CurrentTriggerData->tg_event))
		when = "before";
	else
		when = "after ";
	
	tupdesc = CurrentTriggerData->tg_relation->rd_att;
	CurrentTriggerData = NULL;
	
	/* Connect to SPI manager */
	if ((ret = SPI_connect()) < 0)
		elog(WARN, "trigf (fired %s): SPI_connect returned %d", when, ret);
	
	/* Get number of tuples in relation */
	ret = SPI_exec("select count(*) from ttest", 0);
	
	if (ret < 0)
		elog(WARN, "trigf (fired %s): SPI_exec returned %d", when, ret);
	
	i = SPI_getbinval(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1, &amp;isnull);
	
	elog (NOTICE, "trigf (fired %s): there are %d tuples in ttest", when, i);
	
	SPI_finish();
	
	if (checknull)
	{
		i = SPI_getbinval(rettuple, tupdesc, 1, &amp;isnull);
		if (isnull)
			rettuple = NULL;
	}

	return (rettuple);
}
472 473
    </programlisting>
   </para>
474

475 476 477 478 479
   <para>
    Now, compile and 
    create table ttest (x int4):

    <programlisting>
480 481
create function trigf () returns opaque as 
'...path_to_so' language 'c';
482
    </programlisting>
483

484
    <programlisting>
485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552
vac=> create trigger tbefore before insert or update or delete on ttest 
for each row execute procedure trigf();
CREATE
vac=> create trigger tafter after insert or update or delete on ttest 
for each row execute procedure trigf();
CREATE
vac=> insert into ttest values (null);
NOTICE:trigf (fired before): there are 0 tuples in ttest
INSERT 0 0

-- Insertion skipped and AFTER trigger is not fired

vac=> select * from ttest;
x
-
(0 rows)

vac=> insert into ttest values (1);
NOTICE:trigf (fired before): there are 0 tuples in ttest
NOTICE:trigf (fired after ): there are 1 tuples in ttest
                                       ^^^^^^^^
                             remember what we said about visibility.
INSERT 167793 1
vac=> select * from ttest;
x
-
1
(1 row)

vac=> insert into ttest select x * 2 from ttest;
NOTICE:trigf (fired before): there are 1 tuples in ttest
NOTICE:trigf (fired after ): there are 2 tuples in ttest
                                       ^^^^^^^^
                             remember what we said about visibility.
INSERT 167794 1
vac=> select * from ttest;
x
-
1
2
(2 rows)

vac=> update ttest set x = null where x = 2;
NOTICE:trigf (fired before): there are 2 tuples in ttest
UPDATE 0
vac=> update ttest set x = 4 where x = 2;
NOTICE:trigf (fired before): there are 2 tuples in ttest
NOTICE:trigf (fired after ): there are 2 tuples in ttest
UPDATE 1
vac=> select * from ttest;
x
-
1
4
(2 rows)

vac=> delete from ttest;
NOTICE:trigf (fired before): there are 2 tuples in ttest
NOTICE:trigf (fired after ): there are 1 tuples in ttest
NOTICE:trigf (fired before): there are 1 tuples in ttest
NOTICE:trigf (fired after ): there are 0 tuples in ttest
                                       ^^^^^^^^
                             remember what we said about visibility.
DELETE 2
vac=> select * from ttest;
x
-
(0 rows)
553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574
    </programlisting>

   </para>
  </sect1>
 </chapter>

<!-- Keep this comment at the end of the file
Local variables:
mode:sgml
sgml-omittag:nil
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"./reference.ced"
sgml-exposed-tags:nil
sgml-local-catalogs:("/usr/lib/sgml/catalog")
sgml-local-ecat-files:nil
End:
-->