How to Write a Trigger to Subtract Values Between Two Tables in MySQL

MySQLPHPphpmyadmin

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

Best Answer

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

Related Question