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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

Raphael Bauduin <raphael.bauduin (AT) be (DOT) easynet.net> writes:
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/s...l-trigger.html

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

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

Raph


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

On Wed, Aug 11, 2004 at 09:44:42AM +0200, Raphael Bauduin wrote:
Yes.

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

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

http://www.postgresql.org/docs/7.4/s...l-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/

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Michael Fuhr wrote:
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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

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



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html