Evaluate parameters based on entity's values using trigger/procedure

I have the following problem. In my application user creates a game character using SPECIAL system (Strength, Perception etc with values from 1 to 10). On saving or after (by calling procedure) i need to count statistics of character on the base of SPECIAL parameters values. How can I do this ? This is the relations scheme:

and here's SQL code :

create table Player (
  id_player numeric,
  player_name varchar2(50) not null,
  age decimal not null,
  strength decimal not null,
  perception decimal not null,
  endurance decimal not null,
  charisma decimal not null,
  inteligence decimal not null,
  agility decimal not null,
  luck decimal not null,
  caps decimal not null,
  statistics numeric,
  CONSTRAINT chk_s check (strength <= 10),
  CONSTRAINT chk_p check (perception <= 10),
  CONSTRAINT chk_e check (endurance <= 10),
  CONSTRAINT chk_c check (charisma <= 10),
  CONSTRAINT chk_i check (inteligence <= 10),
  CONSTRAINT chk_a check (agility <= 10),
  CONSTRAINT chk_l check (luck <= 10),
  CONSTRAINT unique_name UNIQUE (player_name),

  CONSTRAINT PLAYER_PK primary key (id_player)
);

create table Player_derived_statistics(
  id_statistics numeric,
  carry_weight decimal,
  hit_points decimal,
  radiation_resistance decimal,

  CONSTRAINT DERIVED_STATISTICS_PK primary key (id_statistics)
);

alter table Player add constraint PLAYER_DERIVED_STATISTICS_FK1 foreign key (statistics) references Player_derived_statistics (id_statistics);

and query returning all parameters:

SELECT p.strength, p.perception, p.endurance, p.charisma, p.inteligence, p.agility, p.luck
from player p inner join player_derived_statistics s on s.id_statistics = p.statistics;

So in the end I'd like to be able to count carry_weight, hit_points and radiation_resistance for each Player. Let's say that all formulas are (player_parameter * 10) + 150. What would be better to use : trigger or procedure ?

EDIT

I'm trying to use the code from开发者_运维百科 answer, but I'm getting error Encountered the symbol "INNER" when expecting one of the following: ( ....

CREATE OR REPLACE PACKAGE pkg_player_stats AS
  FUNCTION get_derived_stats( p_id_player IN player.id_player%TYPE )
    RETURN derived_stats_rec
  IS
    l_stats_rec derived_stats_rec;
  BEGIN
    SELECT (p.strength*10)+150,
           (p.endurance*20)+150,
           ((p.endurance-1)*2)/100
      INTO l_stats_rec.carry_weight,
           l_stats_rec.hit_points,
           l_stats_rec.radiation_resistance
      FROM (
        SELECT p.strength,  
               p.endurance
          from player p inner join player_derived_statistics s on s.id_statistics = p.statistics);
    RETURN l_stats_rec;
  END get_derived_stats;
END;

I definitely wouldn't use a trigger for this sort of thing. It sounds like you would want a function that accepts an ID_PLAYER parameter and returns either a value or a record of values. Something like this (note that I'm not sure that I understand the formulas you're describing so I'm guessing a bit

CREATE OR REPLACE PACKAGE pkg_player_stats
AS
  TYPE derived_stats_rec IS RECORD (
    carry_weight NUMBER,
    hit_points   NUMBER,
    radiation_resistance NUMBER );

  FUNCTION get_derived_stats( p_id_player IN player.id_player%TYPE )
    RETURN derived_stats_rec;
END;

CREATE OR REPLACE PACKAGE pkg_player_stats
AS
  FUNCTION get_derived_stats( p_id_player IN player.id_player%TYPE )
    RETURN derived_stats_rec
  IS
    l_stats_rec derived_stats_rec;
  BEGIN
    SELECT carry_weight         * multiplier + 150,
           hit_points           * multiplier + 150,
           radiation_resistance * multiplier + 150
      INTO l_stats_rec.carry_weight,
           l_stats_rec.hit_points,
           l_stats_rec.radiation_resistance
      FROM (
        SELECT p.strength + 
               p.perception + 
               p.endurance +
               p.charisma + 
               p.inteligence +
               p.agility +
               p.luck multiplier,
               s.carry_weight,
               s.hit_points,
               s.radiation_resistance
          from player p 
               inner join player_derived_statistics s on s.id_statistics = p.statistics);
    RETURN l_stats_rec;
  END get_derived_stats;
END;

Why do you need two tables ? I would go with either

• A single table with all S.P.E.C.I.A.L stats only and a view that computes the derived statistics (in your application you would query the view):

  CREATE VIEW player_v AS 
  SELECT p.strength, ..., /* all attributes */
         p.strength * 10 + 150 as carry_weight,
         p.endurance * 20 + 150 as hit_points,
         (p.endurance - 1) * 2 / 100 as radiation_resistance
    FROM player p
  

• A single table with derived columns that you update with a trigger:

  CREATE OR REPLACE TRIGGER player_ins_up_trg 
     BEFORE UPDATE OR INSERT ON player
     FOR EACH ROW
  BEGIN
     :new.carry_weight := :new.strength * 10 + 150;
     :new.hit_points := :new.endurance * 20 + 150;
     :new.radiation_resistance := (:new.endurance - 1) * 2 / 100;
  END;
  

• If you are using Oracle 11, you could also use a virtual column:

  A virtual column is not stored on disk. Rather, the database derives the values in a virtual column on demand by computing a set of expressions or functions.

  For example:

  ALTER TABLE player ADD (carry_weight AS (strength * 10 + 150));