pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedtrouble creating trigger
3+ messages / 3 participants
[nested] [flat]
* trouble creating trigger
@ 2003-04-22 02:28 Sigi Jekabsons <sigi.j@workskillsprofessionals.com.au>
0 siblings, 2 replies; 3+ messages in thread
From: Sigi Jekabsons @ 2003-04-22 02:28 UTC (permalink / raw)
To: pgsql-sql
I can create the function the trigger is calling, and I can use the function
in a SELECT, and it all works fine, but postgres won't let me create a
trigger using that function.
Here I'm creating the function:
asp_employ=# CREATE FUNCTION upd_act_date(int4) RETURNS INTEGER AS '
asp_employ'# DECLARE
asp_employ'# cand_id_arg ALIAS FOR $1;
asp_employ'# BEGIN
asp_employ'# UPDATE cands SET actlog_updated_last = ( SELECT
MAX(date_logged) FROM cand_log WHERE cand_id = cand_id_arg ) WHERE cand_id =
cand_id_arg;
asp_employ'# RETURN 1;
asp_employ'# END;
asp_employ'# ' LANGUAGE 'plpgsql';
CREATE FUNCTION
And the function appears to work okay:
asp_employ=# SELECT upd_act_date('003234');
upd_act_date
--------------
1
(1 row)
However I can't create the trigger:
asp_employ=# CREATE TRIGGER upd_act_date_tg
asp_employ-# BEFORE INSERT OR UPDATE ON cand_log FOR EACH ROW
asp_employ-# EXECUTE PROCEDURE upd_act_date(cand_id);
ERROR: CreateTrigger: function upd_act_date() does not exist
This doesn't work either:
asp_employ=# CREATE TRIGGER upd_act_date_tg
asp_employ-# AFTER INSERT OR UPDATE ON cand_log
asp_employ-# FOR EACH ROW EXECUTE PROCEDURE upd_act_date('003234');
ERROR: CreateTrigger: function upd_act_date() does not exist
What am I doing wrong?
This is on Postgres 7.3.2 on debian.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: trouble creating trigger
@ 2003-04-22 03:58 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Sigi Jekabsons <sigi.j@workskillsprofessionals.com.au>
1 sibling, 0 replies; 3+ messages in thread
From: Stephan Szabo @ 2003-04-22 03:58 UTC (permalink / raw)
To: Sigi Jekabsons <sigi.j@workskillsprofessionals.com.au>; +Cc: pgsql-sql
On Tue, 22 Apr 2003, Sigi Jekabsons wrote:
> I can create the function the trigger is calling, and I can use the function
> in a SELECT, and it all works fine, but postgres won't let me create a
> trigger using that function.
Trigger functions return type trigger and take no arguments. The
arguments passed at create trigger time are specially handled.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: trouble creating trigger
@ 2003-04-22 12:20 A.Bhuvaneswaran <bhuvansql@myrealbox.com>
parent: Sigi Jekabsons <sigi.j@workskillsprofessionals.com.au>
1 sibling, 0 replies; 3+ messages in thread
From: A.Bhuvaneswaran @ 2003-04-22 12:20 UTC (permalink / raw)
To: Sigi Jekabsons <sigi.j@workskillsprofessionals.com.au>; +Cc: pgsql-sql
> I can create the function the trigger is calling, and I can use the function
> in a SELECT, and it all works fine, but postgres won't let me create a
> trigger using that function.
Follow the below link to learn howto write triggered procedures:
http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=plpgsql-trigger.html
regards,
bhuvaneswaran
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-04-22 12:20 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-04-22 02:28 trouble creating trigger Sigi Jekabsons <sigi.j@workskillsprofessionals.com.au>
2003-04-22 03:58 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-04-22 12:20 ` A.Bhuvaneswaran <bhuvansql@myrealbox.com>
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