2009/12/16 Bino Oetomo <bino@xxxxxxxxxxxxxxxxxxxx>: > Dear All > > I have 2 table : > 1. hotel_pbx_country > 2. hotel_pbx_area > > Country is one2many to area > Area have a field called "prefx" > > The "prefx" field is auto filled by country.code and area.code > and for that purpose, i created trigger and function > --------Trigger------------- > CREATE TRIGGER prefx_xtrigger > BEFORE INSERT OR UPDATE > ON hotel_pbx_area > FOR EACH ROW > EXECUTE PROCEDURE prefx_xupdate(); > --------function---------------- > CREATE OR REPLACE FUNCTION prefx_xupdate() > RETURNS trigger AS $$ > DECLARE ctrcode VARCHAR ; > BEGIN > select code into ctrcode from hotel_pbx_country where "id" = > NEW.country_id ; > NEW.prefx = ctrcode || NEW.code; > RETURN NEW; > END; > $$ LANGUAGE 'plpgsql'; > ---------------EOF------------------- > > I make a bulk entry , > Not all area have area.code .. it's not mandatory > > In normal condition where both country.code and area.code is not null .... > the trigger is work great. > But the problem come when area.code is null, it cause area.prefx to be NULL > also. > > > Is it normal behavior ? or is it my fault ? Hello NULL and any is NULL. So you have to use "coalesce" function. like NEW.prefix = ctrcode || coalesce(NEW.code, ''); Regards Pavel Stehule > > Kindly please give me your enlightment on how to fix it > > Sincerely > -bino- > > -- > Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
