Thread: Creating a trigger function

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\';
     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;
     END IF;
     RETURN null;
   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();

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

--- Peter Erickson <news@redlamb.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/