Solved

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

Posted on 2007-04-11
21
612 Views
Last Modified: 2013-12-07
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
0
Comment
Question by:jim_bob_jim
  • 11
  • 4
  • 3
  • +3
21 Comments
 
LVL 34

Expert Comment

by:johnsone
ID: 18891197
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
 
LVL 16

Expert Comment

by:MohanKNair
ID: 18891256
Create a database trigger ON UPDATE or INSERT
0
 
LVL 21

Expert Comment

by:oleggold
ID: 18892906
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
 
LVL 27

Expert Comment

by:sujith80
ID: 18895830
>> 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
 
LVL 3

Expert Comment

by:biswaranjan_rath
ID: 18895942
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
 

Author Comment

by:jim_bob_jim
ID: 18896393
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
 
LVL 27

Accepted Solution

by:
sujith80 earned 500 total points
ID: 18896658
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
 

Author Comment

by:jim_bob_jim
ID: 18897842
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
 

Author Comment

by:jim_bob_jim
ID: 18897993
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
 

Author Comment

by:jim_bob_jim
ID: 18898532
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:jim_bob_jim
ID: 18898850
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
 
LVL 27

Expert Comment

by:sujith80
ID: 18903739
>>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
 

Author Comment

by:jim_bob_jim
ID: 18904087
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
 
LVL 3

Expert Comment

by:biswaranjan_rath
ID: 18904109
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
 

Author Comment

by:jim_bob_jim
ID: 18904142
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
 
LVL 3

Expert Comment

by:biswaranjan_rath
ID: 18904428
I am preparing a sample code and will send you after i finish that.

0
 

Author Comment

by:jim_bob_jim
ID: 18904468
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
 

Author Comment

by:jim_bob_jim
ID: 18904658
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
 
LVL 27

Expert Comment

by:sujith80
ID: 18916040
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
 

Author Comment

by:jim_bob_jim
ID: 18916238
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
 

Author Comment

by:jim_bob_jim
ID: 18917437
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

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now