From: | Najm Hashmi <najm(at)mondo-live(dot)com> |
---|---|
To: | pgsql <pgsql-sql(at)postgresql(dot)org> |
Subject: | is it me or trigger side effects |
Date: | 2001-03-26 15:41:15 |
Message-ID: | 3ABF631B.18B66558@mondo-live.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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();
Attachment | Content-Type | Size |
---|---|---|
najm.vcf | text/x-vcard | 184 bytes |
From | Date | Subject | |
---|---|---|---|
Next Message | Joe Conway | 2001-03-26 15:56:54 | Re: Help |
Previous Message | Michael Fork | 2001-03-26 14:49:46 | Re: Still don't know how to build this string ? |