Home » SQL & PL/SQL » SQL & PL/SQL » Please advice on this solution
Please advice on this solution [message #421992] Sat, 12 September 2009 19:49 Go to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
I have two tables, table1 and table2, note that both the tables have different columns, execpt the values in ids (tab2_id, tab1_id) match.

Purpose:

To create a history table(replica of table1), and check if the rows for matching ids, delete those rows which are there in table1 but not in table2, and insert into history table (table1_hist). This seems simple, but the problem comes with
create user, create time, mod user, mod time columns, these
are populated by triggers to log the users and times...

In my history table, there are following columns

orig_c_tm date, orig_m_tm date, orig_c_ur varchar2(40), orig_m_ur varchar2(40) ---> reflects values of table1 c_tm,m_tm,c_ur, m_ur values

c_tm date, m_tm date,c_ur varchar2(40), m_ur varchar2(40) --> these are history table columns, and are populated by the trigger
meant for history table...


here are the table creation and sample data scripts

CREATE SEQUENCE SEQ_tab2
  START WITH 1
  MAXVALUE 999999999
  MINVALUE 1
  NOCYCLE
  CACHE 20
  NOORDER
/

CREATE TABLE table2
(
  tab2_id  NUMBER                  NOT NULL,
  tab2_col1        NUMBER                  NOT NULL,
  tab2_col2              VARCHAR2(20 BYTE),c_tm date, m_tm date,c_ur varchar2(40), m_ur varchar2(40))
 /

 CREATE OR REPLACE TRIGGER trig_tab2
    BEFORE INSERT OR UPDATE OR DELETE ON table2
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
DECLARE

    

BEGIN

    

    IF inserting THEN

        IF :new.tab2_id IS NULL THEN

            SELECT  seq_tab2.NEXTVAL
            INTO    :new.tab2_id
            FROM    DUAL ;

        end if;

        :new.c_tm := sysdate ;
        :new.c_ur := user ;

    ELSIF updating THEN

        :new.m_tm := sysdate ;
        :new.m_ur := user ;

    END IF ;

    end;
    /

insert into table2 values (1,1,'one')
/
insert into table2 values (2,2,'ds')
/
insert into table2 values (3,3,'thre')
/
commit
 s   *************************************************

CREATE SEQUENCE SEQ_tab1
  START WITH 1
  MAXVALUE 999999999
  MINVALUE 1
  NOCYCLE
  CACHE 20
  NOORDER
/

CREATE TABLE table1
(
  tab1_id  NUMBER                  NOT NULL,
  tab1_col1        NUMBER                  NOT NULL,
  tab1_col2              VARCHAR2(20 BYTE))
 /

 CREATE OR REPLACE TRIGGER trig_tab1
    BEFORE INSERT OR UPDATE OR DELETE ON table1
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
DECLARE

    

BEGIN

    

    IF inserting THEN

        IF :new.tab1_id IS NULL THEN

            SELECT  seq_tab1.NEXTVAL
            INTO    :new.tab1_id
            FROM    DUAL ;

        end if;

        :new.c_tm := sysdate ;
        :new.c_ur := user ;

    ELSIF updating THEN

        :new.m_tm := sysdate ;
        :new.m_ur := user ;

    END IF ;

    end;
    /

insert into table1 values (1,1,'one')
/
insert into table1 values (2,2,'ds')
/


---

CREATE TABLE table1_hist
(
  tab1_id  NUMBER                  NOT NULL,
  tab1_col1        NUMBER                  NOT NULL,
  tab1_col2              VARCHAR2(20 BYTE),
  orig_c_tm date, orig_m_tm date, orig_c_ur varchar2(40), orig_m_ur varchar2(40), c_tm date, m_tm date,c_ur varchar2(40), m_ur varchar2(40))
  /


  CREATE OR REPLACE TRIGGER table1_hist
   BEFORE INSERT OR UPDATE
   ON table1_hist
   REFERENCING NEW AS NEW OLD AS OLD
   FOR EACH ROW
DECLARE
   v_osuser              VARCHAR2 (30);
BEGIN
     SELECT UPPER (SUBSTR (SYS_CONTEXT ('USERENV', 'OS_USER'), 1, 30))
     INTO v_osuser
     FROM DUAL;

   IF INSERTING
   THEN
      

      :NEW.c_tm  := SYSDATE;
      :NEW.c_ur := v_osuser;
   ELSIF UPDATING
   THEN
      :NEW.m_tm  := SYSDATE ;
      :NEW.m_ur := v_osuser;
   END IF;

END;
/




Here is the solution I tried

INSERT INTO table1_hist
(tab1_id, tab1_col1,tab1_col2,orig_c_tm,orig_m_tm,orig_c_ur,orig_m_ur)
select tab1_id,tab1_col1,tab1_col2,c_tm,m_tm,c_ur,m_ur from table1

(note I am capturing the table1's c_tm,m_tm....values into orig_c_tm....and
the history tabe c_tm,m_tm...will be taken care of trigger cause I need to know
at what time rows are being logged)




I know the above solution works, but I have 100 columns in each table and I can't follow the above insert into table(col, col2..)
select syntax...I am doing this because I have extra columns orig_c_tm, orig_c_ur..columns to capture table1 data...

Any other suggestion to do this ?
Re: Please advice on this solution [message #421993 is a reply to message #421992] Sat, 12 September 2009 19:56 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
I mean i cannot use insert into select * from table...because of these extra columns....this approach means if tomorrow extra columns do get added,
i have to modify the above all the time..
Re: Please advice on this solution [message #421996 is a reply to message #421992] Sat, 12 September 2009 20:42 Go to previous messageGo to next message
BlackSwan
Messages: 24908
Registered: January 2009
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

The posted code utilizes the Oracle builtin function USER.
This implies that every application user has their own Oracle schema & password.
Does the statement above reflect reality?

Is this a 2 tier or 3 tier application?

Why don't you simply modify existing code such that after
INSERT INTO TABLE1
a new line is added such that
INSERT INTO TABLE1_HIST along with the additional desired fields

[Updated on: Sat, 12 September 2009 21:12]

Report message to a moderator

Re: Please advice on this solution [message #422173 is a reply to message #421992] Mon, 14 September 2009 23:21 Go to previous message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
If I understand what you have asked then I agree, if you add a column to a table then your code needs to change, so change it.

Why are you worried about having to change code because of data model changes. This is a normal thing.

If you really want to get a handle on it, then go the extra mile and write a code generator for your triggers etc. Then when ever a change is made, you can get 99% of the work done by simply regenerating the code based on the root table definition.

Indeed, I do this myself. I have several history implementation solutions for when I do database design work. And for each I have a code generator that generates all the necessary views/synonyms/triggers that do the work for my team. It is much more reliable.

Good luck, Kevin

[Updated on: Mon, 14 September 2009 23:23]

Report message to a moderator

Previous Topic: update salary for lowest paid employee in each dept
Next Topic: System error 0
Goto Forum:
  


Current Time: Wed Sep 28 02:26:09 CDT 2016

Total time taken to generate the page: 0.13499 seconds