Chapter 13. Triggers

Table of Contents
�ȥꥬ��������
�ȥꥬ���ޥ͡�����Ȥδط�
�ǡ����ѹ����βĻ���
��

Postgres has various client interfaces such as Perl, Tcl, Python and C, as well as two Procedural Languages (PL). It is also possible to call C functions as trigger actions. 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.

Postgres �� 2 �Ĥ� ��³������ �� PL �ˤ�¾��Perl��Tcl��Python�� C �Ȥ��ä��Ƽ�Υ��󥿥ե���������äƤ��ޤ����ޤ��ȥꥬ���������� ��Ȥ��� C �δؿ���Ƥ֤��Ȥ�Ǥ��ޤ������С������Ǥϡ�STATEMENT ��٥�Υȥꥬ�����٥�Ȥϥ��ݡ��Ȥ���Ƥ��ޤ���Τ����դ��Ʋ������� ���ߤϥ��ץ�� INSERT��DELETE��UPDATE������ BEFORE �ˤޤ��ϸ�� AFTER �ˤ�ȥꥬ�����٥�ȤȤ��ƻ���Ǥ��ޤ���

�ȥꥬ��������

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.

�ȥꥬ�����٥�Ȥ�ȯ������ȡ��ʥ��������塼���ˤ�äƸƤӽФ��� ��˥ȥꥬ���ޥ͡�����ϡʸ�ҤΡ���蹽¤�� TriggerData *CurrentTriggerData �������������Υ��٥�Ȥ򰷤��� �ꥬ���ؿ���ƤӽФ��ޤ���

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

�ȥꥬ����������������ˡ��ȥꥬ���ؿ��ϡ�������Ȥ�ʤ��� opaque �����֤��ؿ��Ȥ��ƺ�������Ƥ��ʤ���Фʤ�ޤ���

The syntax for creating triggers is as follows:

   CREATE TRIGGER <trigger name> <BEFORE|AFTER> <INSERT|DELETE|UPDATE>
       ON <relation name> FOR EACH <ROW|STATEMENT>
       EXECUTE PROCEDURE <procedure name> (<function args>);

�ȥꥬ�����������뤿��ι�ʸ�ϼ����̤�Ǥ���

   CREATE TRIGGER <�ȥꥬ��̾> <BEFORE|AFTER> <INSERT|DELETE|UPDATE>
       ON <��졼�����̾> FOR EACH <ROW|STATEMENT>
       EXECUTE PROCEDURE <�ץ���������̾> (<�ؿ��ΰ���>);

The name of the trigger is used if you ever have to delete the trigger. It is used as an argument to the DROP TRIGGER command.

�ȥꥬ��̾�ϡ��ȥꥬ����������ɬ�פˤʤä����ˤ�Ȥ��ޤ��� DROP TRIGGER ���ޥ�ɤΰ����Ȥ��ƻȤ��ޤ���

The next word determines whether the function is called before or after the event.

����ñ��ϡ��ؿ������٥�Ȥ�ȯ���������˸ƤФ��Τ���˸ƤФ� ��Τ�����ꤷ�ޤ���

The next element of the command determines on what event(s) will trigger the function. Multiple events can be specified separated by OR.

�������Ǥϡ��ɤΥ��ޥ�ɤΥ��٥�ȡ�ʣ���ġˤǴؿ���ƤӽФ��� ����ꤷ�ޤ���ʣ���Υ��٥�Ȥ� OR �Ƕ��ڤäƻ���Ǥ��ޤ���

The relation name determines which table the event applies to.

��졼�����̾�ϤɤΥơ��֥�˥��٥�Ȥ�Ŭ�Ѥ��뤫����ꤷ�ޤ���

The FOR EACH statement determines whether the trigger is fired for each affected row or before (or after) the entire statement has completed.

FOR EACH ʸ�ϥȥꥬ����Ԥ��ƶ���������٤�ȯ�Ԥ��뤫��ʸ���Τ� ��λ�������ʤޤ��ϸ�ˤ�ȯ�Ԥ��뤫����ꤷ�ޤ���

The procedure name is the C function called.

�ץ���������̾�ϸƤӽФ���� C �δؿ��򼨤��ޤ���

The args are 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.

�ؿ��ΰ����ϡ��ؿ��� CurrentTriggerData ��¤�Τ���˳�Ǽ���� ���Ϥ���ޤ���������ؿ����Ϥ���Ū�ϡ�Ʊ��ؿ��θƤӽФ��ˤ� �ä��׵���ͤ����Ƥ���ۤʤ�ȥꥬ���򰷤���褦�ˤ��뤳�Ȥ� ����

Also, function may be used for triggering different relations (these functions are named as "general trigger functions").

�ޤ����ؿ��ϰۤʤ��졼�����Υȥꥬ���˻Ȥ����Ȥ�Ǥ��ޤ��� �ʤ������ä��ؿ��� "���ѥȥꥬ���ؿ�" �ȸƤФ�ޤ�����

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.

�嵭��ξ��ǽ��Ȥ���Ȥ��ơ������˸��ߤΥ桼���򡢤⤦�����˸� ������Ǽ���� 2 �ĤΥե�����ɤ�̾��������Ȥ��ƤȤ����Ѵؿ��� ����ޤ����㤨�С����δؿ��� INSERT ���٥�Ȼ��˻��Ѥ���С��� ��󥶥������ơ��֥���Υ쥳���ɤκ����򸡽Ф��ơ���ưŪ�˽� �����ߤ�Ԥʤ碌�뤳�Ȥ��Ǥ��ޤ����ޤ��ϡ�UPDATE ���٥�Ȼ��˻� �Ѥ���С�"�ǽ�����" ��ǽ��Ԥʤ碌�뤳�Ȥ�Ǥ��ޤ���

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: - return NULL to skip the operation for the current tuple (and so the tuple will not be inserted/updated/deleted); - 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.

�ȥꥬ���ؿ��ϸƤӽФ������������塼���� HeapTuple ���֤��ޤ��� ����� INSERT��DELETE��UPDATE �θ��ȯ�Ԥ����ȥꥬ���� AFTER �� �Ǥ�̵�뤵��ޤ�����BEFORE �Υȥꥬ���Ǥϡ��ʲ���Ԥʤ����Ȥ��� ���ޤ��� - NULL ���֤��ơ������ץ�ؤ�����ȤФ��ޤ����ʤ����ơ����Υ� �ץ������������������ϹԤʤ��ʤ��ʤ�ޤ����� - ����Υ��ץ�ǤϤʤ�¾�Υ��ץ�Υݥ��󥿤��֤��ơ������� UPDATE �ξ��Ϲ������줿���ץ�ο������С������Ȥ��ơ����� �����ޤ����� INSERT �� UPDATE �Τߡ�

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.

CREATE TRIGGER �ϥ�ɥ�ˤ�äƽ�����ϹԤʤ��ʤ����Ȥ����� ���Ʋ����������Τ��ȤϺ����ѹ�����ͽ��Ǥ����ޤ���Ʊ���졼�� ����Ʊ�쥤�٥�Ȥ��Ф���ʣ���ȥꥬ����������줿���ϡ��ȥ� ������ȯ�Խ��ͽ¬�Ǥ��ޤ��󡣤���⺣���ѹ�����뤫�⤷��ޤ� ��

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.

�ȥꥬ���ؿ����� SPI ����Ѥ��ơ�SQL �䤤��碌����������硢�� �����䤤��碌���ȥꥬ�������ȯ�Ԥ��뤳�Ȥ�����ޤ�������ϥ��� �����ɤ��줿�ȥꥬ���ȸƤФ�ޤ������������ɤ��ʿ������Τ����¤Ϥ� ��ޤ���

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...

INSERT �ˤ�ä�ȯ�Ԥ��줿�ȥꥬ����Ʊ����졼�����˿��������ץ� �����������硢���Υȥꥬ��������ȯ�Ԥ���ޤ������������ξ��� Ʊ���ʤʤɤΡ˵�ǽ���󶡤���Ƥ��ޤ��󤬡�������ѹ�������ǽ�� ������ޤ������ʳ��Ǥϡ��쥰��å����ƥ��Ȥ���ˡ����ȤؤκƵ� �ʥ��������ǥ��󥰡ˤ���ߤ����뤿��ο��ĤΥƥ��˥å�����Ѥ��� funny_dup17() �Ȥ����ؿ�������ޤ���