1 2 Previous Next 17 Replies Latest reply on Jul 16, 2019 9:28 PM by jaramill

    insert history records in one table based on update of another table

    3500862

      Hi Experts,

       

      I have 2 tables called 1. Req and 2.Req_hist

       

      I have a data in Req table like below

       

      Req Table:

      Req_no, Req_status, last_modified_date

      1,New,2019-06-23

       

      Req_hist table:

      Req_no,Req_status,last_modified_date

       

      1. How to load history records into Req_hist table based on Req table.

       

      2. Whenever status changes happened in req table values should be appended in Req_hist table with the updated value.

       

      For example:

       

      Req Table:

      Req_no, Req_status, last_modified_date

      1, Offersent , 2019-06-25

       

      Req_hist table:

      Req_no,Req_status,last_modified_date

      1, New, 2019-06-23

      1, Offersent , 2019-06-25

       

      How we have to write a query to handle this type of scenario.

       

      Kindly help on this.

      Thanks in Advance

      Visu

        • 1. Re: insert history records in one table based on update of another table
          jaramill

          Considering you have 2 other questions where you created them yet let them "unanswered" without even going back to reply back is bad form/etiquette (granted they're 2yrs old).

           

          1. how to update value based on two tables
          2. How to execute multiple insert statements from a table automatically?

           

          It makes members of this forum not want to help in general.  Just an observation.

           

          Now if you read the link from the FAQ (Frequently Asked Questions) on -->Re: 2. How do I ask a question on the forums? , please answer questions #5 through #9.

          Provide data and tables in DML and DML statements.  That helps us copy/paste to test in our own environments (include db version from question #5).

          • 2. Re: insert history records in one table based on update of another table
            jaramill

            But to answer your question, what you want is "auditing" which can be done in a few ways.  The simplest is to add a "trigger" to the base table (in your case the "Req Table").  And make it so that upon UPDATE on column, for req table, then INSERT into Req Hist table).

            • 3. Re: insert history records in one table based on update of another table
              jaramill

              Okay here's the code.  You need 3 triggers

               

              1. One for inserting new rows into req table
              2. One for updating existing rows in the req table

               

              DDL and DML statements

               

              drop table req;
              drop table req_hist;
              
              create table req
              (
               Req_no             number
              ,Req_status         varchar2(10)
              ,last_modified_date date
              );
              
              create table req_hist
              (
               Req_no             number
              ,Req_status         varchar2(10)
              ,last_modified_date date
              );
              
              create or replace trigger req_bufer1_trg
                 before insert
                 on req
                 referencing new as new old as old
                 for each row
              begin
              
                 <<conditionalpredicatecase>>
                 case
                    when inserting then
                       insert into req_hist values (:new.req_no, :new.req_status, :new.last_modified_date);
                 end case conditionalpredicatecase;
              
              end req_bufer1_trg;
              /
              
              create or replace trigger req_bufer2_trg
                 -- BUFER = Before Update For Each Row
                 before update
                 of req_status
                 on req
                 referencing new as new old as old
                 for each row
              begin
              
                 <<conditionalpredicatecase>>
                 case
                    when updating then
              
                       :new.last_modified_date := sysdate;
                       insert into req_hist values (:new.req_no, :new.req_status, :new.last_modified_date);
              
                 end case conditionalpredicatecase;
              
              end req_bufer2_trg;
              /
              
              insert into req values (1, 'New', date '2019-06-23');
              
              commit;
              
              select * from req;
              select * from req_hist;
              
              update req set req_status = 'Offersent' where req_no = 1;
              
              commit;
              
              select * from req;
              select * from req_hist;
              
              

               

              OUTPUT

               

              Time Start: 6/26/2019 1:52:39 PM
              Table dropped.
              Table dropped.
              Table created.
              Table created.
              Trigger created.
              Trigger created.
              1 row created.
              Commit complete.
              
                  REQ_NO REQ_STATUS LAST_MODIFIED_DATE
              ---------- ---------- ------------------
                       1 New        23-JUN-19         
              1 row selected.
              
                  REQ_NO REQ_STATUS LAST_MODIFIED_DATE
              ---------- ---------- ------------------
                       1 New        23-JUN-19         
              1 row selected.
              1 row updated
              
              Commit complete.
              
                  REQ_NO REQ_STATUS LAST_MODIFIED_DATE
              ---------- ---------- ------------------
                       1 Offersent  26-JUN-19         
              
              1 row selected.
              
                  REQ_NO REQ_STATUS LAST_MODIFIED_DATE
              ---------- ---------- ------------------
                       1 New        23-JUN-19         
                       1 Offersent  26-JUN-19         
              
              2 rows selected.
              Time End: 6/26/2019 1:52:42 PM
              Elapsed Time for Script Execution: 2 secs
              
              1 person found this helpful
              • 4. Re: insert history records in one table based on update of another table
                3500862

                Thank you so much for your help Jaramill. I will apply code and let you know the response

                • 5. Re: insert history records in one table based on update of another table
                  Cookiemonster76

                  Those CASE statements aren't necessary, you will always be inserting in a before insert trigger.

                  They would only be useful if you combined the two triggers into one before insert or update trigger.

                  • 6. Re: insert history records in one table based on update of another table
                    jaramill

                    Cookiemonster76 wrote:

                     

                    Those CASE statements aren't necessary, you will always be inserting in a before insert trigger.

                    They would only be useful if you combined the two triggers into one before insert or update trigger.

                    Ah thanks, I actually used that in my code at work and I see now it's redundant but for now it doesn't hurt.  Will clean it up later.

                    • 7. Re: insert history records in one table based on update of another table
                      Cookiemonster76

                      It can't hurt (the performance impact isn't going to be measurable).

                       

                      If you do it as a single trigger it simplifies to this:

                      create or replace trigger req_bufer1_trg 
                        before insert OR UPDATE
                        on req 
                        referencing new as new old as old 
                        for each row 
                      begin 
                      
                        IF updating then 
                      
                          :new.last_modified_date := sysdate; 
                      
                        END IF; 
                      
                        insert into req_hist (Req_no, Req_status, last_modified_date)
                        values (:new.req_no, :new.req_status, :new.last_modified_date);
                      
                      end req_bufer1_trg; 
                      / 
                      
                      • 8. Re: insert history records in one table based on update of another table
                        jaramill

                        Ah I see it...."before insert or update".   Combined to eliminate 1 less trigger, and I'm all for reducing the number of triggers.

                         

                        To the OP (original poster) 3500862, modify trigger 1 ("req_bufer1_trg")  using Cookiemonster76's change and get rid of trigger 2 ("req_bufer2_trg").

                        FYI - "bufer" stands for "Before Update For Each Row"

                        • 9. Re: insert history records in one table based on update of another table
                          Cookiemonster76

                          It made sense to combine them in this case since there was little difference in what they were doing.

                          If there are significant differences I'd have separate triggers.

                          • 10. Re: insert history records in one table based on update of another table
                            Cookiemonster76

                            I generally name them:

                            table_name_<action(I(nsert), U(pdate), D(elete))><(B(efore)|A(fter)><R|S (for each row | statement)>

                             

                            So this would be req_IUBR

                             

                            I put action first so that ones for the same action group together, but really it's a matter of taste.

                            • 11. Re: insert history records in one table based on update of another table
                              jaramill

                              Cookiemonster76 wrote:

                               

                              It made sense to combine them in this case since there was little difference in what they were doing.

                              If there are significant differences I'd have separate triggers.

                              This will help me as a few of the tables I'm working with have these triggers in addition to ones that just do updates only so perhaps I can eliminate a few when cleaning up the "technical debt" I've incurred.

                               

                              Cookiemonster76 wrote:

                               

                              I generally name them:

                              table_name_<action(I(nsert), U(pdate), D(elete))><(B(efore)|A(fter)><R|S (for each row | statement)>

                               

                              So this would be req_IUBR

                               

                              I put action first so that ones for the same action group together, but really it's a matter of taste.

                               

                              Agreed.  I think I got that nomenclature from "Ask Tom" (aka Tom Kyte) website.

                              • 12. Re: insert history records in one table based on update of another table
                                Mark D Powell

                                All, I generally prefer that we maintain a separate trigger for each action (insert, update, delete) however there are times when combining the actions into one trigger makes sense.  It is also possible to have multiple triggers of the same type on the same table, say one producing a history row while the second of the same type updates a third table.  The trigger organization should be based on the business logic taking into consideration maintenance requirements.

                                - -

                                HTH -- Mark D Powell --

                                • 13. Re: insert history records in one table based on update of another table
                                  Mike Kutz

                                  Have you considered:

                                   

                                  Flashback Data Archive is included in all editions starting with 11.2.0.4

                                   

                                  My $0.02

                                   

                                  MK

                                  • 14. Re: insert history records in one table based on update of another table
                                    Cookiemonster76

                                    I'll just expand on that:

                                     

                                    There's two (potentially) competing factors here:

                                     

                                    Readability - generally when you're looking at triggers you want to see all the things that happen when a particular action is done on the table (and yes sometimes you need the whole view)

                                    So you don't want lots of IF statements on the triggering action.

                                     

                                    Maintainability - a factor of readability, but also code duplication. If you've got the same code in multiple triggers then when you have to change it in one you'll probably have to change it in all.

                                    So you want to avoid code duplication as much as possible.

                                    In some cases you can solve the code duplication by refactoring the code out into a separate procedure/function and if you can you should.

                                     

                                    If there's lots of duplication you almost certainly want one trigger

                                    If there's very little you want multiple

                                    The dividing line is really a matter of personal preference.

                                     

                                    And I agree with Marks suggestion of multiple triggers of the same type if you end up with large chunks of unrelated code in the same trigger, though I would first look to see if most of it couldn't be moved out into procedures.

                                    1 2 Previous Next