Mysql trigger with two tables

Question

I am creating a trigger to Update a column TEST if column receipt_on = Quarterly Interest Then If so it should find the sum in column receipt_amount and join another table loan on column l_app_file_id from table Loan on column r_app_file_id from table receipt_history also the month name from column receipt_date from table receipt_history should be the same as the current month, however I am not sure as to how to entirely structure this trigger

-- Trigger DDL Statements
DELIMITER $$

USE `lms`$$

CREATE
DEFINER=`root`@`localhost`
TRIGGER `lms`.`updateloan`
BEFORE UPDATE ON `lms`.`receipt_history`
FOR EACH ROW
BEGIN

if new.receipt_on='Quarterly Interest' then 
    SET new.TEST=SUM(receipt_amount)
    join loan l on 
    l.l_app_file_id=r.r_app_file_id
    WHERE r_app_file_id=l_app_file_id
    and monthname(receipt_date)=MONTHNAME(now())
    end if;    

  END$$

Johan · Accepted Answer · 2012-04-12 19:11:59Z

1

As you now know you cannot join in a set statement.
You need to use a select statement for that.
This construct will work however set i:= (select sum(x) from a);
I personally prefer the SELECT something INTO avariable syntax, but that's just a matter of taste.

DELIMITER $$

USE `lms`$$

CREATE
DEFINER=`root`@`localhost`
TRIGGER `lms`.`updateloan`
BEFORE UPDATE ON `lms`.`receipt_history`
FOR EACH ROW
BEGIN
  DECLARE itest integer;

  if new.receipt_on='Quarterly Interest' then 
    SELECT SUM(r.receipt_amount) INTO itest FROM receipt_history r
    INNER JOIN loan l ON (l.l_app_file_id=r.r_app_file_id)
    WHERE monthname(r.receipt_date)=MONTHNAME(now());
    SET new.test = itest;
  end if;    

  END$$

Note that:
the join criterion is already a (kind of) where clause, so you don't have to repeat that in the where clause.
Each and every statement in the trigger needs to be terminated by a ;.

edited Apr 12, 2012 at 19:11

answered Apr 12, 2012 at 18:51

Johan

77.4k28 gold badges204 silver badges346 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mysql trigger with two tables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related