CODEWITHSUNDEEP
mysqlinsertmamp
Gary Mannion
Gary Mannion

Reputation: 29

Subtracting one column from another column in different tables to leave the answer in another column in MySQL

m_type table membership table

I have a membership table with amount paid, amount due. I also have a m_type table with price . I want a trigger so that when a row is inserted or updated into the membership table WHERE membership.type_id = m_type.type-id that the price column from the m_type table is subtracted away from the amount_paid column in the membership table and the answer is put into the amount_due column in the membership table. Thanks

What i have currently tried: What i have tried

Upvotes: 0

Views: 31

Answers (1)

Lord Elrond
Lord Elrond

Reputation: 16032

IIUC:

DELIMITER $$

CREATE TRIGGER amount_due_cal BEFORE INSERT ON membership
  FOR EACH ROW
    BEGIN
    SET NEW.amount_due := (
        SELECT price FROM m_type WHERE type_id = New.type_id) - NEW.amount_paid;
END $$    
DELIMITER ;

If this doesn't work let me know.

Also note that type_id needs to be a unique index / primary key for the price table or this won't work.

Upvotes: 1

Related Questions