CODEWITHSUNDEEP
mysqltriggers
dames
dames

Reputation: 1481

mysql trigger using two tables

I Have a database called 'lms' with two tables loan and value, table loan has: loan_amount, yearly_intrest, loan type; table value has value_id,value_name, value_amount. What i want is for my trigger to calculate the yearly interest in the loan table using the interest rate(value_amount) from the other table value where the loan_type(from loan table) is equal to the value (from Value table) I tried this, it needs some help

-- Trigger DDL Statements
DELIMITER $$

USE `lms`$$

CREATE
DEFINER=`root`@`localhost`
TRIGGER `lms`.`updateloan`
BEFORE INSERT ON `lms`.`loan` INNER JOIN 'lms'.'value'
FOR EACH ROW
BEGIN
    l.loan_type ="Computer Loan"
        SET l.yearly_intrest = (l.loan_amount *(v.value_amount/100))
        WHERE l.loan_type=v.value_name;

  END$$

Table value contains two value_names Computer and Motor vehicle with value amounts of 2, 5

i hope my explanation is clear enough

Upvotes: 1

Views: 684

Answers (1)

user1191247
user1191247

Reputation: 12998

I have not tried this but it should work -

DELIMITER $$

USE `lms`$$

CREATE
DEFINER=`root`@`localhost`
TRIGGER `lms`.`updateloan`
BEFORE INSERT ON `lms`.`loan`
FOR EACH ROW BEGIN
    SET NEW.yearly_interest = (SELECT NEW.loan_amount * value_amount/100 FROM `lms`.`value` WHERE value_name = NEW.loan_type);        
END$$

Upvotes: 1

Related Questions