Unsupported versions: 6.3
This documentation is for an unsupported version of PostgreSQL.
You may want to view the same page for the current version, or one of the other supported versions listed above instead.
PostgreSQL
Prev Chapter 38. Triggers Next

Examples

There are more complex examples in in src/test/regress/regress.c and in contrib/spi.

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

#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, &isnull);
        
        elog (NOTICE, "trigf (fired %s): there are %d tuples in ttest", when, i);
        
        SPI_finish();
        
        if (checknull)
        {
                i = SPI_getbinval(rettuple, tupdesc, 1, &isnull);
                if (isnull)
                        rettuple = NULL;
        }

        return (rettuple);
}

Now, compile and create table ttest (x int4); create function trigf () returns opaque as '...path_to_so' language 'c';

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)

Prev Home Next
Visibility of Data Changes Up Server Programming Interface