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();
