Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2003
    Posts
    81

    Question Unanswered: Calculated coloumn

    I am trying to update one coloumn as a result of a calculation (based on two coloumns). But the trigger seems to be mutating.

    CREATE OR REPLACE TRIGGER Test1
    before update of COL1 on TAB1
    for each row
    declare
    KONST number(10);
    begin
    select COL2+COL3 into KONST from Test1;
    :new.COL1 := KONST;
    end;

    Does anyone know any other easy solution?

  2. #2
    Join Date
    Dec 2003
    Location
    Buenos Aires, Argentina
    Posts
    86
    It seems that there's no condition to update COL1... so why don't you:
    UPDATE COL1 = COL2+COL3 (inside a trigger for new records)... is this what you need???
    Regards,

    Manf

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    CREATE OR REPLACE TRIGGER Test1
    before update of COL1 on TAB1
    for each row
    begin
    :new.col1 := :new.col2 + :new.col3;
    end;

    You can't perfrom a select of the base table in a database trigger. If what you want is to make col1 the total of col2+col3 in the same row, then the above trigger will do the job.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Jun 2003
    Posts
    81

    Half the way

    Thank you
    Yes it is working. I just have one additional problem. If COL2 or COL3 is NULL the result of COL2+COL3 (=COL1) also is NULL - even if the other is not null.

    Actually I have about 7 coloumns, so I can't solve the problem by something like ...where COL2 is null and .....

  5. #5
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    You could try using NVL on both column's new value (COL2, COL3). But I am curious about why is this trigger fired on the column that is supposed to be the calculated one (COL1) ? As I can see, what you need is to update the calculated column when either COL2 or COL3 is updated. If I'm right, then you need something more like this:

    Code:
    create or replace trigger test1
     before update of col2,col3 on tab1
     for each row
    begin
      :new.col1 := nvl(:new.col2,0) + nvl(:new.col3,0);
    end;

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Missed the fact the trigger was on col1, good catch
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Jun 2003
    Posts
    81
    Of course the trigger is to be run on COL2 & COL3 and not on COL1. The NVL function works fine. So everything is now OK. Thank you very much.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •