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/ ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
