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!

Staging Table query

Discussion in 'SQL PL/SQL' started by kraxieboi, Mar 7, 2013.

  1. kraxieboi

    kraxieboi Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hi, I need help creating a query to pull the journal transactions (from all journal sources) coming in and out the staging table prior to posting to the general ledgers. I'm looking to validate that all transactions that enter the staging table are not modify before they are posted to the GL during a specific period of time. Is there a way to capture a historical log of the in and outs into the staging table? Thank you for your help.
     
  2. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    You could post triggers on the table...but that might get really expensive. Is this a temporary thing you're looking to do?
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    As I read this the idea is to set up a mechanism to capture data changes on a specific table. It would take one trigger and one table to put this into action; here's an example:

    Code (SQL):
    SQL> CREATE TABLE part (
      2  part_ser_no NUMBER NOT NULL,
      3  part_no NUMBER NOT NULL,
      4  part_desc varchar2(40) NOT NULL,
      5  part_cond varchar2(10) NOT NULL,
      6  part_hours NUMBER NOT NULL,
      7  part_cycles NUMBER NOT NULL,
      8  CONSTRAINT part_pk_ser PRIMARY KEY(part_ser_no, part_no));
     
    TABLE created.
     
    SQL>
    SQL> CREATE UNIQUE INDEX part_ser_uniq
      2  ON part(part_ser_no);
     
    INDEX created.
     
    SQL>
    SQL> CREATE TABLE engine(
      2  eng_ser_no NUMBER NOT NULL,
      3  eng_hours  NUMBER NOT NULL,
      4  eng_cycles NUMBER NOT NULL,
      5  eng_part_no NUMBER NOT NULL,
      6  eng_part_ser NUMBER NOT NULL,
      7  eng_last_maint_dt DATE NOT NULL,
      8  eng_last_mechanic NUMBER NOT NULL,
      9  CONSTRAINT eng_pk_ser PRIMARY KEY(eng_ser_no),
     10  CONSTRAINT eng_part_fk FOREIGN KEY(eng_part_ser, eng_part_no) REFERENCES part);
     
    TABLE created.
     
    SQL>
    SQL> CREATE TABLE part_hist (
      2  part_ser_no NUMBER NOT NULL,
      3  part_no NUMBER NOT NULL,
      4  part_desc varchar2(40) NOT NULL,
      5  part_cond varchar2(10) NOT NULL,
      6  part_hours NUMBER NOT NULL,
      7  part_cycles NUMBER NOT NULL,
      8  username varchar2(30),
      9  mod_dt     DATE,
     10  action varchar2(6));
     
    TABLE created.
     
    SQL>
    SQL> CREATE TABLE engine_hist (
      2  eng_ser_no NUMBER NOT NULL,
      3  eng_hours  NUMBER NOT NULL,
      4  eng_cycles NUMBER NOT NULL,
      5  eng_part_no NUMBER NOT NULL,
      6  eng_part_ser NUMBER NOT NULL,
      7  eng_last_maint_dt DATE NOT NULL,
      8  eng_last_mechanic NUMBER NOT NULL,
      9  username varchar2(30),
     10  mod_dt     DATE,
     11  action varchar2(6));
     
    TABLE created.
     
    SQL>
    SQL> CREATE OR REPLACE TRIGGER part_hist_trg
      2  after INSERT OR UPDATE OR DELETE
      3  ON part
      4  FOR each ROW
      5  BEGIN
      6          IF inserting THEN
      7                  INSERT INTO part_hist
      8                  (
      9                          part_ser_no ,
     10                          part_no ,
     11                          part_desc ,
     12                          part_cond ,
     13                          part_hours ,
     14                          part_cycles ,
     15                          username ,
     16                          mod_dt ,
     17                          action
     18                  )
     19                  VALUES
     20                  (
     21                          :NEW.part_ser_no ,
     22                          :NEW.part_no    ,
     23                          :NEW.part_desc ,
     24                          :NEW.part_cond ,
     25                          :NEW.part_hours ,
     26                          :NEW.part_cycles ,
     27                          USER ,
     28                          sysdate ,
     29                          'INSERT'
     30                  );
     31          elsif updating THEN
     32                  INSERT INTO part_hist
     33                  (
     34                          part_ser_no ,
     35                          part_no ,
     36                          part_desc ,
     37                          part_cond ,
     38                          part_hours ,
     39                          part_cycles ,
     40                          username ,
     41                          mod_dt ,
     42                          action
     43                  )
     44                  VALUES
     45                  (
     46                          :NEW.part_ser_no ,
     47                          :NEW.part_no    ,
     48                          :NEW.part_desc ,
     49                          :NEW.part_cond ,
     50                          :NEW.part_hours ,
     51                          :NEW.part_cycles ,
     52                          USER ,
     53                          sysdate ,
     54                          'UPDATE'
     55                  );
     56          elsif deleting THEN
     57                  INSERT INTO part_hist
     58                  (
     59                          part_ser_no ,
     60                          part_no ,
     61                          part_desc ,
     62                          part_cond ,
     63                          part_hours ,
     64                          part_cycles ,
     65                          username ,
     66                          mod_dt ,
     67                          action
     68                  )
     69                  VALUES
     70                  (
     71                          :OLD.part_ser_no ,
     72                          :OLD.part_no    ,
     73                          :OLD.part_desc ,
     74                          :OLD.part_cond ,
     75                          :OLD.part_hours ,
     76                          :OLD.part_cycles ,
     77                          USER ,
     78                          sysdate ,
     79                          'DELETE'
     80                  );
     81          END IF;
     82  END;
     83  /
     
    TRIGGER created.
     
    SQL>
    SQL> SHOW errors

    No errors.

    SQL>
    SQL> CREATE OR REPLACE TRIGGER engine_hist_trg
      2  after INSERT OR UPDATE OR DELETE
      3  ON engine
      4  FOR each ROW
      5  BEGIN
      6          IF inserting THEN
      7                  INSERT INTO engine_hist
      8                  (
      9                          eng_ser_no ,
     10                          eng_hours  ,
     11                          eng_cycles ,
     12                          eng_part_no ,
     13                          eng_part_ser ,
     14                          eng_last_maint_dt ,
     15                          eng_last_mechanic ,
     16                          username ,
     17                          mod_dt  ,
     18                          action
     19                  )
     20                  VALUES
     21                  (
     22                          :NEW.eng_ser_no ,
     23                          :NEW.eng_hours  ,
     24                          :NEW.eng_cycles ,
     25                          :NEW.eng_part_no ,
     26                          :NEW.eng_part_ser ,
     27                          :NEW.eng_last_maint_dt ,
     28                          :NEW.eng_last_mechanic ,
     29                          USER ,
     30                          sysdate ,
     31                          'INSERT'
     32                  );
     33          elsif updating THEN
     34                  INSERT INTO engine_hist
     35                  (
     36                          eng_ser_no ,
     37                          eng_hours  ,
     38                          eng_cycles ,
     39                          eng_part_no ,
     40                          eng_part_ser ,
     41                          eng_last_maint_dt ,
     42                          eng_last_mechanic ,
     43                          username ,
     44                          mod_dt  ,
     45                          action
     46                  )
     47                  VALUES
     48                  (
     49                          :NEW.eng_ser_no ,
     50                          :NEW.eng_hours  ,
     51                          :NEW.eng_cycles ,
     52                          :NEW.eng_part_no ,
     53                          :NEW.eng_part_ser ,
     54                          :NEW.eng_last_maint_dt ,
     55                          :NEW.eng_last_mechanic ,
     56                          USER ,
     57                          sysdate ,
     58                          'UPDATE'
     59                  );
     60          elsif deleting THEN
     61                  INSERT INTO engine_hist
     62                  (
     63                          eng_ser_no ,
     64                          eng_hours  ,
     65                          eng_cycles ,
     66                          eng_part_no ,
     67                          eng_part_ser ,
     68                          eng_last_maint_dt ,
     69                          eng_last_mechanic ,
     70                          username ,
     71                          mod_dt  ,
     72                          action
     73                  )
     74                  VALUES
     75                  (
     76                          :OLD.eng_ser_no ,
     77                          :OLD.eng_hours  ,
     78                          :OLD.eng_cycles ,
     79                          :OLD.eng_part_no ,
     80                          :OLD.eng_part_ser ,
     81                          :OLD.eng_last_maint_dt ,
     82                          :OLD.eng_last_mechanic ,
     83                          USER ,
     84                          sysdate ,
     85                          'DELETE'
     86                  );
     87          END IF;
     88  END;
     89  /
     
    TRIGGER created.
     
    SQL>
    SQL> SET echo off

    No errors.
     
    1 ROW created.
     
    Elapsed: 00:00:00.01
     
    1 ROW created.
     
    Elapsed: 00:00:00.03
     
    Commit complete.

    PART_SER_NO    PART_NO PART_DESC                                PART_COND  PART_HOURS PART_CYCLES
    ----------- ---------- ---------------------------------------- ---------- ---------- -----------
              1        432 Flarp flap                               Decent             12           8

    ENG_SER_NO  ENG_HOURS ENG_CYCLES ENG_PART_NO ENG_PART_SER ENG_LAST_ ENG_LAST_MECHANIC
    ---------- ---------- ---------- ----------- ------------ --------- -----------------
       9999999         44         32         432            1 08-MAR-13                56

    PART_SER_NO    PART_NO PART_DESC                                PART_COND  PART_HOURS PART_CYCLES USERNAME
    ----------- ---------- ---------------------------------------- ---------- ---------- ----------- ------------------------------
    MOD_DT    ACTION
    --------- ------
              1        432 Flarp flap                               Decent             12           8 BING
    08-MAR-13 INSERT
     
    ENG_SER_NO  ENG_HOURS ENG_CYCLES ENG_PART_NO ENG_PART_SER ENG_LAST_ ENG_LAST_MECHANIC USERNAME               MOD_DT
    ---------- ---------- ---------- ----------- ------------ --------- ----------------- ------------------------------ ---------
    ACTION
    ------
       9999999         44         32         432            1 08-MAR-13                56 BING                   08-MAR-13
    INSERT

    SQL>
    Notice the elapsed times -- they are not excessive. I have implemented such transactional history for systems including a heavily transacted application for a major airline that met FAA specifications for transactional tracking and it didn't noticeably slow down processing -- and this was with Oracle 7.3.4, back in the 1990's. That system is still in use today, with more modern versions of Oracle, and it hasn't had issues with overly long transaction times with any release of Oracle.
     
  4. kraxieboi

    kraxieboi Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Yes, it's a one time thing we need to do to validate the information that entered the staging table was unmodified before it gets posted to the GL.

    Thanks for the help Zargon. Much appreciated.