Q
Problem solve Get help with specific problems with your technologies, process and projects.

Logging changes to row content, part 2

Here is the remaining code about how to log changes to row content.
 v_SINGLE_QUOTE CHAR(1):=  CHR(39);
 vSep VARCHAR2(10);



BEGIN
  DBMS_OUTPUT.PUT_LINE('CREATE OR REPLACE TRIGGER ' || GENERATE_TRIG_NAME(p_Table_Name));
  DBMS_OUTPUT.PUT_LINE('  AFTER UPDATE ON ' || p_Table_Name);
  DBMS_OUTPUT.PUT_LINE('  FOR EACH ROW');
  DBMS_OUTPUT.PUT_LINE('DECLARE ');
  DBMS_OUTPUT.PUT_LINE('  vTABLE_NAME     VARCHAR2(30) := ' || v_SINGLE_QUOTE || p_Table_Name || v_SINGLE_QUOTE || ';');
  -- Create Key_Value
  DBMS_OUTPUT.PUT_LINE('  vKEY_VALUE      VARCHAR2(100) ');
  FOR c1rec IN c1(p_Table_Name) LOOP
    IF c1rec.POSITION = C1rec.LAST_POSITION THEN
      vSep:=';';
    ELSE
      vSep:='||''~''|| ';
    END IF;
    IF c1rec.data_type = 'DATE' THEN
       DBMS_OUTPUT.PUT_LINE('                  to_char(:new.'||c1rec.column_name||',''MM-DD-YYYY'')' || vSep );
    ELSIF c1rec.data_type = 'NUMBER' THEN
       DBMS_OUTPUT.PUT_LINE('                  to_char(:new.'||c1rec.column_name||')' || vSep );
    ELSE
       DBMS_OUTPUT.PUT_LINE('                  :new.'||c1rec.column_name || vSep );
    END IF;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('  vACTION_DATE    DATE := SYSDATE');
  DBMS_OUTPUT.PUT_LINE('  vUSER_NAME      VARCHAR2(30) := USER');
  DBMS_OUTPUT.PUT_LINE('BEGIN');

  FOR c2rec IN c2(p_Table_Name) LOOP
    IF c2rec.column_id > 1 THEN
      BEGIN
        DBMS_OUTPUT.PUT_LINE('  log_change_info (vTABLE_NAME, vKEY_VALUE, vACTION_DATE, vUSER_NAME, ' );
        DBMS_OUTPUT.PUT_LINE('                      '||v_SINGLE_QUOTE||c2rec.COLUMN_NAME||v_SINGLE_QUOTE||', :old.'||c2rec.COLUMN_NAME||', :new.'||c2rec.COLUMN_NAME||');' );
      EXCEPTION
        WHEN OTHERS THEN
          dbms_output.put_line('**** ');
          dbms_output.put_line('**** Error formatting column: '||c2rec.column_ID);
          dbms_output.put_line('**** ');
      END;
    END IF;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('end;');
  DBMS_OUTPUT.PUT_LINE('/');

--EXCEPTION
--  WHEN OTHERS THEN
--    NULL;
END CREATE_AUDIT_TRIG_DYNAMIC;
/

 
CREATE OR REPLACE PROCEDURE log_change_info   (vTABLE_NAME    IN  VARCHAR2,
                            vKEY_VALUE     IN  VARCHAR2,
                            dACTION_DATE   IN  DATE,
                            vUSER_NAME     IN  VARCHAR2,
                            vCOLUMN_NAME   IN  VARCHAR2,
                            vOLD_VALUE     IN  VARCHAR2,
                            vNEW_VALUE     IN  VARCHAR2) IS
BEGIN
 IF (vNEW_VALUE IS NULL AND vOLD_VALUE IS NOT NULL) OR
     (vNEW_VALUE != vOLD_VALUE) OR
     (vNEW_VALUE IS NOT NULL AND vOLD_VALUE IS NULL) THEN
    INSERT INTO DATA_CHANGE_AUDIT
               (table_name,
                primary_key_value,
                column_name,
                action_date,
                old_value,
                new_value,
                user_name)
        VALUES (vTABLE_NAME,
                vKEY_VALUE,
                vCOLUMN_NAME,
                NVL(dACTION_DATE,SYSDATE),
                vOLD_VALUE,
                vNEW_VALUE,
                NVL(vUSER_NAME,USER));
  END IF;
END log_change_info;
/

This was last published in October 2004

Dig Deeper on Using Oracle PL-SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close