Forum Stats

  • 3,827,381 Users
  • 2,260,767 Discussions
  • 7,897,222 Comments

Discussions

Scan Table for Value Change(s)

Jeremy McNally
Jeremy McNally Member Posts: 35
edited Jul 22, 2015 9:03AM in SQL & PL/SQL

Table A has the values below for example:  As soon as a change takes place in the PARAM_VALUE column (for the PARAM_NAME called "LINE")...  I need to be able to know right away and perform my logic on that change. 

Multiple changes can take place at the same time and I need to account for that. 

I am not sure how to do make this work since there could be multiple changes on the same name/value pair for different order numbers at the same exact time. 

SampleData6.PNG

I was originally thinking that a trigger would work to where as soon as a value changes for the given param_name, perform my logic.


Could you guys please give me some detailed help on this with examples.  I would appreciate it very much.  Thank you in advance.

Tagged:
Jeremy McNally

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,070 Red Diamond
    edited Jul 20, 2015 12:31PM

    Hi,

    Yes, a trigger can notify you as soon as a change is made.  You'll probably want the trigger to fire whenever param_name or param_value is changed, but you can use IF statements inside the trigger so that it doesn't actually do anything unless one of the param_names that you need to track gets changed.  Compare the :OLD and :NEW VALUES, one at a time, to see which one(s) were changed.  Remember to check for a value getting changed to NULL, or vice-versa.

    If you run into problems, post a complete test script that the people who want to help you can run to re-create the problem and test their ideas.  Include your trigger, CREATE TABLE and INSERT statements for a little sample data, some INSERT, UPDATE and DELETE statements that cause the trigger to fire, and what you want to happen (e.g., the message you want generated) after each of those DML statements.  Point out exactly where the problem is (e.g., the error message you're getting, or where the results are not what you want).

    Jeremy McNallyJeremy McNally
  • Jeremy McNally
    Jeremy McNally Member Posts: 35
    edited Jul 20, 2015 12:34PM

    Thank you for answering me.  I will do what you said once I progress through this part of my task here. 


    Question on your comment:  How would I go about multiple value changes at the same time? 

    Generally I suppose a more to the point question would be: 

    1. I can I test for multiple value changes?
    2. What is the best way to store those values (still keeping it attached to the order number)?  i.e (array of record type perhaps?)
    3. And anything else that would help...

    Could you or someone give me a detailed explanation or example from start to finish on how this should be done (still keeping performance in mind)?  Pseudo-code is good too.

  • Pavan Kumar
    Pavan Kumar Member Posts: 11,904 Gold Crown
    edited Jul 20, 2015 1:28PM

    You can do that, prior that you need to get in touch with your friend

    http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/triggers.htm .. it's calling hey Jeremy.. read me up

    - Pavan Kumar N

    Jeremy McNallyJeremy McNally
  • Jeremy McNally
    Jeremy McNally Member Posts: 35
    edited Jul 22, 2015 9:03AM

    Great response!  docs.oracle never fails...  I found a new way to approach this task though.  I'm going to create a stand alone Java application that the users can interface with.  I found a table in our database that can do most of my work for me regarding this task.  My original approach would work with this metadata table I have but apparently it would (at this point) be creating more work that would eventually need to be scrapped anyway to go with my new approach since finding this table yesterday...

    I 'really' appreciate your referral to that link.  Have a great Wednesday.

This discussion has been closed.