0

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.