Assigning NEW. anomoly - Mailing list pgsql-sql

Hello,

I have a trigger function designed to encrypt source data on insert/update.

I have a problem where an assignment isn’t happening, and I don’t understand why.

Any thoughts

In the function, I unnecessarily reset new.pii_ccard_number to null. 

It must be null already for the else condition to apply.

The problem is that the following statement  NEW.pi2_pii_ccard_number=null;

Doesn’t appear to be executing.  After the statement completes, the pi2 column Isn’t set to null.

However when I change the function and remove the highlighted line it works setting pi2 to null.

Really weird.

Thanks

Doug

CREATE OR REPLACE FUNCTION bop.amex_ccr_settlement_encrypt()

  RETURNS "trigger" AS

$BODY$ BEGIN If NEW.pii_ccard_number is not null then

                 NEW.pi2_pii_ccard_number=dba_work.owwencrypt(new.pii_ccard_number,new.amex_ccr_settlement_id);

                 NEW.pii_ccard_number='';

                 else

                 NEW.pi2_pii_ccard_number=null;

                 NEW.pii_ccard_number=null;

end if; RETURN NEW; END; $BODY$

  LANGUAGE 'plpgsql' VOLATILE;

CREATE TRIGGER amex_ccr_settlement_encrypt_0306bak

  BEFORE INSERT OR UPDATE

  ON bop.amex_ccr_settlement_0306bak

  FOR EACH ROW

  EXECUTE PROCEDURE bop.amex_ccr_settlement_encrypt();

COMMENT ON TRIGGER amex_ccr_settlement_encrypt ON bop.amex_ccr_settlement IS 'version:20100305_0912 generated on 2010-03-06 08:01:57.836201-06';

update  bop.amex_ccr_settlement_0306bak a

set pii_ccard_number = null

from bop.amex_ccr_settlement_keys b

where  b.amex_ccr_settlement_id = a.amex_ccr_settlement_id and pi2_pii_ccard_number is not null and b.pii_ccard_number is null

;

Doug Little

Sr. Data Warehouse Architect | Enterprise Data Management | Orbitz Worldwide

500 W. Madison, Suite 1000  Chicago IL 60661| Office 312.260.2588 | Fax 312.894.5164 | Cell 847-997-5741

Douglas.Little@orbitz.com

   orbitz.com | ebookers.com | hotelclub.com | cheaptickets.com | ratestogo.com | asiahotels.com