Trigger :Add an old value with the new value

Question

I'm trying to create an trigger on phpmyadmin. My objective is to create a trigger that add the old value of a row with the new value (inserted with a form).

It's for managing the student dues, so if a student already paid $100, and pay $150 more the other time, the trigger have to add the old value ($100) with the new received value ($150), and the field 'already_paid' in the db is now $250.

I've tried something simple like this, but it didn't worked.

( BEFORE UPDATE )

SET @resultat = already_paid + new.already_paid
UPDATE student_dues
SET already_paid = @resultat
WHERE student_due_id = new.student_due_id

&

UPDATE student_dues
SET versement = versement + new.versement
WHERE student_due_id = new.student_due_id

On the same table, all the process is on the 'student_due' table — mfn_ofc

P.Salmon P.Salmon · Accepted Answer · 2021-01-20T15:09:57

You cannot action the table which fired the trigger in the trigger. Since already have the row to be updated which is the row, which is being updated, then you don't need an UPDATE

SET new.already_paid = old.already_paid + new.already_paid;

is all you need.

And you don't need a trigger a standalone update statement is all you need

UPDATE student_dues
SET already_paid = already_paid + <value to update with>
WHERE student_due_id = <whatever>

I am a bit dubious about your process though given you have no record of what already_paid consists of or when.

Trigger :Add an old value with the new value

1 Answers