Thread: table name firing trigger

Hi,

Does a function executed by a trigger know which table fired the trigger?
I'm using the same function for several triggers on different tables.
Now, I'm passing the table name as argument:
CREATE TRIGGER "customers_update_log_t" after UPDATE on "customers" for each row execute procedure
"customers_update_log"('customers');

I wondered if in the function code, we have access to the table name that fired the trigger.
Also, does the function have access to the type of action that fired the trigger?
Eg, if I create a trigger after update and insert, is it possible to know if it's an insert or an update that fired the
trigger?

thanks.

Raph

Raphael Bauduin <raphael.bauduin@be.easynet.net> writes:
> Does a function executed by a trigger know which table fired the trigger?

Depends what language you're writing the trigger in, but I believe most
of them do.  For plpgsql see
http://www.postgresql.org/docs/7.4/static/plpgsql-trigger.html

            regards, tom lane

On Wed, Aug 11, 2004 at 09:44:42AM +0200, Raphael Bauduin wrote:
> Does a function executed by a trigger know which table fired the trigger?

Yes.

> I'm using the same function for several triggers on different tables.
> Now, I'm passing the table name as argument:
> CREATE TRIGGER "customers_update_log_t" after UPDATE on "customers" for
> each row execute procedure "customers_update_log"('customers');
>
> I wondered if in the function code, we have access to the table name that
> fired the trigger.

You don't say what language you're using, but in PL/pgSQL you can
refer to the table name as TG_RELNAME.

> Also, does the function have access to the type of action that fired the
> trigger?
> Eg, if I create a trigger after update and insert, is it possible to know
> if it's an insert or an update that fired the trigger?

That would be TG_OP.  See the PL/pgSQL "Trigger Procedures" manual page
for more info:

http://www.postgresql.org/docs/7.4/static/plpgsql-trigger.html

If you're using a language other than PL/pgSQL then see that
language's trigger documentation.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Tom Lane wrote:
> Raphael Bauduin <raphael.bauduin@be.easynet.net> writes:
>
>>Does a function executed by a trigger know which table fired the trigger?
>
>
> Depends what language you're writing the trigger in, but I believe most
> of them do.  For plpgsql see
> http://www.postgresql.org/docs/7.4/static/plpgsql-trigger.html
>

Thanks! I don't understand how I managed to overlook that page as I'm using plpgsql.....

Raph

>             regards, tom lane

Michael Fuhr wrote:
> On Wed, Aug 11, 2004 at 09:44:42AM +0200, Raphael Bauduin wrote:
snip
>
>>Also, does the function have access to the type of action that fired the
>>trigger?
>>Eg, if I create a trigger after update and insert, is it possible to know
>>if it's an insert or an update that fired the trigger?
>
>
> That would be TG_OP.  See the PL/pgSQL "Trigger Procedures" manual page
> for more info:
>
> http://www.postgresql.org/docs/7.4/static/plpgsql-trigger.html
>
> If you're using a language other than PL/pgSQL then see that
> language's trigger documentation.
>
I'm trying to use it in a plpgsql trigger. I want to insert a row in a table named
TG_RELNAME_log so for example customers_log.
I had problems with escaping a string in the query I wanted to perform:

insert into TG_RELNAME_log select new.*,'UPDATE';

I tried it that way:


CREATE FUNCTION "activity_log"() RETURNS trigger AS '
 BEGIN
        RAISE NOTICE ''table = %'',TG_RELNAME;
        insert into TG_RELNAME||''_log''|| select new.*,''UPDATE'';
        return new;
 END;
 ' LANGUAGE 'plpgsql';

but when I do an update, I get this output:
NOTICE:  table = customers
ERROR:  syntax error at or near "$1" at character 14
CONTEXT:  PL/pgSQL function "activity_log" line 3 at SQL statement



With this version;
CREATE FUNCTION "activity_log"() RETURNS trigger AS '
 BEGIN
        RAISE NOTICE ''table = %'',TG_RELNAME;
        insert into TG_RELNAME_log select new.*,''UPDATE'';
        return new;
 END;
 ' LANGUAGE 'plpgsql';
I get this output:
NOTICE:  table = customers
ERROR:  NEW used in query that is not in a rule
CONTEXT:  PL/pgSQL function "activity_log" line 3 at SQL statement


How can I execute a questy in a function that has to insert a string?

Further on, I tried to use TG_OP rather than inserting the 'UPDATE' string, but I also
have problems using new.* (it has to be out of the string, like TG_RELNAME, but I still gets
a problem):

CREATE FUNCTION "activity_log"() RETURNS trigger AS '
 BEGIN
        RAISE NOTICE ''table = %'',TG_RELNAME;
        RAISE NOTICE ''operation = %'',TG_OP;
        EXECUTE ''insert into ''|| TG_RELNAME||''_log select ''||new.*||'',''||TG_OP;
        return new;
 END;
 ' LANGUAGE 'plpgsql';

but I get this:
NOTICE:  table = customers
NOTICE:  operation = UPDATE
ERROR:  NEW used in query that is not in a rule
CONTEXT:  PL/pgSQL function "activity_log" line 4 at execute statement

An example found at http://dev.e-taller.net/dbtree/fractal/02-triggers.sql seems to indicate this should be possible.

I hope I didn't miss anything obvious this time.....
Thanks.

Raph

1... Has the data type extension ARRAY been depreciated in Version 8.0?

2... What methodology or concept should be used in a multi-user
     application to avoid or minimize a fatel-imbrace conflict?

--Hal.

===========================================================
Hal Davison(Herbie)          Internet Petroleum  Gateway
Davison Consulting           Caldera 2.2.14  eServer
6850 Myakka Valley Tr     PostgreSQL 7.3.1 - SUN Forte JAVA
Sarasota, Florida 34241         Phone: (941) 921-6578
http://faamsnet.com             eFAX: (419) 821-5999
===========================================================