From: | Donald Brady <my_dev_email(at)yahoo(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | trigger help |
Date: | 2006-05-28 06:02:02 |
Message-ID: | 20060528060204.80724.qmail@web55614.mail.re4.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Pierre Thibaudeau | 2006-05-28 21:17:12 | Concatenation containing a "null" term |
Previous Message | Jim Brown | 2006-05-27 20:38:23 | a MySQL to PostgreSQL successful port, but performance is bad... |