pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTriggers
34+ messages / 25 participants
[nested] [flat]
* Triggers
@ 2000-01-04 15:04 Mitch Vincent <mitch@venux.net>
0 siblings, 3 replies; 34+ messages in thread
From: Mitch Vincent @ 2000-01-04 15:04 UTC (permalink / raw)
To: pgsql-sql
I'm using this funtion via a trigger to total up amounts in several fields
(and adding that value into a total field)..
CREATE FUNCTION invoice_total_trigger() RETURNS OPAQUE AS '
BEGIN
NEW.total := NEW.fee_membership + NEW.fee_logins + NEW.fee_convention +
NEW.fee_prints+ NEW.fee_hotlines + NEW.fee_postage + NEW.fee_ups +
NEW.fee_late + NEW.fee_other1 + NEW.fee_other2 + NEW.fee_other3 +
NEW.fee_pastdue;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
That's the function, here is the trigger.
CREATE TRIGGER invoice_total_trigger AFTER INSERT OR UPDATE ON invoice
FOR EACH ROW EXECUTE PROCEDURE invoice_total_trigger();
It works for updating but not when you insert a new row. Am I doing it
wrong?
I need the invoice_total_trigger to run every time a row is updated or
inserted..
Thanks guys!
-Mitch
^ permalink raw reply [nested|flat] 34+ messages in thread
* Re: [SQL] Triggers
@ 2000-01-04 15:55 Tom Lane <tgl@sss.pgh.pa.us>
parent: Mitch Vincent <mitch@venux.net>
2 siblings, 1 reply; 34+ messages in thread
From: Tom Lane @ 2000-01-04 15:55 UTC (permalink / raw)
To: Mitch Vincent <mitch@venux.net>; +Cc: pgsql-sql
"Mitch Vincent" <mitch@venux.net> writes:
> I'm using this funtion via a trigger to total up amounts in several fields
> (and adding that value into a total field)..
> CREATE TRIGGER invoice_total_trigger AFTER INSERT OR UPDATE ON invoice
> FOR EACH ROW EXECUTE PROCEDURE invoice_total_trigger();
> It works for updating but not when you insert a new row. Am I doing it
> wrong?
Er, don't you want BEFORE INSERT OR UPDATE?
I wouldn't think an AFTER trigger could modify the already-inserted-or-
updated row...
regards, tom lane
^ permalink raw reply [nested|flat] 34+ messages in thread
* Re: [SQL] Triggers
@ 2000-01-04 16:21 Mitch Vincent <mitch@venux.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 34+ messages in thread
From: Mitch Vincent @ 2000-01-04 16:21 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Thanks for the reply Tom (as always)..
I did try both AFTER and BEFORE, neither worked for the insert.. Can you see
anything wrong with the syntax?
I get no errors or anything in the postgres.log, I have debuging set fairly
high too :
StartTransactionCommand
query: insert into invoice
(invoice_number,agencycode,invoice_date,fee_membership,fee_logins,fee_conven
tion,fee_prints,fee_hotlines,fee_postage,fee_ups,fee_late)
values(17623,'PAHO-231','12-01-1999',0.000000,0.000000,5.000000,0.000000,0.0
00000,0.000000,95.000000,0.000000);
ProcessQuery
CommitTransactionCommand
There is an example of one of the inserts, there are 312 done at a time, all
look just like that. It doesn't look like that the trigger is ever getting
called.. Would you agree?
-Mitch
----- Original Message -----
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Mitch Vincent <mitch@venux.net>
Cc: <pgsql-sql@postgresql.org>
Sent: Tuesday, January 04, 2000 10:55 AM
Subject: Re: [SQL] Triggers
> "Mitch Vincent" <mitch@venux.net> writes:
> > I'm using this funtion via a trigger to total up amounts in several
fields
> > (and adding that value into a total field)..
>
> > CREATE TRIGGER invoice_total_trigger AFTER INSERT OR UPDATE ON invoice
> > FOR EACH ROW EXECUTE PROCEDURE invoice_total_trigger();
>
> > It works for updating but not when you insert a new row. Am I doing it
> > wrong?
>
> Er, don't you want BEFORE INSERT OR UPDATE?
>
> I wouldn't think an AFTER trigger could modify the already-inserted-or-
> updated row...
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 34+ messages in thread
* Re: [SQL] Triggers
@ 2000-01-04 18:42 Jan Wieck <wieck@debis.com>
parent: Mitch Vincent <mitch@venux.net>
2 siblings, 0 replies; 34+ messages in thread
From: Jan Wieck @ 2000-01-04 18:42 UTC (permalink / raw)
To: Mitch Vincent <mitch@venux.net>; +Cc: pgsql-sql
Mitch Vincent wrote:
> I'm using this funtion via a trigger to total up amounts in several fields
> (and adding that value into a total field)..
>
> CREATE FUNCTION invoice_total_trigger() RETURNS OPAQUE AS '
> ...
>
> That's the function, here is the trigger.
>
> CREATE TRIGGER invoice_total_trigger AFTER INSERT OR UPDATE ON invoice
> FOR EACH ROW EXECUTE PROCEDURE invoice_total_trigger();
>
> It works for updating but not when you insert a new row. Am I doing it
> wrong?
I wonder why it works on UPDATE at all, it shouldn't. It is an
AFTER ROW trigger and thus, the changed row you return should get
ignored. Use BEFORE INSERT instead.
But that it works on UPDATE indicates some bug somewhere. What
release, OS, etc. are you using?
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck@debis.com (Jan Wieck) #
^ permalink raw reply [nested|flat] 34+ messages in thread
* Re: [SQL] Triggers
@ 2000-01-04 19:27 Matthew Hagerty <matthew@venux.net>
parent: Mitch Vincent <mitch@venux.net>
2 siblings, 0 replies; 34+ messages in thread
From: Matthew Hagerty @ 2000-01-04 19:27 UTC (permalink / raw)
To: Jan Wieck <wieck@debis.com>; Mitch Vincent <mitch@venux.net>; +Cc: pgsql-sql
The system config is:
P2-400, 256Meg ECC, 1 SCSI WDE9180 ULTRA2, FreeBSD-3.3-Release,
gcc-2.7.2.1, pg-6.5.1.
That is the development server, our operational server is:
Dual P2-333, 256Meg ECC, 3 SCSI, FreeBSD-3.3-Release, gcc-2.7.2.1, pg-6.5.3.
The trigger is untested on the operational server as of yet.
Matthew (works with Mitch)
At 07:42 PM 1/4/00 +0100, Jan Wieck wrote:
>Mitch Vincent wrote:
>
> > I'm using this funtion via a trigger to total up amounts in several fields
> > (and adding that value into a total field)..
> >
> > CREATE FUNCTION invoice_total_trigger() RETURNS OPAQUE AS '
> > ...
> >
> > That's the function, here is the trigger.
> >
> > CREATE TRIGGER invoice_total_trigger AFTER INSERT OR UPDATE ON invoice
> > FOR EACH ROW EXECUTE PROCEDURE invoice_total_trigger();
> >
> > It works for updating but not when you insert a new row. Am I doing it
> > wrong?
>
> I wonder why it works on UPDATE at all, it shouldn't. It is an
> AFTER ROW trigger and thus, the changed row you return should get
> ignored. Use BEFORE INSERT instead.
>
> But that it works on UPDATE indicates some bug somewhere. What
> release, OS, etc. are you using?
>
>Jan
>
>--
>
>#======================================================================#
># It's easier to get forgiveness for being wrong than for being right. #
># Let's break this rule - forgive me. #
>#========================================= wieck@debis.com (Jan Wieck) #
>
>
>
>
>************
^ permalink raw reply [nested|flat] 34+ messages in thread
* Triggers
@ 2002-01-21 15:46 Hunter, Ray <rhunter@enterasys.com>
0 siblings, 0 replies; 34+ messages in thread
From: Hunter, Ray @ 2002-01-21 15:46 UTC (permalink / raw)
To: pgsql-sql
Is there any great examples (tutorials) on creating and implementing
triggers in Postgres?
Thanks,
RAY HUNTER
Automated Test Group
Firmware Engineer
ENTERASYS NETWORKS
Internal: 53888
Phone: 801 887-9888
Fax: 801 972-5789
rhunter@enterasys.com <mailto:rhunter@enterasys.com>
www.enterasys.com <www.enterasys.com>
^ permalink raw reply [nested|flat] 34+ messages in thread
* triggers
@ 2002-10-11 13:45 Stian Riis <stian.riis@elineweb.com>
0 siblings, 1 reply; 34+ messages in thread
From: Stian Riis @ 2002-10-11 13:45 UTC (permalink / raw)
To: pgsql-sql
Hi.
Does anyone know if it is posible to make a trigger that execute an
external program ? I want to execute a another program on the server
when I get a row in one of my tables...
-Stian
^ permalink raw reply [nested|flat] 34+ messages in thread
* triggers
@ 2002-10-11 13:48 Stian Riis <stian@elineweb.com>
0 siblings, 1 reply; 34+ messages in thread
From: Stian Riis @ 2002-10-11 13:48 UTC (permalink / raw)
To: pgsql-sql
Hi.
Does anyone know if it is posible to make a trigger that execute an
external program ? I want to execute a another program on the server
when I get a row in one of my tables...
-Stian
^ permalink raw reply [nested|flat] 34+ messages in thread
* Re: triggers
@ 2002-10-11 14:19 Richard Huxton <dev@archonet.com>
parent: Stian Riis <stian@elineweb.com>
0 siblings, 1 reply; 34+ messages in thread
From: Richard Huxton @ 2002-10-11 14:19 UTC (permalink / raw)
To: Stian Riis <stian@elineweb.com>; pgsql-sql
On Friday 11 Oct 2002 2:48 pm, Stian Riis wrote:
> Hi.
>
> Does anyone know if it is posible to make a trigger that execute an
> external program ? I want to execute a another program on the server
> when I get a row in one of my tables...
The customary way is to have a "helper" process that sits there LISTENing for
a NOTICE and then calls the external program as required. Cleaner and means
the other program doesn't have any direct connection to the Postgresql
backend.
- Richard Huxton
^ permalink raw reply [nested|flat] 34+ messages in thread
* Re: triggers
@ 2002-10-11 15:19 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 34+ messages in thread
From: Achilleus Mantzios @ 2002-10-11 15:19 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Stian Riis <stian@elineweb.com>; pgsql-sql
On Fri, 11 Oct 2002, Richard Huxton wrote:
>
> The customary way is to have a "helper" process that sits there LISTENing for
> a NOTICE and then calls the external program as required. Cleaner and means
> the other program doesn't have any direct connection to the Postgresql
> backend.
>
> - Richard Huxton
Yet another cool feature (ala java listeners) of postgresql.
P.S.
Is the word "thank you" too difficult to be written
by people posting and reading??
If anything else, a "thank you" is a motive.
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel: +30-10-8981112
fax: +30-10-8981877
email: achill@matrix.gatewaynet.com
mantzios@softlab.ece.ntua.gr
^ permalink raw reply [nested|flat] 34+ messages in thread
* Re: triggers
@ 2002-10-18 18:02 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Stian Riis <stian.riis@elineweb.com>
0 siblings, 0 replies; 34+ messages in thread
From: Bruce Momjian @ 2002-10-18 18:02 UTC (permalink / raw)
To: Stian Riis <stian.riis@elineweb.com>; +Cc: pgsql-sql
Stian Riis wrote:
> Hi.
>
> Does anyone know if it is posible to make a trigger that execute an
> external program ? I want to execute a another program on the server
> when I get a row in one of my tables...
Yes, you can use plperl and call an external program from there, or us
plsh and run it that way. plsh is mentioned on our web site under
interfaces.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 34+ messages in thread
* Triggers
@ 2004-01-12 16:14 beyaRecords - The home Urban music <uzo@beya-records.com>
0 siblings, 3 replies; 34+ messages in thread
From: beyaRecords - The home Urban music @ 2004-01-12 16:14 UTC (permalink / raw)
To: pgsql-sql
Hi,
does postgresql support the ability to email as in SQL Server? I want
to create a trigger which on input of a record will send out an email.
Is this possible?
Uzo
^ permalink raw reply [nested|flat] 34+ messages in thread
* Re: Triggers
@ 2004-01-12 16:33 Peter Eisentraut <peter_e@gmx.net>
parent: beyaRecords - The home Urban music <uzo@beya-records.com>
2 siblings, 0 replies; 34+ messages in thread
From: Peter Eisentraut @ 2004-01-12 16:33 UTC (permalink / raw)
To: beyaRecords - The home Urban music <uzo@beya-records.com>; pgsql-sql
beyaRecords - The home Urban music wrote:
> does postgresql support the ability to email as in SQL Server? I want
> to create a trigger which on input of a record will send out an
> email. Is this possible?
Write a trigger function in, say, PL/PerlU or PL/sh and have it send the
email with the usual Perl or shell commands.
^ permalink raw reply [nested|flat] 34+ messages in thread
* Re: Triggers
@ 2004-01-12 16:47 Sai Hertz And Control Systems <sank89@sancharnet.in>
parent: beyaRecords - The home Urban music <uzo@beya-records.com>
2 siblings, 2 replies; 34+ messages in thread
From: Sai Hertz And Control Systems @ 2004-01-12 16:47 UTC (permalink / raw)
To: beyaRecords - The home Urban music <uzo@beya-records.com>; +Cc: pgsql-sql
Dear Uzo ,
> Hi,
> does postgresql support the ability to email as in SQL Server? I want
> to create a trigger which on input of a record will send out an email.
> Is this possible?
http://pgmail.sourceforge.net/ is what you need.
Regards,
Vishal Kashyap
^ permalink raw reply [nested|flat] 34+ messages in thread
* Re: Triggers
@ 2004-01-12 17:11 Richard Huxton <dev@archonet.com>
parent: beyaRecords - The home Urban music <uzo@beya-records.com>
2 siblings, 0 replies; 34+ messages in thread
From: Richard Huxton @ 2004-01-12 17:11 UTC (permalink / raw)
To: beyaRecords - The home Urban music <uzo@beya-records.com>; pgsql-sql
On Monday 12 January 2004 16:14, beyaRecords - The home Urban music wrote:
> Hi,
> does postgresql support the ability to email as in SQL Server? I want
> to create a trigger which on input of a record will send out an email.
> Is this possible?
Have a look in the mailing list archives - this has been covered recently. I
seem to recall there is an add-on that does just this.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 34+ messages in thread
* Re: Triggers
@ 2004-01-13 08:26 Benoît BOURNON <benoit.bournon@adelis.com>
parent: Sai Hertz And Control Systems <sank89@sancharnet.in>
1 sibling, 0 replies; 34+ messages in thread
From: Benoît BOURNON @ 2004-01-13 08:26 UTC (permalink / raw)
To: aspire420@hotpop.com; +Cc: beyaRecords - The home Urban music <uzo@beya-records.com>; pgsql-sql
I try pgmail and that is well running ...
fo security, do not use attachment files with your mail ...
try pgmail, you need to use pl/tclu ...
Ben
Sai Hertz And Control Systems wrote:
> Dear Uzo ,
>
>> Hi,
>> does postgresql support the ability to email as in SQL Server? I want
>> to create a trigger which on input of a record will send out an
>> email. Is this possible?
>
>
> http://pgmail.sourceforge.net/ is what you need.
>
> Regards,
>
> Vishal Kashyap
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
^ permalink raw reply [nested|flat] 34+ messages in thread
* Re: Triggers
@ 2004-01-13 09:11 BenLaKnet <benlaknet@icqmail.com>
parent: Sai Hertz And Control Systems <sank89@sancharnet.in>
1 sibling, 0 replies; 34+ messages in thread
From: BenLaKnet @ 2004-01-13 09:11 UTC (permalink / raw)
To: aspire420@hotpop.com; +Cc: beyaRecords - The home Urban music <uzo@beya-records.com>; pgsql-sql
I try pgmail and that is well running ...
fo security, do not use attachment files with your mail ...
try pgmail, you need to use pl/tclu ...
Ben
Sai Hertz And Control Systems wrote:
> Dear Uzo ,
>
>> Hi,
>> does postgresql support the ability to email as in SQL Server? I want
>> to create a trigger which on input of a record will send out an
>> email. Is this possible?
>
>
> http://pgmail.sourceforge.net/ is what you need.
>
> Regards,
>
> Vishal Kashyap
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
^ permalink raw reply [nested|flat] 34+ messages in thread
* Triggers
@ 2004-02-26 10:18 Philip J. Boonzaaier <phil@cobol-africa.com>
0 siblings, 2 replies; 34+ messages in thread
From: Philip J. Boonzaaier @ 2004-02-26 10:18 UTC (permalink / raw)
To: pgsql-sql
The technical reference gives an example of a trigger on a table - employee
Just to test this, I have created the following table,
CREATE TABLE employee
(name VARCHAR(30),
age int4,
state VARCHAR(2),
manager VARCHAR(3),
adult VARCHAR(3));
The I created a simple Function, as follows :
CREATE FUNCTION trig_insert_update_check_emp() RETURNS opaque AS '
BEGIN
IF new.age > 20 THEN
new.adult = ''yes'';
ELSE
new.adult = ''no'';
END IF;
END;
' LANGUAGE 'plpgsql';
Finally, I defined the Trigger as :
CREATE TRIGGER employee_insert_update
BEFORE INSERT OR UPDATE ON employee
FOR EACH ROW EXECUTE PROCEDURE trig_insert_update_check_emp();
Now, when I execute the following :
INSERT INTO employee (name,age,state,manager)
VALUES ('sean',29,'tx','yes');
I get :
ERROR fmgr_info function 6264440 cache lookup failed
What am I doing wrong ????
^ permalink raw reply [nested|flat] 34+ messages in thread
* Re: Triggers
@ 2004-03-05 16:32 Jonathan M. Gardner <jgardner@jonathangardner.net>
parent: Philip J. Boonzaaier <phil@cobol-africa.com>
1 sibling, 0 replies; 34+ messages in thread
From: Jonathan M. Gardner @ 2004-03-05 16:32 UTC (permalink / raw)
To: phil@cobol-africa.com; +Cc: pgsql-sql
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Thursday 26 February 2004 2:18 am, Philip J. Boonzaaier wrote:
> The technical reference gives an example of a trigger on a table -
> employee Just to test this, I have created the following table,
> CREATE TABLE employee
> (name VARCHAR(30),
> age int4,
> state VARCHAR(2),
> manager VARCHAR(3),
> adult VARCHAR(3));
>
> The I created a simple Function, as follows :
>
> CREATE FUNCTION trig_insert_update_check_emp() RETURNS opaque AS '
> BEGIN
> IF new.age > 20 THEN
> new.adult = ''yes'';
> ELSE
> new.adult = ''no'';
> END IF;
> END;
> ' LANGUAGE 'plpgsql';
Couple of comments:
1) Your trigger functions needs to return something. In this case, you
would "RETURN NEW".
2) I believe trigger functions need to "RETURNS TRIGGER", not "RETURNS
opaque", but I could be wrong. Give that a try.
Question:
1) What version of PostgreSQL are you running?
- --
Jonathan Gardner
jgardner@jonathangardner.net
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)
iD8DBQFASKulqp6r/MVGlwwRAgpLAKCycwL/i+/mb6bW1W4QjHKBO9e0xQCgl42q
pmohSw7PZiuIWgOQXxtgvI0=
=4iDJ
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 34+ messages in thread
* Re: Triggers
@ 2004-06-09 09:01 Richard Huxton <dev@archonet.com>
parent: Philip J. Boonzaaier <phil@cobol-africa.com>
1 sibling, 0 replies; 34+ messages in thread
From: Richard Huxton @ 2004-06-09 09:01 UTC (permalink / raw)
To: phil@cobol-africa.com; +Cc: pgsql-sql
Philip J. Boonzaaier wrote:
> The technical reference gives an example of a trigger on a table - employee
> Just to test this, I have created the following table,
> CREATE TABLE employee
> (name VARCHAR(30),
> age int4,
> state VARCHAR(2),
> manager VARCHAR(3),
> adult VARCHAR(3));
>
> The I created a simple Function, as follows :
>
> CREATE FUNCTION trig_insert_update_check_emp() RETURNS opaque AS '
The "opaque" type has been split into several different ones, if you're
using a recent version of PG, you'll want "RETURNS trigger".
[snip]
> Now, when I execute the following :
>
> INSERT INTO employee (name,age,state,manager)
> VALUES ('sean',29,'tx','yes');
>
> I get :
>
> ERROR fmgr_info function 6264440 cache lookup failed
This usually means you redefined something - PG isn't smart enough (yet)
to re-check all the dependencies in something like this. I'd guess you
dropped/recreated the function after defining the trigger, yes?
The solution in older versions is to put the CREATE FUNCTION and CREATE
TRIGGER in the same file so that you drop/recreate both together.
HTH
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 34+ messages in thread
* Re: triggers
@ 2005-02-14 18:27 Michael Fuhr <mike@fuhr.org>
1 sibling, 0 replies; 34+ messages in thread
From: Michael Fuhr @ 2005-02-14 18:27 UTC (permalink / raw)
To: ALÝ ÇELÝK <ali@verus.com.tr>; +Cc: pgsql-sql
On Mon, Feb 14, 2005 at 05:42:27PM +0200, ALÝ ÇELÝK wrote:
>
> update trigger working on same table???
Could you be more specific, please? What are you trying to do?
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
^ permalink raw reply [nested|flat] 34+ messages in thread
* Re: triggers
@ 2005-02-14 20:06 PFC <lists@boutiquenumerique.com>
1 sibling, 0 replies; 34+ messages in thread
From: PFC @ 2005-02-14 20:06 UTC (permalink / raw)
To: ALÝ ÇELÝK <ali@verus.com.tr>; pgsql-sql
> update trigger working on same table???
If an UPDATE trigger does an update on its own table, it can trigger
itself and explode...
^ permalink raw reply [nested|flat] 34+ messages in thread
* Triggers
@ 2005-11-22 14:43 Leif B. Kristensen <leif@solumslekt.org>
0 siblings, 1 reply; 34+ messages in thread
From: Leif B. Kristensen @ 2005-11-22 14:43 UTC (permalink / raw)
To: pgsql-sql
I'm trying to understand triggers. I have read the documentation in the
manual as well as the few pages in the Douglas book about the subject,
but I don't see how to implement a trigger that simply updates a
'last_edit' date field in my 'persons' table whenever I do an insert or
update into my 'participants' table; that is a trigger that basically
does an "UPDATE persons SET 'last_edit' = NOW() WHERE persons.person_id
= participants.person_fk". Is that even possible?
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
^ permalink raw reply [nested|flat] 34+ messages in thread
* Re: Triggers
@ 2005-11-22 15:44 Leif B. Kristensen <leif@solumslekt.org>
parent: Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 2 replies; 34+ messages in thread
From: Leif B. Kristensen @ 2005-11-22 15:44 UTC (permalink / raw)
To: pgsql-sql
On Tuesday 22 November 2005 17:25, Achilleus Mantzios wrote:
>O Leif B. Kristensen Ýãñáøå óôéò Nov 22, 2005 :
>> I'm trying to understand triggers. I have read the documentation in
>> the manual as well as the few pages in the Douglas book about the
>> subject, but I don't see how to implement a trigger that simply
>> updates a 'last_edit' date field in my 'persons' table whenever I do
>> an insert or update into my 'participants' table; that is a trigger
>> that basically does an "UPDATE persons SET 'last_edit' = NOW() WHERE
>> persons.person_id = participants.person_fk". Is that even possible?
>
>smth like:
>foodb=# CREATE or REPLACE FUNCTION upd_dad() RETURNS "trigger" AS '
>foodb'# BEGIN
>foodb'# UPDATE dad set lastedit=now() where id=new.dadid;
>foodb'# RETURN new;
>foodb'# END;
>foodb'# '
>foodb-# LANGUAGE plpgsql;
>CREATE FUNCTION
>foodb=# CREATE TRIGGER upd_dad_tg AFTER INSERT OR UPDATE ON kid FOR
> EACH ROW EXECUTE PROCEDURE upd_dad();
leif=> create or replace function update_last_edit() returns trigger as
$$
leif$> begin
leif$> update persons set last_edit=now() where person_id=new.person_fk;
leif$> return new;
leif$> end;
leif$> $$ language plpgsql;
CREATE FUNCTION
leif=> create trigger update_last_edit after insert or update on
participants
leif-> for each row execute procedure update_last_edit();
CREATE TRIGGER
leif=> insert into participants (participant_id,person_fk) values (1,1);
ERROR: record "new" has no field "last_edit"
CONTEXT: PL/pgSQL function "process_last_edited" line 2 at assignment
leif=>
What am I missing?
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
^ permalink raw reply [nested|flat] 34+ messages in thread
* Re: Triggers
@ 2005-11-22 16:10 Neil Saunders <n.j.saunders@gmail.com>
parent: Leif B. Kristensen <leif@solumslekt.org>
1 sibling, 0 replies; 34+ messages in thread
From: Neil Saunders @ 2005-11-22 16:10 UTC (permalink / raw)
To: Leif B. Kristensen <leif@solumslekt.org>; +Cc: pgsql-sql
Try:
create or replace function update_last_edit() returns trigger as
$$
begin
new.last_edit=now();
return new;
end;
$$ language plpgsql;
On 11/22/05, Leif B. Kristensen <leif@solumslekt.org> wrote:
> On Tuesday 22 November 2005 17:25, Achilleus Mantzios wrote:
> >O Leif B. Kristensen Ýãñáøå óôéò Nov 22, 2005 :
> >> I'm trying to understand triggers. I have read the documentation in
> >> the manual as well as the few pages in the Douglas book about the
> >> subject, but I don't see how to implement a trigger that simply
> >> updates a 'last_edit' date field in my 'persons' table whenever I do
> >> an insert or update into my 'participants' table; that is a trigger
> >> that basically does an "UPDATE persons SET 'last_edit' = NOW() WHERE
> >> persons.person_id = participants.person_fk". Is that even possible?
> >
> >smth like:
> >foodb=# CREATE or REPLACE FUNCTION upd_dad() RETURNS "trigger" AS '
> >foodb'# BEGIN
> >foodb'# UPDATE dad set lastedit=now() where id=new.dadid;
> >foodb'# RETURN new;
> >foodb'# END;
> >foodb'# '
> >foodb-# LANGUAGE plpgsql;
> >CREATE FUNCTION
> >foodb=# CREATE TRIGGER upd_dad_tg AFTER INSERT OR UPDATE ON kid FOR
> > EACH ROW EXECUTE PROCEDURE upd_dad();
>
> leif=> create or replace function update_last_edit() returns trigger as
> $$
> leif$> begin
> leif$> update persons set last_edit=now() where person_id=new.person_fk;
> leif$> return new;
> leif$> end;
> leif$> $$ language plpgsql;
> CREATE FUNCTION
> leif=> create trigger update_last_edit after insert or update on
> participants
> leif-> for each row execute procedure update_last_edit();
> CREATE TRIGGER
> leif=> insert into participants (participant_id,person_fk) values (1,1);
> ERROR: record "new" has no field "last_edit"
> CONTEXT: PL/pgSQL function "process_last_edited" line 2 at assignment
> leif=>
>
> What am I missing?
> --
> Leif Biberg Kristensen | Registered Linux User #338009
> http://solumslekt.org/ | Cruising with Gentoo/KDE
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
^ permalink raw reply [nested|flat] 34+ messages in thread
* Re: Triggers
@ 2005-11-22 16:21 Neil Saunders <n.j.saunders@gmail.com>
parent: Achilleus Mantzios <achill@matrix.gatewaynet.com>
1 sibling, 1 reply; 34+ messages in thread
From: Neil Saunders @ 2005-11-22 16:21 UTC (permalink / raw)
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>; +Cc: Leif B. Kristensen <leif@solumslekt.org>; pgsql-sql
And change AFER INSERT to BEFORE INSERT
On 11/22/05, Achilleus Mantzios <achill@matrix.gatewaynet.com> wrote:
> O Leif B. Kristensen Ýãñáøå óôéò Nov 22, 2005 :
>
> > On Tuesday 22 November 2005 17:25, Achilleus Mantzios wrote:
> > >O Leif B. Kristensen Ýãñáøå óôéò Nov 22, 2005 :
> > >> I'm trying to understand triggers. I have read the documentation in
> > >> the manual as well as the few pages in the Douglas book about the
> > >> subject, but I don't see how to implement a trigger that simply
> > >> updates a 'last_edit' date field in my 'persons' table whenever I do
> > >> an insert or update into my 'participants' table; that is a trigger
> > >> that basically does an "UPDATE persons SET 'last_edit' = NOW() WHERE
> > >> persons.person_id = participants.person_fk". Is that even possible?
> > >
> > >smth like:
> > >foodb=# CREATE or REPLACE FUNCTION upd_dad() RETURNS "trigger" AS '
> > >foodb'# BEGIN
> > >foodb'# UPDATE dad set lastedit=now() where id=new.dadid;
> > >foodb'# RETURN new;
> > >foodb'# END;
> > >foodb'# '
> > >foodb-# LANGUAGE plpgsql;
> > >CREATE FUNCTION
> > >foodb=# CREATE TRIGGER upd_dad_tg AFTER INSERT OR UPDATE ON kid FOR
> > > EACH ROW EXECUTE PROCEDURE upd_dad();
> >
> > leif=> create or replace function update_last_edit() returns trigger as
> > $$
> > leif$> begin
> > leif$> update persons set last_edit=now() where person_id=new.person_fk;
> > leif$> return new;
> > leif$> end;
> > leif$> $$ language plpgsql;
> > CREATE FUNCTION
> > leif=> create trigger update_last_edit after insert or update on
> > participants
> > leif-> for each row execute procedure update_last_edit();
> > CREATE TRIGGER
> > leif=> insert into participants (participant_id,person_fk) values (1,1);
> > ERROR: record "new" has no field "last_edit"
> > CONTEXT: PL/pgSQL function "process_last_edited" line 2 at assignment
> ^^^^^^^^^^^^^^^^^^^^^
>
> > leif=>
> >
> > What am I missing?
> >
>
> apparently some forgotten process_last_edited() function.
>
> --
> -Achilleus
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
^ permalink raw reply [nested|flat] 34+ messages in thread
* Re: Triggers
@ 2005-11-22 16:25 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Leif B. Kristensen <leif@solumslekt.org>
0 siblings, 1 reply; 34+ messages in thread
From: Achilleus Mantzios @ 2005-11-22 16:25 UTC (permalink / raw)
To: Leif B. Kristensen <leif@solumslekt.org>; +Cc: pgsql-sql
O Leif B. Kristensen Ýãñáøå óôéò Nov 22, 2005 :
> I'm trying to understand triggers. I have read the documentation in the
> manual as well as the few pages in the Douglas book about the subject,
> but I don't see how to implement a trigger that simply updates a
> 'last_edit' date field in my 'persons' table whenever I do an insert or
> update into my 'participants' table; that is a trigger that basically
> does an "UPDATE persons SET 'last_edit' = NOW() WHERE persons.person_id
> = participants.person_fk". Is that even possible?
>
smth like:
foodb=# CREATE or REPLACE FUNCTION upd_dad() RETURNS "trigger" AS '
foodb'# BEGIN
foodb'# UPDATE dad set lastedit=now() where id=new.dadid;
foodb'# RETURN new;
foodb'# END;
foodb'# '
foodb-# LANGUAGE plpgsql;
CREATE FUNCTION
foodb=# CREATE TRIGGER upd_dad_tg AFTER INSERT OR UPDATE ON kid FOR EACH
ROW EXECUTE PROCEDURE upd_dad();
--
-Achilleus
^ permalink raw reply [nested|flat] 34+ messages in thread
* Re: Triggers
@ 2005-11-22 17:07 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Leif B. Kristensen <leif@solumslekt.org>
1 sibling, 2 replies; 34+ messages in thread
From: Achilleus Mantzios @ 2005-11-22 17:07 UTC (permalink / raw)
To: Leif B. Kristensen <leif@solumslekt.org>; +Cc: pgsql-sql
O Leif B. Kristensen Ýãñáøå óôéò Nov 22, 2005 :
> On Tuesday 22 November 2005 17:25, Achilleus Mantzios wrote:
> >O Leif B. Kristensen Ýãñáøå óôéò Nov 22, 2005 :
> >> I'm trying to understand triggers. I have read the documentation in
> >> the manual as well as the few pages in the Douglas book about the
> >> subject, but I don't see how to implement a trigger that simply
> >> updates a 'last_edit' date field in my 'persons' table whenever I do
> >> an insert or update into my 'participants' table; that is a trigger
> >> that basically does an "UPDATE persons SET 'last_edit' = NOW() WHERE
> >> persons.person_id = participants.person_fk". Is that even possible?
> >
> >smth like:
> >foodb=# CREATE or REPLACE FUNCTION upd_dad() RETURNS "trigger" AS '
> >foodb'# BEGIN
> >foodb'# UPDATE dad set lastedit=now() where id=new.dadid;
> >foodb'# RETURN new;
> >foodb'# END;
> >foodb'# '
> >foodb-# LANGUAGE plpgsql;
> >CREATE FUNCTION
> >foodb=# CREATE TRIGGER upd_dad_tg AFTER INSERT OR UPDATE ON kid FOR
> > EACH ROW EXECUTE PROCEDURE upd_dad();
>
> leif=> create or replace function update_last_edit() returns trigger as
> $$
> leif$> begin
> leif$> update persons set last_edit=now() where person_id=new.person_fk;
> leif$> return new;
> leif$> end;
> leif$> $$ language plpgsql;
> CREATE FUNCTION
> leif=> create trigger update_last_edit after insert or update on
> participants
> leif-> for each row execute procedure update_last_edit();
> CREATE TRIGGER
> leif=> insert into participants (participant_id,person_fk) values (1,1);
> ERROR: record "new" has no field "last_edit"
> CONTEXT: PL/pgSQL function "process_last_edited" line 2 at assignment
^^^^^^^^^^^^^^^^^^^^^
> leif=>
>
> What am I missing?
>
apparently some forgotten process_last_edited() function.
--
-Achilleus
^ permalink raw reply [nested|flat] 34+ messages in thread
* Re: Triggers
@ 2005-11-22 17:17 Leif B. Kristensen <leif@solumslekt.org>
parent: Achilleus Mantzios <achill@matrix.gatewaynet.com>
1 sibling, 0 replies; 34+ messages in thread
From: Leif B. Kristensen @ 2005-11-22 17:17 UTC (permalink / raw)
To: pgsql-sql
On Tuesday 22 November 2005 18:07, Achilleus Mantzios wrote:
>O Leif B. Kristensen ?????? ???? Nov 22, 2005 :
>> What am I missing?
>
>apparently some forgotten process_last_edited() function.
Yes -- an earlier attempt at the same thing ...
I finally managed to create my first trigger:
CREATE OR REPLACE FUNCTION update_last_edit() RETURNS TRIGGER AS $$
BEGIN
UPDATE persons SET last_edit=NOW() WHERE person_id=NEW.person_fk;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER update_last_edit
BEFORE INSERT OR UPDATE ON participants
FOR EACH ROW EXECUTE PROCEDURE update_last_edit();
And it's even working as it should :-)
Thanks, guys.
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
^ permalink raw reply [nested|flat] 34+ messages in thread
* Re: Triggers
@ 2005-11-23 08:44 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Neil Saunders <n.j.saunders@gmail.com>
0 siblings, 1 reply; 34+ messages in thread
From: Achilleus Mantzios @ 2005-11-23 08:44 UTC (permalink / raw)
To: Neil Saunders <n.j.saunders@gmail.com>; +Cc: Leif B. Kristensen <leif@solumslekt.org>; pgsql-sql
O Neil Saunders Ýãñáøå óôéò Nov 22, 2005 :
> And change AFER INSERT to BEFORE INSERT
1) it doesnt make any difference since we are updating
a different table than the trigger's one
2) Your email text comes really garbled
>
--
-Achilleus
^ permalink raw reply [nested|flat] 34+ messages in thread
* Re: Triggers
@ 2005-11-29 23:25 Jan Wieck <JanWieck@Yahoo.com>
parent: Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 0 replies; 34+ messages in thread
From: Jan Wieck @ 2005-11-29 23:25 UTC (permalink / raw)
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>; +Cc: Neil Saunders <n.j.saunders@gmail.com>; Leif B. Kristensen <leif@solumslekt.org>; pgsql-sql
On 11/23/2005 3:44 AM, Achilleus Mantzios wrote:
> O Neil Saunders έγραψε στις Nov 22, 2005 :
>
>> And change AFER INSERT to BEFORE INSERT
>
> 1) it doesnt make any difference since we are updating
> a different table than the trigger's one
In this particular case it doesn't. In general, another BEFORE trigger
can still modify the new row, so your trigger might not see the final
values in NEW.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 34+ messages in thread
* Triggers
@ 2006-08-21 12:03 Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
0 siblings, 2 replies; 34+ messages in thread
From: Ezequias Rodrigues da Rocha @ 2006-08-21 12:03 UTC (permalink / raw)
To: pgsql-sql
Hi list,
I am planning to generate a trigger to each [update or delete or insert] on
my database to register all moviment on my base (current_user, when, action,
user).
I notice that the trigger is able put a procedure in action but as I have
more than 20 tables I would like to use only one trigger to solve my
problem. Is that possible ?
Now my trigger is working well but I do not want to make 26 triggers and 26
functions.
Is there any people that can help me with this question ?
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Atenciosamente (Sincerely)
Ezequias Rodrigues da Rocha
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
A pior das democracias ainda é melhor do que a melhor das ditaduras
The worst of democracies is still better than the better of dictatorships
http://ezequiasrocha.blogspot.com/
^ permalink raw reply [nested|flat] 34+ messages in thread
* Re: Triggers
@ 2006-08-21 12:19 Kaloyan Iliev <news1@faith.digsys.bg>
parent: Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
1 sibling, 0 replies; 34+ messages in thread
From: Kaloyan Iliev @ 2006-08-21 12:19 UTC (permalink / raw)
To: Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>; pgsql-sql
Hi,
You can use one procedure(function in Postgres) but you have to use 26
triggers.
Every trigger can call this one procedure to record the information you
want.
Regards,
Kaloyan Iliev
Ezequias Rodrigues da Rocha wrote:
> Hi list,
>
> I am planning to generate a trigger to each [update or delete or
> insert] on my database to register all moviment on my base
> (current_user, when, action, user).
>
> I notice that the trigger is able put a procedure in action but as I
> have more than 20 tables I would like to use only one trigger to solve
> my problem. Is that possible ?
>
> Now my trigger is working well but I do not want to make 26 triggers
> and 26 functions.
>
> Is there any people that can help me with this question ?
>
>
> =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
> Atenciosamente (Sincerely)
> Ezequias Rodrigues da Rocha
> =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
> A pior das democracias ainda й melhor do que a melhor das ditaduras
> The worst of democracies is still better than the better of dictatorships
> http://ezequiasrocha.blogspot.com/
^ permalink raw reply [nested|flat] 34+ messages in thread
* Re: Triggers
@ 2006-08-22 06:34 Kis <kjt@takarnet.hu>
parent: Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
1 sibling, 0 replies; 34+ messages in thread
From: Kis János Tamás @ 2006-08-22 06:34 UTC (permalink / raw)
To: pgsql-sql
Hi,
Plesae read my previous message in this mail list,
on 2006-08-15. 14.20 at " [SQL] Undo an update" thread.
bye,
kjt
McAfee SCM 4.1 által ellenőrizve!
^ permalink raw reply [nested|flat] 34+ messages in thread
end of thread, other threads:[~2006-08-22 06:34 UTC | newest]
Thread overview: 34+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-01-04 15:04 Triggers Mitch Vincent <mitch@venux.net>
2000-01-04 15:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-01-04 16:21 ` Mitch Vincent <mitch@venux.net>
2000-01-04 18:42 ` Jan Wieck <wieck@debis.com>
2000-01-04 19:27 ` Matthew Hagerty <matthew@venux.net>
2002-01-21 15:46 Triggers Hunter, Ray <rhunter@enterasys.com>
2002-10-11 13:45 triggers Stian Riis <stian.riis@elineweb.com>
2002-10-18 18:02 ` Re: triggers Bruce Momjian <pgman@candle.pha.pa.us>
2002-10-11 13:48 triggers Stian Riis <stian@elineweb.com>
2002-10-11 14:19 ` Re: triggers Richard Huxton <dev@archonet.com>
2002-10-11 15:19 ` Re: triggers Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-01-12 16:14 Triggers beyaRecords - The home Urban music <uzo@beya-records.com>
2004-01-12 16:33 ` Peter Eisentraut <peter_e@gmx.net>
2004-01-12 16:47 ` Sai Hertz And Control Systems <sank89@sancharnet.in>
2004-01-13 08:26 ` Benoît BOURNON <benoit.bournon@adelis.com>
2004-01-13 09:11 ` BenLaKnet <benlaknet@icqmail.com>
2004-01-12 17:11 ` Richard Huxton <dev@archonet.com>
2004-02-26 10:18 Triggers Philip J. Boonzaaier <phil@cobol-africa.com>
2004-03-05 16:32 ` Jonathan M. Gardner <jgardner@jonathangardner.net>
2004-06-09 09:01 ` Richard Huxton <dev@archonet.com>
2005-02-14 18:27 ` Re: triggers Michael Fuhr <mike@fuhr.org>
2005-02-14 20:06 ` Re: triggers PFC <lists@boutiquenumerique.com>
2005-11-22 14:43 Triggers Leif B. Kristensen <leif@solumslekt.org>
2005-11-22 16:25 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2005-11-22 15:44 ` Leif B. Kristensen <leif@solumslekt.org>
2005-11-22 16:10 ` Neil Saunders <n.j.saunders@gmail.com>
2005-11-22 17:07 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2005-11-22 16:21 ` Neil Saunders <n.j.saunders@gmail.com>
2005-11-23 08:44 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2005-11-29 23:25 ` Jan Wieck <JanWieck@Yahoo.com>
2005-11-22 17:17 ` Leif B. Kristensen <leif@solumslekt.org>
2006-08-21 12:03 Triggers Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
2006-08-21 12:19 ` Kaloyan Iliev <news1@faith.digsys.bg>
2006-08-22 06:34 ` Kis <kjt@takarnet.hu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox