Обсуждение: Triggers

> 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';

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

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
>

> 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.

No, you can't have one trigger that fires both before and after.

However, you can have one trigger that fires before and another that fires
after, and both of them can call the SAME function, leaving you with
just one block of code to maintain.

Whether this is more efficient than multiple blocks of code is debatable,
but I think it enhances maintainability, which is at least as important
as performance.  Computer time is cheap, programmer time is not.

TG_WHEN contains when the trigger fired ('BEFORE' or 'AFTER') and TG_OP
contains the database operation that caused the trigger to fire
('INSERT','DELETE' or 'UPDATE').

These variables can be used as follows:

BEGIN
if TG_OP = ''UPDATE'' and TG_WHEN = ''BEFORE'' then
   action 1
   return NEW;
end if;
if TG_OP = ''UPDATE'' and TG_WHEN = ''AFTER'' then
   action 2
   RETURN NULL;
end if;
if TG_OP = ''INSERT'' and TG_WHEN = ''BEFORE'' then
   action 3
   return NEW;
end if;
if TG_OP = ''INSERT'' and TG_WHEN = ''AFTER'' then
   action 4
   RETURN NULL;
end if;
if TG_OP = ''DELETE'' and TG_WHEN = ''BEFORE'' then
   action 5
   return OLD;
end if;
if TG_OP = ''DELETE'' and TG_WHEN = ''AFTER'' then
   action 6
   RETURN NULL;
end if;
-- SHOULD NEVER GET HERE!
END;

Note:  A trigger that fires on an AFTER cannot alter the values for the
row it was fired on, so what it returns is not critical, the pgsql
documentation recommends it return NULL.
--
Mike Nolan