Re: Triggers

Hi Nolan,

 >>CREATE FUNCTION t_dec_item_summary ()
 >>RETURNS trigger
 >>AS '
 >>   BEGIN
 >>     update orders set
 >>       item_count = item_count - 1
 >>     WHERE code = OLD.orders;
 >>     RETURN OLD;
 >>   END;
 >>' language 'plpgsql';

Yes you are right.  By getting the trigger to return NEW instead of OLD,
  my UPDATE does work.  You are also right in saying that this then
stoppes the DELETE working!

You are also right, I have two functions/triggers, one to decrement my
couters and one to increment.  The Dec called 'BEFORE UPDATE OR DELETE',
the Inc called 'AFTER UPDATE OR INSERT'.  Therefore, UPDATE is called
BEFORE and AFTER.

If 'RETURN OLD' only works for 'BEFORE DELETE' and 'RETURN NEW' only
work for 'BEFORE INSERT', then I guess I need three triggers / functions
for all these cases??

BEFORE DELETE -> (Decrememt) -> RETURN OLD
BEFORE UPDATE -> (Decrement) -> RETURN NEW
AFTER UPDATE OR INSERT -> (Increment) -> RETURN NEW

?

As you rightly comment, this is getting messy.  It also has am air of
nasty non-liniarity, and from my University work, increases the Metric
count, which is apparently a bad thing!

I do not know what the TG_OP is.  I'll do some research.  If I can avoid
having a list of triggers for a single action, calling one 'BEFORE AND
AFTER UPDATE OR DELETE OR INSERT' then this would be great.

If you or any of your members know of a set of triggers / functions to
ideally effect a counter in another reference, I would be extreamly
interested in seeing it!

Thanks for your help!  Once again I can return to my coding....

Ben



> I think your problem may be that you are returning OLD instead of NEW,
> I think that substitutes the old values for the new ones which cancels
> the impact of an update.  (I'm not sure what it'd do on a delete,
> you may need an OLD there, in which case you will need to vary the
> return statement based on TG_OP.)
>
> I'm also not sure of your logic.  Why decrement the counter on an
> update?  Do you have a separate trigger to increment it on an insert?
> By using TG_OP you can probably combine all three actions into one
> trigger, personally I find that neater than having multiple triggers.
> --
> Mike Nolan
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>