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