How can I write a trigger that can subtract values between two tables

Question

I have a table called transaction that has these fields

CREATE TABLE transaction (
    transaction_id INT AUTO_INCREMENT,
    status varchar(255),
    myPIN varchar(255),
    amount_paid varchar(255),
    PRIMARY KEY (transaction_id)
)  ENGINE=INNODB;

Another table called defaulters that has the following

CREATE TABLE defaulters (
    defaulters_id INT AUTO_INCREMENT,
    myPIN varchar(255),
    debt_amount varchar(255),
    PRIMARY KEY (defaulters_id)
)  ENGINE=INNODB;

Now I want the value to subtract if the value of status=1 in transaction table where myPIN is equal to the amount_paid then it subtract debt_amount-amount_paid to get debt_amount remain and it reflect the remaining amount in defaulters table in debt_amount field.

fiddle

Answer 1

Look for

CREATE TRIGGER trigger_name
AFTER INSERT
ON transaction 
FOR EACH ROW
UPDATE defaulters 
SET debt_amount = debt_amount - NEW.amount_paid
WHERE myPIN = NEW.myPIN
  AND NEW.status = 1;

fiddle