asked on

oracle 10G trigger to auto update two columns based on which column is updated

Oracle 10 G
asp.net C# front end

Hi guys,

I have the following trigger set up in my tables so that when either col_1 or col_2 get updated both cols are caclulated eg:

col_1 = col_2 / rate
col_2 = col_1 * rate

where rate will be different each row but is from the same table.

It is just so that when one column is poulated the other column displays the same equivilant detail so that the user has the choice of which format they wish to enter in.

I had a previous question open and got a lot of help with it but it has since progressed so it is only fair I open a new question.

here is my trigger. the problem Im having is that when it returns all of my rows it calculates all the rows with the rate from the first row. I need help (i think) with setting a loop within the curser so that each row is calculated with the corect rate.

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;

any help appreciated

Mark Geerlings

"I need help (i think) with setting a loop within the curser..."
No!

You don't need a cursor! In fact, in a "for each row" trigger in Oracle you are not allowed to use a cursor that selects values from the triggering table (nor from any tables related by foreign key).

(I'll look this over a bit more, but post this much for now.)

Mark Geerlings

This should work (but if the user ever changes both col_1 and col_2, the value in col_1 will take precedence):

CREATE OR REPLACE TRIGGER TEST_trg
BEFORE UPDATE OF col_1, col_2
ON table_1
FOR EACH ROW
BEGIN
IF :NEW.col_1 IS NULL or :NEW.col_2 IS NULL 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;
else
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;
end;

jim_bob_jim

ASKER

Hi thanks for your time......

just a few things....

do I not have to declare rate?

and will this have it so that it will calc based on the row of col_1 and col_2 and not just the rate from col_1

Its telling me to declare rate.

Mark Geerlings

Oops! I missed "rate". You do either need to declare it and and set it based on :new.rate, or simply refer to it directly as :new.rate. This assumes of course that the user/application will always provide a value for this column. If they don't, then trying to do this trigger becomes more complex. You could then use: "nvl(:new.rate,:old.rate)".

Mark Geerlings

No, you cannot "select" the rate using a cursor in a "for each row" trigger, but you may refer to it directly using either the :new.column_name or :old.column_name syntax, depending on whether you want the value from before the change or after.

jim_bob_jim

ASKER

the rate value will always stay the same but it changes as per row. and there will always be a value in the rate column

Currently I have my trigger working so that it updates correctly but is calculates all rows by the rate from the first row.

How would I incorporate the rate into my trigger by declaring it?

ASKER CERTIFIED SOLUTION

Mark Geerlings

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jim_bob_jim

ASKER

that did it thanks!

I meant it was updating in that it was giving me a value but just the value that was calculated by the wrond rate.

thanks a lot!

Mark Geerlings

You may want to add a check constraint on the rate column of this table to make sure that never gets set to 0. because if that is ever 0 (or null) you will get a "divide by 0" error from this trigger.