Re: Creating a trigger function - Mailing list pgsql-general

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