Thread: trouble creating trigger

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.

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.

> 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