Home » SQL & PL/SQL » SQL & PL/SQL » Viewing the old and new value in the local area of a trigger
Viewing the old and new value in the local area of a trigger [message #276150] Wed, 24 October 2007 02:30 Go to next message
achilleus
Messages: 1
Registered: October 2007
Junior Member
consider the trigger below,
CREATE OR REPLACE TRIGGER PPMAPP.PPMCR_HH_CHR_TRG
   AFTER UPDATE
   ON PPMCR_STEN.PPMCR_HH_CHARACTERISTICS
   REFERENCING NEW AS POST OLD AS PRE
   FOR EACH ROW
DECLARE
   MKT_AREAGNMBR   VARCHAR2(6);
   HH_ATT_VAR      VARCHAR2(75);

   CURSOR HH_ATTR_CSR
   IS
      SELECT SUBSTR(PMC.CHRSTC_NAME, INSTR(PMC.CHRSTC_NAME, '.') + 1)
      FROM   PPMCR_STEN.PPMCR_MKT_CHRTSTC PMC
      WHERE  PMC.MKT_AREA_GNMBR = MKT_AREAGNMBR AND PMC.CHRSTC_NAME LIKE '%_HH_%';
BEGIN
   OPEN HH_ATTR_CSR;

   LOOP
      FETCH HH_ATTR_CSR
      INTO  HH_ATT_VAR;

      EXIT WHEN HH_ATTR_CSR%NOTFOUND;

      IF NVL(:PRE.HH_ATT_VAR, 'NULL') != NVL(:POST.HH_ATT_VAR, 'NULL')
      THEN
         INSERT INTO PPMCR_STEN.PPMCR_HH_CHRTCS_AUDIT
                     ( HOUSEHOLD_ID
                     , ATTRIBUTE, PREVIOUS_VALUE, NEW_VALUE
                     , MODIFICATION_DATE, SURVEY_ID
                     )
         VALUES      ( TO_CHAR(:PRE.H_HOUSEHOLD_ID)
                     , 'HH_ATT_VAR', TO_CHAR(PRE.HH_ATT_VAR)
                     , TO_CHAR(POST.HH_ATT_VAR)
                     , :POST.UPDATE_ETL_TIMESTAMP
                     , TO_CHAR(:PRE.S_SURVEY_ID)
                     );
      END IF;
   END LOOP;

   CLOSE HH_ATTR_CSR;
END PPMCR_HH_CHR_TRG;
/

The cursor HH_ATTR_CSR returns a set of values and I'm iterating each values using a loop, but when comparing the post and pre values, I have to use the variables instead of column names.Usually we give it as (:pre.XXXX_YYYY) but the cloumn names has to be given in the form of a variable got from the cursor like (:pre.HH_ATT_VAR).In doing so, I'm getting an error as "bad bind variable"
So,Is there any to view the old and the new value in the local?

[mod-edit]Formatted code. Do so yourself next time, please.

[Updated on: Wed, 24 October 2007 02:42] by Moderator

Report message to a moderator

Re: Viewing the old and new value in the local area of a trigger [message #276157 is a reply to message #276150] Wed, 24 October 2007 03:04 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't do this.
And having column name as data in a table is surely a bad and not scalable design.

Regards
Michel
Previous Topic: Subtract two dates to get years,months and days
Next Topic: Enforcing a processing time limit
Goto Forum:
  


Current Time: Mon Dec 05 21:24:36 CST 2016

Total time taken to generate the page: 0.08720 seconds