Ok, I have determined that i can't do:
IF NEW IS NOT NULL -- or -- IF OLD IS NOT NULL
and that is what is causing the error.
So, with this now known, is there a way to create a trigger & function
that will allow inserts, updates, and deletes to occur while updating a
field (mtime) in another table? At the same time, if an update takes
place, it updates the modified time field (mtime) to the current time.
I can them all to work individually by modifying the function, but I
cant get the to all work in the same function. Do I need to create a
trigger for inserts/updates and another for deletes?
Any help is greatly appreciated. Thanks in advance.
If it helps, here are the table definitions:
CREATE TABLE journals (
id int NOT NULL DEFAULT nextval('journal_id_seq'::text),
owner_id int NOT NULL,
name varchar(15) NOT NULL,
descr varchar(50) NOT NULL,
ctime timestamp NOT NULL DEFAULT ('now'::text)::timestamp(6),
mtime timestamp NOT NULL DEFAULT ('now'::text)::timestamp(6),
CONSTRAINT journals_pkey PRIMARY KEY (id),
CONSTRAINT fkey_user_id FOREIGN KEY (owner_id) REFERENCES users
(user_id) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT journal_descr CHECK descr::text <> ''::text,
CONSTRAINT journal_name CHECK name::text <> ''::text
);
CREATE TABLE journal_entries
(
id int NOT NULL DEFAULT nextval('journal_ent_id_seq'::text),
journ_id int NOT NULL,
entry varchar(1000) NOT NULL,
ctime timestamp NOT NULL DEFAULT ('now'::text)::timestamp(6),
mtime timestamp NOT NULL DEFAULT ('now'::text)::timestamp(6),
CONSTRAINT journal_entries_pkey PRIMARY KEY (id),
CONSTRAINT fkey_journal_id FOREIGN KEY (journ_id) REFERENCES journals
(id) ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT entry_check CHECK entry::text <> ''::text
);
Peter Erickson 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\';
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();
