1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

procedure to get old and new column values

Discussion in 'SQL PL/SQL' started by Anish_88, Sep 19, 2011.

  1. Anish_88

    Anish_88 Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    hello everyone

    i want a procedure which gives me old and new column values which will be maintained in other table ..

    for eg :=>
    ihave a table " product" having columns as " p_id number(pk) ,
    p_name varchar2(20),
    p_qty number"

    every updation in the "product" table will insert the old and new values in "Audit_product_table"

    which is having columns as " aud_id number (pk),
    Table_name number,
    p_id number references product( p_id),
    field_name varchar2(20),
    old_value varchar2(20),
    new_value varchar2(20),"

    Thank you in Advance.
    Anish
     
  2. robin

    robin Forum Advisor

    Messages:
    55
    Likes Received:
    7
    Trophy Points:
    160
    Location:
    Hyderabad
    Re: procedure to get old and new column values

    Dept(dname,id,location)

    Depart(newname,oldname)

    Trigger:


    create or replace trigger up_dept
    before update on dept
    for each row
    begin
    insert into depart values:)new.dname,:Old.dname);
    end;
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This is a variation on transactional history; normally in such cases one would log the old row, the new row, the date of the change and which user made the change.

    Why is table_name declared as a number? Should it not be a varchar2(8)? Why have it at all since the only table you're monitoring is the PRODUCT table?

    The following example might help you:

    Code (SQL):
    SQL> CREATE TABLE product(
      2          p_id NUMBER,
      3          p_name varchar2(20),
      4          p_qty NUMBER,
      5          CONSTRAINT product_pk
      6          PRIMARY KEY(p_id));
    TABLE created.
    SQL>
    SQL> BEGIN
      2          FOR i IN 1..50 loop
      3                  INSERT INTO product
      4                  (p_id, p_name, p_qty)
      5                  VALUES
      6                  (i, to_char(to_date(i, 'j'), 'Jsp'), 50*i);
      7          END loop;
      8
      9          commit;
     10
     11  END;
     12  /
    PL/SQL PROCEDURE successfully completed.
    SQL>
    SQL> CREATE TABLE audit_product_table(
      2          aud_id NUMBER,
      3          TABLE_NAME varchar2(8) DEFAULT 'PRODUCT',
      4          p_id NUMBER,
      5          field_name varchar2(20),
      6          old_value varchar2(20),
      7          new_value varchar2(20),
      8          CONSTRAINT autid_prd_tab_pk
      9          PRIMARY KEY (aud_id),
     10          CONSTRAINT audit_prd_tab_fk
     11          FOREIGN KEY (p_id)
     12          REFERENCES product);
    TABLE created.
    SQL>
    SQL> CREATE SEQUENCE audit_prd_tab_seq
      2  START WITH 1 INCREMENT BY 1 nomaxvalue nocycle ORDER;
    SEQUENCE created.
    SQL>
    SQL> CREATE OR REPLACE TRIGGER product_audit_trg
      2  after UPDATE ON product
      3  FOR each ROW
      4  BEGIN
      5          IF :NEW.p_name <> nvl(:OLD.p_name, 'TEST') THEN
      6                  INSERT INTO audit_product_table
      7                  (aud_id, p_id, field_name, old_value, new_value)
      8                  VALUES
      9                  (audit_prd_tab_seq.NEXTVAL, :NEW.p_id, 'P_NAME', :OLD.p_name, :NEW.p_name);
     10          elsif :NEW.p_qty <> :OLD.p_qty THEN
     11                  INSERT INTO audit_product_table
     12                  (aud_id, p_id, field_name, old_value, new_value)
     13                  VALUES
     14                  (audit_prd_tab_seq.NEXTVAL, :NEW.p_id, 'P_QTY', :OLD.p_qty, :NEW.p_qty);
     15          END IF;
     16
     17  END;
     18  /
    TRIGGER created.
    SQL>
    SQL> UPDATE product
      2  SET p_qty = p_qty*5
      3  WHERE p_id IN (1,5,10,20,45);
    5 ROWS updated.
    SQL>
    SQL> commit;
    Commit complete.
    SQL>
    SQL> SELECT * FROM audit_product_table;
        AUD_ID TABLE_NA       P_ID FIELD_NAME           OLD_VALUE            NEW_VALUE
    ---------- -------- ---------- -------------------- -------------------- --------------------
             1 PRODUCT           1 P_QTY                50                   250
             2 PRODUCT           5 P_QTY                250                  1250
             3 PRODUCT          10 P_QTY                500                  2500
             4 PRODUCT          20 P_QTY                1000                 5000
             5 PRODUCT          45 P_QTY                2250                 11250
    SQL>
    SQL> UPDATE product
      2  SET p_name = p_name||' Test'
      3  WHERE p_id IN (4,15,27,49);
    4 ROWS updated.
    SQL>
    SQL> commit;
    Commit complete.
    SQL>
    SQL> SELECT * FROM audit_product_table;
        AUD_ID TABLE_NA       P_ID FIELD_NAME           OLD_VALUE            NEW_VALUE
    ---------- -------- ---------- -------------------- -------------------- --------------------
             1 PRODUCT           1 P_QTY                50                   250
             2 PRODUCT           5 P_QTY                250                  1250
             3 PRODUCT          10 P_QTY                500                  2500
             4 PRODUCT          20 P_QTY                1000                 5000
             5 PRODUCT          45 P_QTY                2250                 11250
             6 PRODUCT           4 P_NAME               Four                 Four Test
             7 PRODUCT          15 P_NAME               Fifteen              Fifteen Test
             8 PRODUCT          27 P_NAME               Twenty-Seven         Twenty-Seven Test
             9 PRODUCT          49 P_NAME               Forty-Nine           Forty-Nine Test
    9 ROWS selected.
    SQL>
     
     
  4. Anish_88

    Anish_88 Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Thanks
    but if their is any to do this by procedure way,
    because their 3 tables are linked with it
    first is "Audit Transaction" from this we get transaction ID and this ID is passes to
    second table i.e "Audit_table" and from this we get Table_ID and this ID is passes to
    Third Table i.e "Audit_field " .
    so writing trigger it will not work for me ..
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This new description differs greatly from the original problem statement; you need to provide an ACCURATE and COMPLETE problem statement if you want any sort of usable assistance.

    Post the EXACT requirements for this; you cannot use a procedure as you cannot trap the old and new values outside of a trigger, unless you populate a staging table with this information (yes, using a trigger) then run your procedure against the staging table.
     
  6. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    The workaround could be use of a procedure that accepts the parameters for update, retrieves the current values , update the table and then populating relevant audit tables in the same procedure. But you need to be more specific with the issues you face before anybody could help you in the way you need.