CODEWITHSUNDEEP
sqloracle-databasetriggersaverage
BrunoWB
BrunoWB

Reputation: 135

SQL Trigger Update Average


I'm having a lot of trouble trying to make a trigger on Oracle SQL...
Here is my problem :
- I have a table X that contains values 0-10 and a table Y that contains the Average of this values for a group of X IDs. So I have to make a trigger that recalculate the average of the ID I'm inserting deleting or updating.
What basically im doing and failling... :

CREATE or REPLACE trigger Update_Average
    after insert or update or delete on X

DECLARE 
    holdID X.ID%type;
    Avrg Y.Average%type;

BEGIN
    holdID := X.ID;

    select avg(value)
      into Avrg
      from X 
      where X.id = holdID;

    update Y
       set Average = Avrg
       where Y.id = holdID;

END;

Upvotes: 1

Views: 1220

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270723

I think you can just do:

begin
    update Y
        set average = (select avg(value) from x where x.id = :new.id)
        where y.id = :new.id;
end

EDIT:

Oracle is fussy about updating tables that are being modified in a trigger. Personally, I think I would avoid this problem by storing two values in Y -- the sum and the count. Then, the trigger would look like:

begin
    update Y
        set sum = (sum + (case when :new.id is not null then :new.value else 0 end) -
                   (case when :old.id is not null then :old.value else 0 end))
            cnt = (cnt + (case when :new.id is not null then 1 else 0 end) -
                   (case when :old.id is not null then 1 else 0 end))
        where y.id = :new.id;
end

Note: for the ease of putting this in one statement, this checks the values for NULL, rather than using if DELETING. I'm pretty sure Oracle returns NULL for things like the :NEW record in a delete trigger.

Upvotes: 1

Related Questions