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.lit...@orbitz.com<mailto:douglas.lit...@orbitz.com>
[cid:image001.jpg@01CABD15.EE7F1830] orbitz.com<http://www.orbitz.com/> |
ebookers.com<http://www.ebookers.com/> |
hotelclub.com<http://www.hotelclub.com/> |
cheaptickets.com<http://www.cheaptickets.com/> |
ratestogo.com<http://www.ratestogo.com/> |
asiahotels.com<http://www.asiahotels.com/>
<<inline: image001.jpg>>
