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();
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
