is it me or trigger side effects

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

Responses

Browse pgsql-sql by date

  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 ?