ORACLE PL SQL help needed! using asp.net C# front end - trying to update and display two related columns

Hi

Using ORACLE 10G and asp.net C#

I have a table and a couple of views.

I have to columns that are of datatype number and these are populated from the user via my asp app

col_1
col_2

each column that is populated has an imediate effect on the other for example:

col_1 = (col_2 * rate)
and col_2 = (col_1 / rate)

It is displayed in a gridview and the user can input on either of the columns but will not input on both of them in the same row

How can I represent this on the database side so that which ever option the user decides to update it has an automatic result on the other column when they click the save button?

Any help on this would be great
jim_bob_jimAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
One way would be to use triggers.  The simple way is two triggers, one for updating each column.  It can be done in one trigger, but they you have to check which column was updated yourself.
0
MohanKNairCommented:
Create a database trigger ON UPDATE or INSERT
0
oleggoldCommented:
example of such a trigger:
CREATE TRIGGER PGMSCHEMA.UPDATE_FACT_COL1
 BEFORE
 INSERT OR UPDATE OF COL_1
 ON CMR.EMPL_TEST_SCORE_FACT
 REFERENCING OLD AS OLD NEW AS NEW
 FOR EACH ROW
UPDATE TAB
SET COL_2=col_1 / rate
/
CREATE TRIGGER PGMSCHEMA.UPDATE_FACT_COL2
 BEFORE
 INSERT OR UPDATE OF COL_2
 ON CMR.EMPL_TEST_SCORE_FACT
 REFERENCING OLD AS OLD NEW AS NEW
 FOR EACH ROW
UPDATE TAB
SET COL_2=col_2* rate
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

SujithData ArchitectCommented:
>> these are populated from the user via my asp app
Are you talking about new records only(INSERT) or You have to consider UPDATE as well?

INSERT cases can be easily handled using a row level TRIGGER. But for UPDATE you will have to write a function.
0
biswaranjan_rathCommented:
Trigger is a nice option. As Sujith said for Insert there won't be much problem, but to Update there might problems.
Also Oleggold suggests nice code for the triggers, but I suspect updating one row might invoke the trigger on other. So it might go for a recursive loop.(I am not sure, oleggold please comment). So there need to be another condition need to be added as :OLD != :NEW.

Finally I would like to suggest, to make your application efficient, put this validation in the front-end code (like a function in ASP or in VB Script(most prefered) ) and just update the table with values. This is standard business rule for Front-end and back-end applications and I don't think it is that difficult. If you have some other concern you can use triggers.

You can write code in the front-end using Focus-out property of the input box.

0
jim_bob_jimAuthor Commented:
Hi thanks all for your comments......

Yes it is needed for both inserting new records but also updating existing rows

I will work through this today and post my results

any help greatly appreciated
0
SujithData ArchitectCommented:
The best practice is to centralize the logics like this generally using a trigger. Otherwise that logic will have to be brought in every front end piece manipulating the table.

The following trigger will handle the case you have shown for both INSERT and UPDATEs. Dont try to have another update to the same table in the trigger. Coz, it will result in mutation and is not necessary.

create or replace trigger test_trg
before insert OR update of col_1 , col_2
on <your table>
for each row
declare
 l_rate number;
begin
select rate into l_rate
from <your rate table> where <your where condition>;

if INSERTING then
  if :new.col_1 is null then
   :new.col_1 := nvl(:new.col_2,0) * l_rate;
  elsif :new.col_2 is null then
   :new.col_2 := nvl(:new.col_1,0) / l_rate;
  end if;
end if;

if UPDATING then
 if :new.col_1 <> :old.col_1 then
  :new.col_2 := :new.col_1 / l_rate;
 elsif :new.col_2 <> :old.col_2 then
  :new.col_1 := :new.col_2 * l_rate;
 end if;
end if;
end;
/
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jim_bob_jimAuthor Commented:
Hi - the main problem I have is that the rate column of which I have to multiply (col_1) and divide (col_2) with  is from a different table.

I need to be able to both insert and update col_1 and col_2 and display both in one select statement via my GridView where

col_1 = (col_2 * rate)
and col_2 = (col_1 / rate)

I have been busy trying to do this via the front end C# and have it but it does not display both and it also keeps multiplying and dividing out each click.

I will attempt the trigger method now but how will it affect it now that I have explained that the rate column is being pulled from a separate table?

thanks again
0
jim_bob_jimAuthor Commented:
Hi again....... after re-designing my tables somewhat I have made it so that it only will ever be an update statement that is required
0
jim_bob_jimAuthor Commented:
Hi Im getting the following error when running the trigger:

ORA-04098: trigger 'test_trig' is invalid and failed re-validation- I get this for any update/insert on the table even when the two columns are not being updated....

Can this trigger be re-written to only update the table as I dont need to insert
0
jim_bob_jimAuthor Commented:
Hi thanks Im seeing double at this stage!

this works for one update

create or replace trigger test_trg
before update of col_1 , col_2
on <your table>
for each row
declare
 l_rate number;
begin
select rate into l_rate
from <your rate table> where <your where condition>;
if UPDATING then
 if :new.col_1 <> :old.col_1 then
  :new.col_2 := :new.col_1 / l_rate;
 elsif :new.col_2 <> :old.col_2 then
  :new.col_1 := :new.col_2 * l_rate;
 end if;
end if;
end;
/

How do I get it so that I can update multiple rows and columns??   I have this as a gridview and I update and save all with one click so I need to be able to update this table for the rows required


thanks
0
SujithData ArchitectCommented:
>>How do I get it so that I can update multiple rows and columns??  
I didnt understand that question.

The trigger code above is defined already "FOR EACH ROW". So it will fire for every record being updated. Now, there is no resriction of updating any number of columns also.
Your requirement is to adjust the value of col_1 and col_2 based on the updated value of each other. So, in cases where these columns are NOT updated, there is no need to fire the trigger right? That is why the clause "before update of col_1 , col_2".

Hope its clear.
0
jim_bob_jimAuthor Commented:
Yes I understand but let me explain a bit better.

At the moment I have it so that there is one row in my table.

The columns are inserted at another point via the user but col_1 and col_2 remain NULL until the part where Im trying to get working now.

When the grid is loaded up the colums are visible with col_1 and col_2 blank.  the user then clicks to edit and  col_1 or col_2 become editable and a value is entered in ONE of them - The user will only ever enter values into one of those columns per row but when that column has a value in it the other column should be updated based on the value of the other column and a separate column even when it is NULL to begin with.

The trigger you have provided me has got me in the right direction.  It does update each col_1 and col_2 based on each ones contents but ONLY after both of them have values already in them.  At the beginning both columns are NULL and then when I put a value into col_1 and hit save col_2 remains NULL.  I then put a value into col_2 and col_1 remains the same.  The next time I edit any of the two columns then the trigger does work and they both update correct.  It only does it though after both columns have been updated.

Also it works when there is one row.  If I have two rows and I try and edit row two I get the following error

ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "SYS.TEST_TRG", line 4
ORA-04088: error during execution of trigger 'SYS.TEST_TRG'

Thanks for your time so far it has been very much appreciated!

Jim
0
biswaranjan_rathCommented:
could you provide the code you are using in front-end to do this?
I per my understanding:
Since the two columns are inter-dependent, multiplication and division will happen. But if the values have not changed, prevet the arithmatic operations.
Also this seems to be the right logic as you have mentioned above as
1. If somebody modifies col1, col2 will be automatically updated
2. if col2 modified, then col1 will be updated.

So in any case if you click on the col1 inputbox, the code will definitely check if there is any change or not. So don't panic and don't rush to make database side changes though it look simpler. But it's your call.

In trigger you mentioned above, you have update the table for each <col1;col2> combination. Then only the trigger will be fired for that column and for the corresponding row. Since it is not the case of bulk updating, you cannot update the whole table at a single go. So you have to loop through all the records and update the table.
Also as I mentioned earlier triggers are considered to be costly. So better to avoid it as much as you can.
0
jim_bob_jimAuthor Commented:
Im using C# front end.  Im using a grid view where I have one master edit button that enables all editable columns to be edited - in this case col_1 and col_2

I also have a master save button that saves all changes by updating the table

here is my code

        OleDbCommand myCMD = new OleDbCommand("update table set col_1 = ?, col_2 = ? where col_3 = ?", myConn);
       
        OleDbParameter col1;
        OleDbParameter col2;
        OleDbParameter col3;

        param1 = myCMD.Parameters.AddWithValue("col_1", DBNull.Value);
        param2 = myCMD.Parameters.AddWithValue("col_2", DBNull.Value);
        param3 = myCMD.Parameters.AddWithValue("col3", DBNull.Value);


        myConn.Open();

        for (int i = 0; i < GridView1.Rows.Count; i++)
        {
            param1.Value = ((TextBox)GridView1.Rows[i].FindControl("text1")).Text;
            param2.Value = ((TestBoxl)GridView1.Rows[i].FindControl("text2")).Text;
            param3.Value = ((Label)GridView1.Rows[i].FindControl("label1")).Text;
 
            myCMD.ExecuteNonQuery();
         }

        myConn.Close();
0
biswaranjan_rathCommented:
I am preparing a sample code and will send you after i finish that.

0
jim_bob_jimAuthor Commented:
I know have it in my trigger that it updates each col even if they are NULL at the start.

Thanks for the help sujith80

I now just need it so that if I try and update row_2 I dont recieve these errors

ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "SYS.TEST_TRG", line 4
ORA-04088: error during execution of trigger 'SYS.TEST_TRG'


It only works when the table is 1 row
0
jim_bob_jimAuthor Commented:
the problem is with the SELECT INTO syntax as that cant handle returning more than 1 row

I will need to use a cursor
0
SujithData ArchitectCommented:
If you define the trigger only on "UPDATE", you can initially insert NULL in both columns. So, the trigger will not bother what values are inserted for the first time. Hence there is no problem

Now, about the error that yor are getting:
The SELECT statement is supposed to get "a" rate value from "some" table, based on "some" condition. So, while selecting into the variable  l_rate you should ensure that only one row is present in the rate table for that where clase.
I.e. the below statement should return only one row.

>>select rate into l_rate
from <your rate table> where <your where condition>;


And, if you have differnt rate values for the two columns, there should be a proper where clause to distinguish both of them. And use one more variable in the trigger to process with that value.
0
jim_bob_jimAuthor Commented:
Hi but because I will be returning more than one row most of the time I will need to use a cursur.  Is that correct??

I have brought a curser into my trigger and it does allow me return more than one row but it now calculates all rows with the rate value from the first row!!.........

here is my trigger I need to implement a loop inside it if Im to use a curser.

can you give me an example of your method by using an extra variable in the trigger to distinguish between the rate values.

Or if my method of using a curser is the way to go can you have a look at this and see how I will make it calculate by the correct rate value for each row.

thanks

CREATE OR REPLACE TRIGGER TEST_trg
BEFORE UPDATE OF col_1, col_2
ON table_1
FOR EACH ROW
DECLARE
rate NUMBER;
CURSOR c1
IS
SELECT table_1.rate INTO  rate
FROM table_1;
BEGIN
OPEN c1;
FETCH c1 INTO rate;
IF UPDATING THEN
  IF :NEW.col_1 IS NULL THEN
   :NEW.col_1 := NVL(:NEW.col_2,0) * rate;
  ELSIF :NEW.col_2 IS NULL THEN
   :NEW.col_2 := NVL(:NEW.col_1,0) / rate;
  END IF;
END IF;
IF UPDATING THEN
 IF :NEW.col_1 <> :OLD.col_1 THEN
  :NEW.col_2 := :NEW.col_1 / rate;
 ELSIF :NEW.col_2 <> :OLD.col_2 THEN
  :NEW.col_1 := :NEW.col_2 * rate;
 END IF;
END IF;

thanks......
0
jim_bob_jimAuthor Commented:
thanks - I'm going to close this question as I think you have ansered my orriginal request

so it is only fair to reward you the points and open a fresh Q

here is new Q

http://www.experts-exchange.com/Database/Oracle/10.x/Q_22513473.html
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.