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 ;