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