Обсуждение: Trigger documentation? Need more examples.. pleeeze.. ;-)

Hello PostgreSQL friends!

I'm new to PostgreSQL, but I've been using MySQL/mSQL for a long time now.  I
switched to PostgreSQL mostly because of it's features: triggers, specially.

I've studied, compiled and tried the examples from the crontrib/spi directory.
They all worked, but without some kind of documentation it is really hard to
understand how things really work, and how I can create my own stuff.

In the "Programmer's Guide", there's a whole chapter documenting the SPI
library, but I didn't find any documentation of the functions directly related
to TRIGGERS (library "triggers.h"), like: "TRIGGER_FIRED_BY_UPDATE()",
"CurrentTriggerData->tg_newtuple", etc...

Is this documentation available somewhere?

-----

I have this table:

CREATE TABLE users (
    userid INT4 NOT NULL,
    fullname CHAR(40),
    username CHAR(20),
    password CHAR(13)
)


Using the examples from contrib/spi, I guess I can manage to create an INSERT
and DELETE trigger.  But I'm not sure how to create an UPDATE one.

Let's suppose someone updates both fields "username" and "password".  HOW from
the trigger I know which fields changed.  Is it possible to get the older
and newer values and compare them?

This UPDATE trigger doesn't need to modify it's own new inserted data, but it
has to modify external data (create/delete files at my Linux filesystem,
rename directories, etc..).

Is such thing possible?  Someone has an example of this?  ;-)


Thanks a lot!

Regards from Brazil!


--
Henrique Pantarotto
CEPAnet Internet Provider
webmaster / analista de sistemas
Email: scanner@cepa.com.br
Tel: (011) 5506-8477
Cel: (011) 9706-3444
LINUX FRIEND

On Thu, 19 Aug 1999, Henrique Pantarotto wrote:

> Hello PostgreSQL friends!
>
> I'm new to PostgreSQL, but I've been using MySQL/mSQL for a long time now.  I
> switched to PostgreSQL mostly because of it's features: triggers, specially.
>

Trigger examples ... why not:
..see a /postgresql-6.5.1/contrib/spi directory in PostgreSQL source package.

                        Zakkr

Zakkr,

I wonder if you read my message.  I am aware of the examples in the contrib/spi
directory, but they aren't enough for me.  I'm looking for a more specific
trigger documentation, or more trigger examples.

I would like to know how, from a trigger C function, can I get the "old" and
"new" value for an updated field.  Is this possible?  How do I do that?

For example, if I do:

update users set username = "clinton" where id = "2400"

When the trigger is activated, is it possible to compare the OLD value and NEW
value of the field "username"?

Did you understand?  ;-)

Thanks!

> > I'm new to PostgreSQL, but I've been using MySQL/mSQL for a long time now.
 I > > switched to PostgreSQL mostly because of it's features: triggers,
specially. > >
>
> Trigger examples ... why not:
> ..see a /postgresql-6.5.1/contrib/spi directory in PostgreSQL source package.
>
>                         Zakkr
--
Henrique Pantarotto
CEPAnet Internet Provider
webmaster / analista de sistemas
Email: scanner@cepa.com.br
Tel: (011) 5506-8477
Cel: (011) 9706-3444
LINUX FRIEND

On Fri, 20 Aug 1999, Henrique Pantarotto wrote:

> I would like to know how, from a trigger C function, can I get the "old" and
> "new" value for an updated field.  Is this possible?  How do I do that?
>
> For example, if I do:
>
> update users set username = "clinton" where id = "2400"

example:
-------
TupleDesc    tupdesc;
HeapTuple          tnew,
        told;
char        *oldvalue,
        *newvalue;

if (TRIGGER_FIRED_BY_UPDATE(CurrentTriggerData->tg_event)) {
    tnew = CurrentTriggerData->tg_newtuple;
    told = CurrentTriggerData->tg_trigtuple;
}
tupdesc = CurrentTriggerData->tg_relation->rd_att;

newvalue = SPI_getvalue(tnew, tupdesc, SPI_fnumber(tupdesc, "username"));
oldvalue = SPI_getvalue(told, tupdesc, SPI_fnumber(tupdesc, "username"));
------

NOTE:  If you run trigger AFTER|BEFORE insert in CurrentTriggerData
    is set 'tg_trigtuple' only.

> Did you understand?  ;-)

And you? :-)
                        Zakkr

Zakkr,

I love you.  It worked!!  Your help should keep me busy this weekend..
;-)

Thanks a lot..  I shall bug you again for sure.. j/k.. ;-)

Henrique Pantarotto
Sao Paulo, SP - Brazil

On sex, 20 ago 1999, Zakkr wrote:
> On Fri, 20 Aug 1999, Henrique Pantarotto wrote:
>
> > I would like to know how, from a trigger C function, can I get the "old" and
> > "new" value for an updated field.  Is this possible?  How do I do that?
> >
> > For example, if I do:
> >
> > update users set username = "clinton" where id = "2400"
>
> example:
> -------
> TupleDesc    tupdesc;
> HeapTuple          tnew,
>         told;
> char        *oldvalue,
>         *newvalue;
>
> if (TRIGGER_FIRED_BY_UPDATE(CurrentTriggerData->tg_event)) {
>     tnew = CurrentTriggerData->tg_newtuple;
>     told = CurrentTriggerData->tg_trigtuple;
> }
> tupdesc = CurrentTriggerData->tg_relation->rd_att;
>
> newvalue = SPI_getvalue(tnew, tupdesc, SPI_fnumber(tupdesc, "username"));
> oldvalue = SPI_getvalue(told, tupdesc, SPI_fnumber(tupdesc, "username"));
> ------
>
> NOTE:  If you run trigger AFTER|BEFORE insert in CurrentTriggerData
>     is set 'tg_trigtuple' only.
>
> > Did you understand?  ;-)
>
> And you? :-)
>                         Zakkr
--
Henrique Pantarotto
CEPAnet Internet Provider
webmaster / analista de sistemas
Email: scanner@cepa.com.br
Tel: (011) 5506-8477
Cel: (011) 9706-3444
LINUX FRIEND