Re: Creating a trigger function

You've already received some help in later messages.
See below for a couple of additional comments.

--- Peter Erickson <news(at)redlamb(dot)net> wrote:
> I am running postgresql 7.4.2 and having problems
> creating a trigger
> function properly. I keep getting the following
> error:
>
> ERROR: OLD used in query that is not in rule
>
> I have a table called journal_entries with a foreign
> key to a table
> called journals. When a entry is added to
> journal_entries, I am trying
> to get it to update the 'mtime' field of the
> corresponding entry in the
> journals table.
>
> Can anyone help me with this problem? If you need
> more information,
> please let me know.
>
> CREATE OR REPLACE FUNCTION
> public.update_journal_mtime()
> RETURNS trigger AS
> '
> DECLARE
> curtime TIMESTAMP;
> BEGIN
> curtime := \'now\';

You may just prefer to use CURRENT_TIMESTAMP, which is
the SQL-standard built in variable which gives you the
same thing.

> IF OLD IS NOT NULL THEN
> UPDATE journals SET mtime = curtime WHERE id
> = OLD.journ_id;
> END IF;
> IF NEW IS NOT NULL THEN
> UPDATE journals SET mtime = curtime WHERE id
> = NEW.journ_id;
> UPDATE journal_entries SET mtime = curtime
> WHERE id = NEW.id;

This will get you an infinite loop, because you are
recursively calling this trigger function. Just
assign directly, i.e. NEW.mtime := curtime .

Note that the assignment operator is supposed to be
":=", not "=", which is a test of equality. But the
two ended up equivalent by mistake. Somebody might
fix that one day...

> END IF;
> RETURN null;

If you return "null" from a trigger function, the
operation will be aborted. You will need to return
"NEW" or "OLD" as appropriate (hmm, I wonder if
returning "NEW" from a delete operation would cause an
error? I haven't tried it).

> END;
> '
> LANGUAGE 'plpgsql' VOLATILE;
>
> CREATE TRIGGER update_mtime_trigger
> AFTER INSERT OR UPDATE OR DELETE
> ON public.journal_entries
> FOR EACH ROW
> EXECUTE PROCEDURE public.update_journal_mtime();
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 8: explain analyze is your friend

__________________________________
Do you Yahoo!?
Yahoo! Small Business $15K Web Design Giveaway
http://promotions.yahoo.com/design_giveaway/