Assigning NEW. anomoly
От | Little, Douglas |
---|---|
Тема | Assigning NEW. anomoly |
Дата | |
Msg-id | A434C531E37AD442815608A769550D8059422B9806@EGEXCMB01.oww.root.lcl обсуждение исходный текст |
Список | pgsql-sql |
<div class="Section1"><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size: 10.0pt;font-family:Arial;color:navy">Hello,</span></font><p class="MsoNormal"><font color="navy" face="Arial" size="2"><spanstyle="font-size: 10.0pt;font-family:Arial;color:navy"> </span></font><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size: 10.0pt;font-family:Arial;color:navy">I have a trigger function designed to encrypt source data on insert/update.</span></font><pclass="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size: 10.0pt;font-family:Arial;color:navy">I have a problem where an assignment isn’t happening, and I don’t understand why. </span></font><pclass="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size: 10.0pt;font-family:Arial;color:navy">Any thoughts</span></font><p class="MsoNormal"><font color="navy" face="Arial" size="2"><spanstyle="font-size: 10.0pt;font-family:Arial;color:navy"> </span></font><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size: 10.0pt;font-family:Arial;color:navy">In the function, I unnecessarily reset new.pii_ccard_number to null. </span></font><pclass="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size: 10.0pt;font-family:Arial;color:navy">It must be null already for the else condition to apply. </span></font><p class="MsoNormal"><fontcolor="navy" face="Arial" size="2"><span style="font-size: 10.0pt;font-family:Arial;color:navy">The problem is that the following statement NEW.pi2_pii_ccard_number=null; </span></font><pclass="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size: 10.0pt;font-family:Arial;color:navy">Doesn’t appear to be executing. After the statement completes, the pi2 column Isn’tset to null. </span></font><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size: 10.0pt;font-family:Arial;color:navy"> </span></font><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size: 10.0pt;font-family:Arial;color:navy">However when I change the function and remove the highlighted line it works settingpi2 to null.</span></font><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size: 10.0pt;font-family:Arial;color:navy">Really weird.</span></font><p class="MsoNormal"><font color="navy" face="Arial" size="2"><spanstyle="font-size: 10.0pt;font-family:Arial;color:navy">Thanks</span></font><p class="MsoNormal"><font color="navy" face="Arial" size="2"><spanstyle="font-size: 10.0pt;font-family:Arial;color:navy">Doug</span></font><p class="MsoNormal"><font color="navy" face="Arial" size="2"><spanstyle="font-size: 10.0pt;font-family:Arial;color:navy"> </span></font><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size: 10.0pt;font-family:Arial;color:navy"> </span></font><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size: 10.0pt;font-family:Arial;color:navy">CREATE OR REPLACE FUNCTION bop.amex_ccr_settlement_encrypt()</span></font><p class="MsoNormal"><fontcolor="navy" face="Arial" size="2"><span style="font-size: 10.0pt;font-family:Arial;color:navy"> RETURNS "trigger" AS</span></font><p class="MsoNormal"><font color="navy" face="Arial"size="2"><span style="font-size: 10.0pt;font-family:Arial;color:navy">$BODY$ BEGIN If NEW.pii_ccard_number is not null then </span></font><p class="MsoNormal"><fontcolor="navy" face="Arial" size="2"><span style="font-size: 10.0pt;font-family:Arial;color:navy"> NEW.pi2_pii_ccard_number=dba_work.owwencrypt(new.pii_ccard_number,new.amex_ccr_settlement_id);</span></font><p class="MsoNormal"><fontcolor="navy" face="Arial" size="2"><span style="font-size: 10.0pt;font-family:Arial;color:navy"> NEW.pii_ccard_number=''; </span></font><p class="MsoNormal"><font color="navy"face="Arial" size="2"><span style="font-size: 10.0pt;font-family:Arial;color:navy"> else </span></font><p class="MsoNormal"><font color="navy" face="Arial"size="2"><span style="font-size: 10.0pt;font-family:Arial;color:navy"> NEW.pi2_pii_ccard_number=null; </span></font><p class="MsoNormal"><b><fontcolor="navy" face="Arial" size="2"><span style="font-size:10.0pt;font-family:Arial;color:navy;font-weight:bold"> NEW.pii_ccard_number=null;</span></font></b><pclass="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size: 10.0pt;font-family:Arial;color:navy">end if; RETURN NEW; END; $BODY$</span></font><p class="MsoNormal"><font color="navy"face="Arial" size="2"><span style="font-size: 10.0pt;font-family:Arial;color:navy"> LANGUAGE 'plpgsql' VOLATILE;</span></font><p class="MsoNormal"><font face="Arial"size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">CREATE TRIGGER amex_ccr_settlement_encrypt_0306bak</span></font><p class="MsoNormal"><font face="Arial"size="2"><span style="font-size:10.0pt; font-family:Arial"> BEFORE INSERT OR UPDATE</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> ON bop.amex_ccr_settlement_0306bak</span></font><p class="MsoNormal"><font face="Arial" size="2"><spanstyle="font-size:10.0pt; font-family:Arial"> FOR EACH ROW</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> EXECUTE PROCEDURE bop.amex_ccr_settlement_encrypt();</span></font><p class="MsoNormal"><font face="Arial"size="2"><span style="font-size:10.0pt; font-family:Arial">COMMENT ON TRIGGER amex_ccr_settlement_encrypt ON bop.amex_ccr_settlement IS 'version:20100305_0912 generatedon 2010-03-06 08:01:57.836201-06';</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">update bop.amex_ccr_settlement_0306bak a</span></font><p class="MsoNormal"><font face="Arial" size="2"><spanstyle="font-size:10.0pt; font-family:Arial">set pii_ccard_number = null</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">from bop.amex_ccr_settlement_keys b</span></font><p class="MsoNormal"><font face="Arial" size="2"><spanstyle="font-size:10.0pt; font-family:Arial">where b.amex_ccr_settlement_id = a.amex_ccr_settlement_id and pi2_pii_ccard_number is not null and b.pii_ccard_numberis null</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">;</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><strong><b><font face="Arial" size="2"><span style="font-size: 10.0pt;font-family:Arial">Doug Little</span></font></b></strong><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">Sr. Data Warehouse Architect | Enterprise Data Management | Orbitz Worldwide </span></font><p class="MsoNormal"style="margin-right:-1.25in"><font face="Arial" size="2"><span style="font-size:10.0pt;font-family:Arial">500W. Madison, Suite 1000 Chicago IL 60661| Office 312.260.2588 | Fax 312.894.5164| Cell 847-997-5741</span></font><h3 style="margin:0in;margin-bottom:.0001pt"><strong><b><font face="Arial" size="2"><spanstyle="font-size:10.0pt;font-family:Arial;font-weight:normal"><a href="mailto:Douglas.Little@orbitz.com" title="mailto:dlittle@orbitz.com"><b><span style="font-weight:bold">Douglas.Little@orbitz.com</span></b></a></span></font></b></strong><span class="MsoHyperlink"><u><fontcolor="blue"></font></u></span></h3><p><font face="Times New Roman" size="3"><span style="font-size:12.0pt"> <imgborder="0" height="61" id="_x0000_i1025" src="cid:image001.jpg@01CABD15.EE7F1830" width="83"/> <a href="http://www.orbitz.com/" title="http://www.orbitz.com/"><font face="Arial" size="1"><span style="font-size: 8.0pt;font-family:Arial"><span title="http://www.orbitz.com/">orbitz.com</span></span></font></a> <font color="blue"><spanstyle="color:blue">|</span></font> <a href="http://www.ebookers.com/" title="http://www.ebookers.com/"><fontface="Arial" size="1"><span style="font-size:8.0pt;font-family:Arial"><span title="http://www.ebookers.com/">ebookers.com</span></span></font></a><font color="blue"><span style="color:blue">|</span></font><a href="http://www.hotelclub.com/" title="http://www.hotelclub.com/"><font face="Arial"size="1"><span style="font-size:8.0pt;font-family:Arial"><span title="http://www.hotelclub.com/">hotelclub.com</span></span></font></a><font color="blue"><span style="color:blue">|</span></font><a href="http://www.cheaptickets.com/" title="http://www.cheaptickets.com/"><font face="Arial"size="1"><span style="font-size:8.0pt;font-family:Arial"><span title="http://www.cheaptickets.com/">cheaptickets.com</span></span></font></a><font color="blue"><span style="color:blue">|</span></font><a href="http://www.ratestogo.com/" title="http://www.ratestogo.com/"><font face="Arial"size="1"><span style="font-size:8.0pt;font-family:Arial"><span title="http://www.ratestogo.com/">ratestogo.com</span></span></font></a><fontcolor="blue"><span style="color:blue"> |</span></font><a href="http://www.asiahotels.com/" title="http://www.asiahotels.com/"><font face="Arial" size="1"><spanstyle="font-size:8.0pt;font-family:Arial"><span title="http://www.asiahotels.com/">asiahotels.com</span></span></font></a> </span></font><pclass="MsoNormal"><font face="TimesNew Roman" size="3"><span style="font-size: 12.0pt"> </span></font></div>
В списке pgsql-sql по дате отправления: