Member Avatar for bioplanet
bioplanet 0 Newbie Poster

I have the following two tables:

TABLE 1:

+-----------------------------+--------------------------------------+------+-----+---------+-------+
| Field                       | Type                                 | Null | Key | Default | Extra |
+-----------------------------+--------------------------------------+------+-----+---------+-------+
| patient_id                  | bigint(20)                           | NO   | PRI | NULL    |       |
| patient_wpid                | int(11)                              | NO   | PRI | NULL    |       |
| age_at_visit                | int(11)                              | YES  |     | NULL    |       |
| sex                         | enum('male','female')                | YES  |     | NULL    |

TABLE 2

+-------------------------+----------------------------+------+-----+---------+----------------+
| Field                   | Type                       | Null | Key | Default | Extra          |
+-------------------------+----------------------------+------+-----+---------+----------------+
| patient_id              | bigint(20)                 | NO   | PRI | NULL    |                |
| patient_wpid            | int(11)                    | NO   | PRI | NULL    |                |
| weight                  | text                       | YES  |     | NULL    |                |
| creatinine              | text                       | YES  |     | NULL    |                |
| eGFR                    | varchar(100)               | YES  |     | N/A     |                |
+-------------------------+----------------------------+------+-----+---------+----------------+

What I need to do is, after an insert on Table 2, perform the following:

Select the age_at_visit and sex values from Table 1, for the given patient_id and patient_wpid (these fields have same values on both Table 1 and Table 2 and Table 1 has been filled BEFORE Table 2)

  1. Use the inserted values of weight and creatinine in Table2 and calculate a formula, in order to produce, and consecutively store, a value for the eGFR column of Table2.
    The formula is the following:

    eGFR = 175 × (creatinine)^(-1.154) × (age)^(-0.203) × (0.742 if female)

So, what I wrote (and produced quite a few errors since it is my very first attempt with Triggers) is the following:

delimiter //
CREATE TRIGGER calcEGFR AFTER INSERT ON Table2
FOR EACH ROW
BEGIN
    SELECT age_at_visit, sex FROM Table1 WHERE (Table1.patient_wpid = Table2.patient_wpid AND Table1.patient_id = Table2.patient_id)
    SET @creatinine_power := SELECT POWER(creatinine,-1.154);
    SET @age_power := SELECT POWER(Table1.age_at_visit,-0.203);
    IF Table1.sex = 'female' THEN
        SET @sex_addition := 0.742
    END IF;
    IF Table1.sex = 'male' THEN
        SET @sex_addition := 1
    END IF; 

    SET @eGFR_value := (175 * @creatinine_power * @age_power * @sex_addition)
    UPDATE Table2 SET eGFR = @eGFR_value;
END;//
delimiter ;

Can you please help me correct this?
Thanks!

Edited by bioplanet
  • 1 Contributor
  • 0 Replies
  • 118 Views

Dani AI

Generated

A few practical fixes and a ready-to-run pattern to use here.

As noted, Table1 is populated first. The trigger should compute eGFR before the row is written to Table2 so it can set the column directly (use NEW). Do not perform an UPDATE on the same table inside an AFTER trigger — use a BEFORE INSERT trigger, SELECT ... INTO to fetch age/sex from Table1, convert the incoming creatinine text to a numeric type, compute with POWER(), then assign the result to NEW.eGFR. Handle missing or non-numeric creatinine and absent Table1 rows by setting NEW.eGFR to NULL or signalling an error.

Key steps to follow

  • Use BEFORE INSERT and NEW to set the value instead of updating the row afterwards.
  • Fetch age and sex into local variables with SELECT ... INTO using NEW.patient_id and NEW.patient_wpid.
  • Convert NEW.creatinine to a numeric value (CAST/NULLIF/TRIM) and check for zero or NULL to avoid math errors.
  • Compute the multiplier for sex with a simple conditional and use POWER() for the exponentiations.
  • Store a rounded numeric string or, preferably, change the column to a numeric type (e.g., DECIMAL) for correctness.

Example trigger (illustrative — drop the old trigger first and adapt names/types to your schema):

delimiter //
DROP TRIGGER IF EXISTS Table2_calcEGFR;
//
CREATE TRIGGER Table2_calcEGFR
BEFORE INSERT ON Table2
FOR EACH ROW
BEGIN
    DECLARE v_age INT;
    DECLARE v_sex VARCHAR(6);
    DECLARE v_cr DECIMAL(9,4);
    DECLARE v_mult DECIMAL(4,3);
    DECLARE v_egfr DECIMAL(9,3);

    SELECT age_at_visit, sex INTO v_age, v_sex
      FROM Table1
      WHERE patient_id = NEW.patient_id AND patient_wpid = NEW.patient_wpid
      LIMIT 1;

    SET v_cr = NULLIF(TRIM(NEW.creatinine), '');
    SET v_cr = CAST(v_cr AS DECIMAL(9,4));

    IF v_age IS NULL OR v_cr IS NULL OR v_cr <= 0 THEN
       SET NEW.eGFR = NULL;
    ELSE
       SET v_mult = IF(v_sex = 'female', 0.742, 1.0);
       SET v_egfr = 175 * POWER(v_cr, -1.154) * POWER(v_age, -0.203) * v_mult;
       SET NEW.eGFR = CAST(ROUND(v_egfr,1) AS CHAR);
    END IF;
END;
//
delimiter ;

Notes: test with representative data, and prefer a numeric column for eGFR to simplify later math. MySQL trigger rules and functions are documented here: Triggers and POWER(), CAST().

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.