Goboxe wrote:
Hi,
I have a trigger as below.
I am wondering why when I tried to insert to master table with date=
20080908,
the trigger does not insert to z_agg_tmcarr_pfx_gtwy_cc_w_20080908.
First, check using \d tablename to make sure that the trigger is
actually defined on the table. Could you have forgotten to CREATE TRIGGER ?
If it is, you might want to add a RAISE NOTICE statement immediately
after the BEGIN block so you can tell that the trigger its self is in
fact running. Eg:
RAISE NOTICE 't_agg_tmcarr_pfx_gtwy_cc() fired on %',TG_OP
then reload the function and re-test. Be sure to test using psql where
you'll actually see the notice.
If that RAISE NOTICE doesn't fire, make sure the trigger is not set to
disabled. Also check that it's not DEFERRABLE INITIALLY DEFERRED, as it
might be firing just not when you're expecting it to.
Also, note that if your trigger is cancelling all operations on the
table it's attached to, including UPDATE and DELETE if it's fired on
those events. I assume it's for partitioning? If so, you should probably
handle the UPDATE and DELETE cases even if you just 'RAISE EXCEPTION' to
indicate that they're not permitted, or should take the appropriate
action to relocate/remove records.
Personally, I'd also be explicit about my dates. Instead of:
20080901
I prefer
DATE '2008-09-01'
eg:
IF NEW.CallDate BETWEEN DATE '2008-09-01' AND DATE '2008-09-07' THEN
(note that BETWEEN is _inclusive_ on both bounds, and is a bit nicer
than repeating the NEW.CallDate expression for both bounds).
--------------------------------------------------
CREATE OR REPLACE FUNCTION t_agg_tmcarr_pfx_gtwy_cc()
RETURNS "trigger" AS
$BODY$
DECLARE
BEGIN
IF (TG_OP = 'INSERT') THEN
IF NEW.CallDate >= 20080901 AND NEW.CallDate <= 20080907 THEN
INSERT INTO z_agg_tmcarr_pfx_gtwy_cc_w_20080901 VALUES ( NEW.* );
ELSEIF NEW.CallDate >= 20080908 AND NEW.CallDate <= 20080914 THEN
INSERT INTO z_agg_tmcarr_pfx_gtwy_cc_w_20080908 VALUES ( NEW.* );
ELSEIF NEW.CallDate >= 20080915 AND NEW.CallDate <= 20080921 THEN
INSERT INTO z_agg_tmcarr_pfx_gtwy_cc_w_20080915 VALUES ( NEW.* );
END IF;
END IF;
RETURN NULL;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION t_agg_tmcarr_pfx_gtwy_cc() OWNER TO sa;
