15,664,506 members
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)`

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
END IF;
IF Table1.sex = 'male' THEN
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
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.