From: Najm Hashmi <najm@mondo-live.com>
To: pgsql <pgsql-sql@postgresql.org>
Subject: is it me or trigger side effects
Date: Mon, 26 Mar 2001 10:41:15 -0500
Message-ID: <3ABF631B.18B66558@mondo-live.com> (raw)
Hi all,
I have written a trigger to update a table called categories whenever a tuple
is either deleted or inserted in tables articles, media, and links. It works
fine for inserts but "pukes" on deletes :). I am using two auxiliary
addcount(varchar) and delecount (varchar)
I am getting the following error on deletes:
record new is unassigned yet .
below is my code.
drop function addCount(varchar);
create function AddCount(varchar) returns integer as '
declare
cat_id alias for $1;
len integer;
cnt integer;
cond varchar;
begin
len:= length(cat_id);
cnt:=0;
for i in 1 .. len loop
cnt:=cnt +1;
cond:=substr(cat_id,1,cnt);
update categories set items= items+1 where id like cond;
end loop;
return cnt;
end;
' language 'plpgsql';
select addCount('KDA');
drop function delCount(varchar);
create function delCount(varchar) returns integer as '
declare
cat_id alias for $1;
len integer;
cnt integer;
cond varchar;
begin
len:= length(cat_id);
cnt:=0;
for i in 1 .. len loop
cnt:=cnt +1;
cond:=substr(cat_id,1,cnt);
update categories set items= items-1 where id like cond;
end loop;
return cnt;
end;
' language 'plpgsql';
select delCount('KDA');
drop trigger trigger_update_articles on articles;
drop trigger trigger_update_links on links;
drop trigger trigger_update_media on media;
drop function updateCat();
create function updateCat() returns opaque as '
declare
rec record;
rename new to cat;
rename old to ct;
maxlen integer;
begin
if tg_op = ''INSERT'' and cat.category is null then
raise exception ''You are missing entry for category field'';
else
if cat.display then
maxlen:= addCount(cat.category);
return cat;
else
return cat;
end if;
end if;
if tg_op = ''DELETE''then
maxlen:= delCount(ct.category);
return ct;
end if;
end;
' language 'plpgsql';
create trigger trigger_update_articles before insert or delete
on articles for each row execute procedure updateCat();
create trigger trigger_update_media before insert or delete
on media for each row execute procedure updateCat();
create trigger trigger_update_links before insert or delete
on links for each row execute procedure updateCat();
Attachments:najm.vcf (text/x-vcard, 183B) download
begin:vcard
n:Hashmi;Najm
x-mozilla-html:FALSE
org:Mondo-Live.com;www.flipr.com
adr:;;;;;;
version:2.1
email;internet:najm@mondo-live.com
x-mozilla-cpt:;6144
fn:Najm Hashmi
end:vcard
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: najm@mondo-live.com
Subject: Re: is it me or trigger side effects
In-Reply-To: <3ABF631B.18B66558@mondo-live.com>
References: <3ABF631B.18B66558@mondo-live.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox