pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedis it me or trigger side effects
3+ messages / 3 participants
[nested] [flat]
* is it me or trigger side effects
@ 2001-03-26 15:41 Najm Hashmi <najm@mondo-live.com>
2001-03-26 17:10 ` Re: is it me or trigger side effects Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 3+ messages in thread
From: Najm Hashmi @ 2001-03-26 15:41 UTC (permalink / raw)
To: 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();
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: is it me or trigger side effects
2001-03-26 15:41 is it me or trigger side effects Najm Hashmi <najm@mondo-live.com>
@ 2001-03-26 17:10 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-03-26 20:42 ` Re: is it me or trigger side effects Cedar Cox <cedarc@visionforisrael.com>
0 siblings, 1 reply; 3+ messages in thread
From: Stephan Szabo @ 2001-03-26 17:10 UTC (permalink / raw)
To: Najm Hashmi <najm@mondo-live.com>; +Cc: pgsql-sql
> 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';
I think you want your if blocks to be more like:
if tg_op = ''INSERT''
if cat.category is null
(raise exception)
else
(do insert stuff)
end if
end if
if tg_op = ''DELETE''
(do delete stuff)
end if
I think you're currently going to get into the
insert stuff on both inserts where it isn't
null and deletes (since in both cases the
and will be false).
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: is it me or trigger side effects
2001-03-26 15:41 is it me or trigger side effects Najm Hashmi <najm@mondo-live.com>
2001-03-26 17:10 ` Re: is it me or trigger side effects Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2001-03-26 20:42 ` Cedar Cox <cedarc@visionforisrael.com>
0 siblings, 0 replies; 3+ messages in thread
From: Cedar Cox @ 2001-03-26 20:42 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: Najm Hashmi <najm@mondo-live.com>; pgsql-sql
On Mon, 26 Mar 2001, Stephan Szabo wrote:
>
> > 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';
>
> I think you want your if blocks to be more like:
> if tg_op = ''INSERT''
> if cat.category is null
> (raise exception)
> else
> (do insert stuff)
> end if
> end if
> if tg_op = ''DELETE''
> (do delete stuff)
> end if
>
> I think you're currently going to get into the
> insert stuff on both inserts where it isn't
> null and deletes (since in both cases the
> and will be false).
You probably want to handle UPDATEs as well.. make sure you plan for this
too.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2001-03-26 20:42 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-03-26 15:41 is it me or trigger side effects Najm Hashmi <najm@mondo-live.com>
2001-03-26 17:10 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-03-26 20:42 ` Cedar Cox <cedarc@visionforisrael.com>
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