Home » SQL & PL/SQL » SQL & PL/SQL » Please suggest a logic for this
Please suggest a logic for this [message #423734] Sun, 27 September 2009 12:17 Go to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
I have two tables, table1, table2,

table1 --> has an audit table audit_table1 which stores the old
values of any data change

table2 has an audit table audi_table2 which stores the old values for any data change

both the above audit tables are populated by after update
triggers

Now I have a new audit table new_audit, which has to log both
the previous value and new value , table_name and column name
of the values changed so far...

CREATE TABLE table1(id NUMBER,status1 VARCHAR2(20), status2 VARCHAR2(20))
/
CREATE TABLE table2(id NUMBER,col1 VARCHAR2(20), col2 VARCHAR2(20))
/
CREATE TABLE audit_table1(aud_id NUMBER,id NUMBER,status1 VARCHAR2(20),status2 VARCHAR2(20))
/
CREATE TABLE audit_table2(aud_id NUMBER,id NUMBER,cpl1 VARCHAR2(20),col2 VARCHAR2(20))
/
CREATE TABLE new_audit(newid NUMBER, id NUMBER, table_name VARCHAR2(20),column_name VARCHAR2(20),
old_val VARCHAR2(30),new_val VARCHAR2(30))
/

INSERT INTO table1 VALUES(200,'done','wait')
/
INSERT INTO table2 VALUES(300,'start','finish')
/

INSERT INTO audit_table1 VALUES(1,200,'oldvalue','wait')
/
INSERT INTO audit_table2 VALUES(1, 300,'start','oldvalue')
/


see old values are stored in audit tables...the data should
look like this after the solution for the new audit table

(1,200,'TABLE1','STATUS1','Oldvalue','done')


also there are millions of rows in audit_table1, audit_table2, so that has to be taken into
considration

any suggestions are appreciated

[Updated on: Sun, 27 September 2009 23:31] by Moderator

Report message to a moderator

Re: Please suggest a logic for this [message #423735 is a reply to message #423734] Sun, 27 September 2009 12:43 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>any suggestions are appreciated
You need to increase the number of expected /desired records to a few more than 1 & explain in detail from where each of the values are obtained.


By SQL standards table name & column names are VARCHAR2(30).

Just mimic/modify/expend existing triggers to populate new table.
Re: Please suggest a logic for this [message #423739 is a reply to message #423734] Sun, 27 September 2009 13:06 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
my procedure needs to select from both audit_table1 and audit_table2 and insert into new audit table....

all the historical data, that is, old value and new value
for all the ids....after inserting into new audit table,

the data in the new audit table should look like what example
i gave
Re: Please suggest a logic for this [message #423741 is a reply to message #423739] Sun, 27 September 2009 13:12 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And your problem in doing this is?
For old data/changes, just select current audit tables.
For new data/changes, just do as BlackSwan said.

Regards
Michel
Re: Please suggest a logic for this [message #423742 is a reply to message #423739] Sun, 27 September 2009 13:30 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>all the historical data, that is, old value and new value
Historical audit table does NOT contain old value & new value;
which is why new table is required.

Provided examples are incomplete.
Re: Please suggest a logic for this [message #423743 is a reply to message #423734] Sun, 27 September 2009 14:15 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
this is my new table
CREATE TABLE new_audit(newid NUMBER, id NUMBER, 
table_name VARCHAR2(20),column_name VARCHAR2(20),
old_val VARCHAR2(30),new_val VARCHAR2(30))


newid --> i will say seq.nextval
id---> should reflect the id from either table1 or table2

table_name--> should be either 'table1', or 'table2'

column_name --> should be the column name that got changed

old_val --> should be the old value (getting from audit_table1
or audit_table2)

new_val --> here is the problem, a single id can be changed n number of times.....so for last record in new_audit, we should
go back to base table (table1) and then get the current value
for that column and populate here?

i am working on a procedure, (using records, plsql tables,)
but the logic seems to fail when populating new_val

[Updated on: Sun, 27 September 2009 14:17] by Moderator

Report message to a moderator

Re: Please suggest a logic for this [message #423744 is a reply to message #423743] Sun, 27 September 2009 14:22 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i am working on a procedure, (using records, plsql tables,)
but the logic seems to fail when populating new_val

Which logic?

Regards
Michel
Re: Please suggest a logic for this [message #423745 is a reply to message #423734] Sun, 27 September 2009 14:50 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
would post that in a whle
Re: Please suggest a logic for this [message #423746 is a reply to message #423743] Sun, 27 September 2009 15:09 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>old_val --> should be the old value (getting from audit_table1 or audit_table2)

for INSERT what is "old value"?
Re: Please suggest a logic for this [message #423749 is a reply to message #423734] Sun, 27 September 2009 17:03 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
ok here is an example


CREATE TABLE MYEX(id number, X VARCHAR2(30), UPD_TM DATE DEFAULT SYSDATE, UPD_USR VARCHAR2(30) DEFAULT USER)

 CREATE TABLE audit_MYEX(id number, X VARCHAR2(30), UPD_TM DATE DEFAULT SYSDATE, UPD_USR VARCHAR2(30) DEFAULT USER)
    
    
    INSERT INTO MYEX VALUES(1,'VALUE1',SYSDATE,USER)
    /
    INSERT INTO MYEX VALUES(2, 'VALUE2',SYSDATE,USER)
    /
    commit
/


    CREATE OR REPLACE TRIGGER TRIG_EX
    BEFORE INSERT OR UPDATE OR DELETE ON myex
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW

  

BEGIN

       
    IF updating OR deleting THEN

        INSERT INTO audit_MYEX
                   (id,X, UPD_TM,UPD_USR)
        VALUES     (:old.id,:old.x,:old.upd_tm,:old.upd_usr) ;

    END IF ;

END ;
/





 update myex set x = 'newvalue' where id = 1
/
 commit
/


select * from audit_myex

gives 1 and value1

now i need a procedure which would log this in a new audit table


create table audit_new(id number,table_name varchar2(20),column_name varchar2(20),NEW_VAL VARCHAR2(20), OLD_VAL VARCHAR2(20),upd_tm date,upd_usr varchar2(30))

which would log this:

1, 'MYEX','X','newvalue','VALUE1', '9/27/2009',scott


Re: Please suggest a logic for this [message #423752 is a reply to message #423749] Sun, 27 September 2009 18:09 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
What must the triggers be when base table is like following:

CREATE TABLE MYEX(id number, COL1 VARCHAR2(30),
COL2 VARCHAR2(30),
COL3 VARCHAR2(30),
UPD_TM DATE DEFAULT SYSDATE, UPD_USR VARCHAR2(30) DEFAULT USER)
Re: Please suggest a logic for this [message #423753 is a reply to message #423734] Sun, 27 September 2009 18:23 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
CREATE TABLE MYEX(id number, COL1 VARCHAR2(30),
COL2 VARCHAR2(30),
COL3 VARCHAR2(30),
UPD_TM DATE DEFAULT SYSDATE, UPD_USR VARCHAR2(30) DEFAULT USER)



CREATE OR REPLACE TRIGGER TRIG_EX
    BEFORE INSERT OR UPDATE OR DELETE ON myex
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW

  

BEGIN

       
    IF updating OR deleting THEN

        INSERT INTO audit_MYEX
                   (id,col1,col2,col3, UPD_TM,UPD_USR)
        VALUES     (:old.id,:old.col1,:old.col2,:old.col3,upd_tm,:old.upd_usr) ;

    END IF ;

END ;
/



there is an new row inserted no matter what column is inserted or deleted...

Re: Please suggest a logic for this [message #423754 is a reply to message #423753] Sun, 27 September 2009 18:55 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>there is an new row inserted no matter what column is inserted or deleted...
No disagreement here, but your implementation makes it appear that every column changes even when only 1 or 2 columns change.
Based upon the content of the audit table how do you know whether an INSERT or UPDATE occurred?


I suggest SYSDATE should be used instead of :old value.

[Updated on: Sun, 27 September 2009 19:27]

Report message to a moderator

Re: Please suggest a logic for this [message #423755 is a reply to message #423734] Sun, 27 September 2009 19:56 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
that is true, there is no way to find out which one changed
unless there is a column extra in base audit table to tell that..


i am unable to figure out the logic of how to perform this task, i am sure this new audit table will also be redundant.

can you think of a solution?


CREATE OR REPLACE PROCEDURE p_log_myex

IS


  
   v_newval    lm_mod_status_log.new_value%TYPE;
   v_errm      VARCHAR2 (300)                     := NULL;
   v_count     NUMBER                             := 0;
   v_count2    NUMBER                             := 0;
   v_trace     NUMBER := 0;
   v_1_newval   VARCHAR2(500)   := NULL;
  
   rec_mt_tab   myex%ROWTYPE := NULL;

  
   TYPE rec_mt IS RECORD (
      x varchar2(30),
      upd_tm date,
      upd_usr varchar2(30)
   );

   TYPE t_mt_rec IS TABLE OF rec_mt index by binary_integer;

   vt_mt_rec   t_mt_rec;
BEGIN
  
   SELECT lm.x, lm.upd_tm,lm.upd_usr
   BULK COLLECT INTO vt_mt_rec
     FROM myex lm,   
          hist h
    WHERE lm.id = h.id;

  
   IF vt_mt_rec.COUNT > 0
   THEN
       SELECT *
         INTO rec_mt_tab 
         FROM myex
        WHERE ID = vt_mt_rec (1).id;
        
        dbms_output.put_line('vt_mt_rec (1).id: '||vt_mt_rec (1).id);
        
        
      FOR i IN vt_mt_rec.FIRST .. vt_mt_rec.COUNT
      LOOP
         v_count := i;
        
INSERT INTO audit_new
            (ID, table_name, column_name, old_val,
             new_val, upd_tm, upd_usr
            )
     VALUES (vt_mt_rec (i).ID, 'MYEX', 'X', vt_mt_rec (i).x,
             vt_mt_rec (i + 1).x, vt_mt_rec (i).upd_tm, vt_mt_rec (i).upd_usr
            );
               
            dbms_output.put_line(v_count);
                     
          
           v_count := nvl(v_count,0) + i; 
           if mod(v_count,0) = 100 then
           commit;
           end if;                                           -- if t_logs(i)
      END LOOP;
   END IF;
    
     
     
  
   COMMIT;
   vt_mt_rec.delete;
   
-- if t_logs.count
EXCEPTION
   WHEN OTHERS
   THEN
       
      v_errm := SUBSTR (SQLERRM, 1, 250) || ' at record#: ' || v_count;

  --   END;                                                      -- FINAL END OF PROC
/
show errors
/


hist is a sample table wich has all the ids...inserted, we will delete that after the load.

i designed the above proc, but i see the foll flaws

SELECT *
INTO rec_mt_tab
FROM myex
WHERE ID = vt_mt_rec (1).id;


has to be in the loop..but i am not sure if it works

the idea is that i --> old value

i+1 --> new value...not working
Re: Please suggest a logic for this [message #423756 is a reply to message #423755] Sun, 27 September 2009 20:18 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>i+1 --> new value...not working
COMMIT within LOOP is a bad, bad idea.

True & "not working" for more than 1 reason.
Rows in a table are like balls in a basket.
Assume you select a red ball from the basket & multiple other red balls exist in basket; which is the "next" (i+1) red ball?

On possible alternative is to ignore "history" at least for now.
Concentrate on collecting new complete audit a new table going forward.

Worry about loading history as phase 2 of project.
Re: Please suggest a logic for this [message #423757 is a reply to message #423734] Sun, 27 September 2009 21:00 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
My actual task is to load historical data
Re: Please suggest a logic for this [message #423761 is a reply to message #423757] Sun, 27 September 2009 22:48 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>My actual task is to load historical data

The requirements keep changing. WHY?

[message #423734]
CREATE TABLE new_audit(newid NUMBER, id NUMBER, table_name VARCHAR2(20),column_name VARCHAR2(20),old_val VARCHAR2(30),new_val VARCHAR2(30))

[message #423749
create table audit_new(id number,table_name varchar2(20),column_name varchar2(20),NEW_VAL VARCHAR2(20), OLD_VAL VARCHAR2(20),upd_tm date,upd_usr varchar2(30))

Existing audit tables do not contain USER or timestamps.
Do you leave these fields NULL?

Re: Please suggest a logic for this [message #423762 is a reply to message #423734] Sun, 27 September 2009 22:52 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
No, I have triggers to populate them , I did not include them here
having them populated has nothing to do with the problem in hand
Re: Please suggest a logic for this [message #423763 is a reply to message #423762] Sun, 27 September 2009 23:01 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>No, I have triggers to populate them
Good luck with what ever other answers you have not yet shared.
I am tired of the games you are playing.
Re: Please suggest a logic for this [message #423774 is a reply to message #423734] Mon, 28 September 2009 00:45 Go to previous message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Can you please post a full test case, that is with more than one line per table and the result you want with these data.

Regards
Michel
Previous Topic: select query to take row value as column name
Next Topic: avoid duplicate in select statement
Goto Forum:
  


Current Time: Fri Sep 30 02:13:04 CDT 2016

Total time taken to generate the page: 0.13369 seconds