On Sunday 28 May 2006 02:02 am, Donald Brady <my_dev_email@yahoo.com> thus
communicated:
--> Hi
-->
--> I have a simple funtion and trigger that should fire
--> when I insert, update or delete a a ticket table. The
--> purpose of the trigger is to keep two total columns in
--> an associated business_day table equal to the sum of
--> all lunch and dinner tickets for that day. Trouble is
--> nothing happens. Nothing.
-->
--> Am I doing anything obviously wrong? Or how do I go
--> about debugging this.
-->
--> Any help much appreciated.
-->
--> Here is the function and trigger...
-->
--> CREATE OR REPLACE FUNCTION process_ticket() RETURNS
--> TRIGGER AS $process_ticket$
--> declare
--> l_total numeric(10,2);
--> d_total numeric(10,2);
--> business_day_pkey integer;
--> BEGIN
-->
--> IF (TG_OP = 'DELETE') THEN
--> business_day_pkey = OLD.id;
--> ELSE
--> business_day_pkey = NEW.id;
--> END IF; --
-->
--> select into l_total sum(ticket_amount) from tickets
--> where lunch_ticket = true and business_day_id =
--> business_day_pkey;
--> select into d_total sum(ticket_amount) from tickets
--> where lunch_ticket = false and business_day_id =
--> business_day_pkey;
-->
--> update business_days set lunch_sales = l_total where
--> id = business_day_pkey;
--> update business_days set dinner_sales = d_total where
--> id = business_day_pkey;
-->
--> RETURN NULL; -- result is ignored since this is an
--> AFTER trigger
--> END;
--> $process_ticket$ LANGUAGE plpgsql;
-->
--> CREATE TRIGGER ticket_trigger_i_u_d
--> AFTER INSERT OR UPDATE OR DELETE ON tickets
--> FOR EACH ROW EXECUTE PROCEDURE process_ticket();
-->
-->
--> __________________________________________________
--> Do You Yahoo!?
--> Tired of spam? Yahoo! Mail has the best spam protection around
--> http://mail.yahoo.com
-->
--> ---------------------------(end of broadcast)---------------------------
--> TIP 6: explain analyze is your friend
-->
Well, I think that your select and/or update statements are not working for
some reason. You need some error processing around these statements. Check
out the use of IF FOUND.