Click here to Skip to main content
15,867,924 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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:

1. 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)

2. 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)



Can you please help me correct this?
Thanks!

What I have tried:

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 ;
Posted
Updated 4-May-22 6:09am
v2
Comments
k5054 27-Mar-20 21:00pm    
Although MySQL seems to allow math on non-numeric columns, using text or varchar() for columns that should only have numeric values may lead to problems in the future.

Please give us some indication as to what errors you are getting with your code. We're not mind readers, and most of us probably won't try to recreate your schema to help you.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900