By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,367 Members | 1,279 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,367 IT Pros & Developers. It's quick & easy.

Creating a trigger function

P: n/a
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();
Nov 23 '05 #1
Share this Question
Share on Google+
5 Replies

P: n/a
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();

Nov 23 '05 #2

P: n/a
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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #3

P: n/a
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.
Nov 23 '05 #4

P: n/a
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


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #5

P: n/a
You've already received some help in later messages.
See below for a couple of additional comments.

--- Peter Erickson <ne**@redlamb.net> 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\';
You may just prefer to use CURRENT_TIMESTAMP, which is
the SQL-standard built in variable which gives you the
same thing.
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;
This will get you an infinite loop, because you are
recursively calling this trigger function. Just
assign directly, i.e. NEW.mtime := curtime .

Note that the assignment operator is supposed to be
":=", not "=", which is a test of equality. But the
two ended up equivalent by mistake. Somebody might
fix that one day...
END IF;
RETURN null;
If you return "null" from a trigger function, the
operation will be aborted. You will need to return
"NEW" or "OLD" as appropriate (hmm, I wonder if
returning "NEW" from a delete operation would cause an
error? I haven't tried it).

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 8: explain analyze is your friend

__________________________________
Do you Yahoo!?
Yahoo! Small Business $15K Web Design Giveaway
http://promotions.yahoo.com/design_giveaway/

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.