Thread: Re: Creating a trigger function
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();
On Sunday 04 April 2004 20:40, Peter Erickson wrote: > 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? In plpgsql, there are a number of special TG_XXX variables defined. You want to look at TG_OP. See the manuals for details. -- Richard Huxton Archonet Ltd
You want the TG_OP variable. For example:
IF TG_OP = 'INSERT' THEN
.. code here ..
ELSIF TG_OP = 'UPDATE' THEN
.. code here ..
ELSIF TG_OP = 'DELETE' THEN
.. code here ..
END IF;
Greg
Peter Erickson wrote:
> 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
Richard Huxton wrote: > On Sunday 04 April 2004 20:40, Peter Erickson wrote: > >>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? > > > In plpgsql, there are a number of special TG_XXX variables defined. You want > to look at TG_OP. See the manuals for details. > Thanks. Worked like a charm. I must have glossed over the variables other than NEW and OLD. Thanks again.