Scan Table for Value Change(s)

Jeremy McNally

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. 

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.

Frank Kulash

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 McNally

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

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 McNally

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.