Creating a trigger function

From: Peter Erickson <news(at)redlamb(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Creating a trigger function
Date: 2004-04-04 02:36:38
Message-ID: 9sGdnVnmCewn6fLdRVn-uw@comcast.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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();

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Joe Stump 2004-04-04 04:35:02 Re: Recursive FOREIGN KEY?
Previous Message Joe Stump 2004-04-04 02:31:15 Re: Recursive FOREIGN KEY?