MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE transaction (
    transaction_id INT AUTO_INCREMENT,
    status varchar(255),
    myPIN varchar(255),
    amount_paid varchar(255),
    PRIMARY KEY (transaction_id)
)  ENGINE=INNODB;
CREATE TABLE defaulters (
    defaulters_id INT AUTO_INCREMENT,
    myPIN varchar(255),
    debt_amount varchar(255),
    PRIMARY KEY (defaulters_id)
)  ENGINE=INNODB;
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
INSERT INTO defaulters VALUES
(1, '123', '1000'),
(2, '456', '2000')

Records: 2  Duplicates: 0  Warnings: 0


SELECT * FROM defaulters


defaulters_id
myPIN
debt_amount


1
123
1000

2
456
2000


INSERT INTO transaction VALUES
(1, '1', '123', '100'),
(2, '0', '456', '200')

Records: 2  Duplicates: 0  Warnings: 0


SELECT * FROM defaulters


defaulters_id
myPIN
debt_amount


1
123
900

2
456
2000


INSERT INTO transaction VALUES
(3, '0', '123', '300'),
(4, '1', '456', '400')

Records: 2  Duplicates: 0  Warnings: 0


SELECT * FROM defaulters


defaulters_id
myPIN
debt_amount


1
123
900

2
456
1600