This page in other versions: 9.0 / 9.1 / 9.2 / 9.3  |  Development versions: devel / 9.4  |  Unsupported versions: 7.4 / 8.0 / 8.1 / 8.2 / 8.3 / 8.4

33.4. A Complete Example

Here is a very simple example of a trigger function written in C. (Examples of triggers written in procedural languages may be found in the documentation of the procedural languages.)

The function trigf reports the number of rows in the table ttest and skips the actual operation if the command attempts to insert a null value into the column x. (So the trigger acts as a not-null constraint but doesn't abort the transaction.)

First, the table definition:

CREATE TABLE ttest (
    x integer
);

This is the source code of the trigger function:

#include "postgres.h"
#include "executor/spi.h"       /* this is what you need to work with SPI */
#include "commands/trigger.h"   /* ... and triggers */

extern Datum trigf(PG_FUNCTION_ARGS);

PG_FUNCTION_INFO_V1(trigf);

Datum
trigf(PG_FUNCTION_ARGS)
{
    TriggerData *trigdata = (TriggerData *) fcinfo->context;
    TupleDesc   tupdesc;
    HeapTuple   rettuple;
    char       *when;
    bool        checknull = false;
    bool        isnull;
    int         ret, i;

    /* make sure it's called as a trigger at all */
    if (!CALLED_AS_TRIGGER(fcinfo))
        elog(ERROR, "trigf: not called by trigger manager");

    /* tuple to return to executor */
    if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
        rettuple = trigdata->tg_newtuple;
    else
        rettuple = trigdata->tg_trigtuple;

    /* check for null values */
    if (!TRIGGER_FIRED_BY_DELETE(trigdata->tg_event)
        && TRIGGER_FIRED_BEFORE(trigdata->tg_event))
        checknull = true;

    if (TRIGGER_FIRED_BEFORE(trigdata->tg_event))
        when = "before";
    else
        when = "after ";

    tupdesc = trigdata->tg_relation->rd_att;

    /* connect to SPI manager */
    if ((ret = SPI_connect()) < 0)
        elog(INFO, "trigf (fired %s): SPI_connect returned %d", when, ret);

    /* get number of rows in table */
    ret = SPI_exec("SELECT count(*) FROM ttest", 0);

    if (ret < 0)
        elog(NOTICE, "trigf (fired %s): SPI_exec returned %d", when, ret);

    /* count(*) returns int8, so be careful to convert */
    i = DatumGetInt64(SPI_getbinval(SPI_tuptable->vals[0],
                                    SPI_tuptable->tupdesc,
                                    1,
                                    &isnull));

    elog (INFO, "trigf (fired %s): there are %d rows in ttest", when, i);

    SPI_finish();

    if (checknull)
    {
        SPI_getbinval(rettuple, tupdesc, 1, &isnull);
        if (isnull)
            rettuple = NULL;
    }

    return PointerGetDatum(rettuple);
}

After you have compiled the source code, declare the function and the triggers:

CREATE FUNCTION trigf() RETURNS trigger
    AS 'filename'
    LANGUAGE C;

CREATE TRIGGER tbefore BEFORE INSERT OR UPDATE OR DELETE ON ttest 
    FOR EACH ROW EXECUTE PROCEDURE trigf();

CREATE TRIGGER tafter AFTER INSERT OR UPDATE OR DELETE ON ttest 
    FOR EACH ROW EXECUTE PROCEDURE trigf();

Now you can test the operation of the trigger:

=> INSERT INTO ttest VALUES (NULL);
INFO:  trigf (fired before): there are 0 rows in ttest
INSERT 0 0

-- Insertion skipped and AFTER trigger is not fired

=> SELECT * FROM ttest;
 x
---
(0 rows)

=> INSERT INTO ttest VALUES (1);
INFO:  trigf (fired before): there are 0 rows in ttest
INFO:  trigf (fired after ): there are 1 rows in ttest
                                       ^^^^^^^^
                             remember what we said about visibility.
INSERT 167793 1
vac=> SELECT * FROM ttest;
 x
---
 1
(1 row)

=> INSERT INTO ttest SELECT x * 2 FROM ttest;
INFO:  trigf (fired before): there are 1 rows in ttest
INFO:  trigf (fired after ): there are 2 rows in ttest
                                       ^^^^^^
                             remember what we said about visibility.
INSERT 167794 1
=> SELECT * FROM ttest;
 x
---
 1
 2
(2 rows)

=> UPDATE ttest SET x = NULL WHERE x = 2;
INFO:  trigf (fired before): there are 2 rows in ttest
UPDATE 0
=> UPDATE ttest SET x = 4 WHERE x = 2;
INFO:  trigf (fired before): there are 2 rows in ttest
INFO:  trigf (fired after ): there are 2 rows in ttest
UPDATE 1
vac=> SELECT * FROM ttest;
 x
---
 1
 4
(2 rows)

=> DELETE FROM ttest;
INFO:  trigf (fired before): there are 2 rows in ttest
INFO:  trigf (fired before): there are 1 rows in ttest
INFO:  trigf (fired after ): there are 0 rows in ttest
INFO:  trigf (fired after ): there are 0 rows in ttest
                                       ^^^^^^
                             remember what we said about visibility.
DELETE 2
=> SELECT * FROM ttest;
 x
---
(0 rows)

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

Comments


Dec. 15, 2005, 8:26 p.m.

This is my simple example of how to use trigger in my shopping cart application.

I have two simple tables here:
- product: product_id, quantity, create_date, last_update
- shopping: shopping_id, product_id, create_date, last_update

Here are my business rule
- I want to have create_date = 'now' everytime I added product into shopping table.
- I want to update last_update = 'now' when update each rows.
- I want to reduce quantity from product table when I added product to my shopping cart (shopping table) and increase my product quantity when I removed product from shopping cart.

So here are trigger function and trigger:

1. Trigger function

CREATE OR REPLACE FUNCTION shopping_update()
RETURNS "trigger" AS
'
BEGIN
IF (TG_OP = ''INSERT'') THEN

NEW.create_date = ''now'';
NEW.last_update = ''now'';
UPDATE product SET quantity = quantity - 1
WHERE product_id = NEW.product_id;

RETURN NEW;

ELSIF (TG_OP = ''DELETE'') THEN

UPDATE product SET quantity = quantity + 1
WHERE product_id = OLD.product_id;
RETURN OLD;

ELSIF (TG_OP = ''UPDATE'') THEN

NEW.last_update = ''now'';
RETURN NEW;

END IF;

END;
'
LANGUAGE 'plpgsql' VOLATILE;

2. Trigger

CREATE TRIGGER shopping_trigger
BEFORE INSERT OR UPDATE OR DELETE
ON shopping
FOR EACH ROW
EXECUTE PROCEDURE shopping_update();

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group