MySQL Forums
Forum List  »  Triggers

How I can make the trigger calculates the sum of a column for specific condition
Posted by: Robin vanpersie
Date: October 03, 2020 10:25PM

I have table employees as following

CREATE TABLE `employees`
(
  `Employee_Id` Int NOT NULL AUTO_INCREMENT,
  `Employeer_Name` Char(150) NOT NULL,
  `Job` Varchar(100),
  `Salary` Decimal(10,2),
  PRIMARY KEY (`Employee_Id`)
) ENGINE = InnoDB
 AUTO_INCREMENT = 1
 ROW_FORMAT = Compact
;

During a month the employee can borrow some money from his salary many times,
it's required to record that amount of money borrowed , the remaining money from his monthly salary each time.

So I created table Salary_Transaction to record the transactions of borrowing and receiving money

CREATE TABLE `Salary_Transaction`
( `Salary_Transaction_Id` Int NOT NULL AUTO_INCREMENT,
  `money_amount` Decimal(10,2) NOT NULL,
  `Employee_Id` Int,
  PRIMARY KEY (`Salary_Transaction_Id`) );

And I created a table called debt_alerts to display the remaining money from the salary

CREATE TABLE `debt_alerts`
(
  `Debt_alert_id` Int NOT NULL AUTO_INCREMENT,
  `Salary_Transaction_Id` Int,
  `Remaining_money` Decimal(10,2),
  `Employee_Id` Int,
  PRIMARY KEY (`Debt_alert_id`)
) ENGINE = InnoDB
 AUTO_INCREMENT = 5
 ROW_FORMAT = Compact
;

--These are my indexes relationships

CREATE INDEX `IX_Relationship23` USING BTREE ON `debt_alerts` (`Salary_Transaction_Id`)
;

CREATE INDEX `IX_Relationship33` ON `debt_alerts` (`Employee_Id`)
;

CREATE INDEX `IX_Relationship11` USING BTREE ON `salary_transaction` (`Employee_Id`)
;
ALTER TABLE `salary_transaction` ADD CONSTRAINT `Relationship11` FOREIGN KEY (`Employee_Id`) REFERENCES `employees` (`Employee_Id`) ON DELETE CASCADE ON UPDATE CASCADE
;


ALTER TABLE `debt_alerts` ADD CONSTRAINT `Relationship23` FOREIGN KEY (`Salary_Transaction_Id`) REFERENCES `salary_transaction` (`Salary_Transaction_Id`) ON DELETE CASCADE ON UPDATE CASCADE
;

ALTER TABLE `debt_alerts` ADD CONSTRAINT `Relationship18` FOREIGN KEY (`Employee_Id`) REFERENCES `employees` (`Employee_Id`) ON DELETE CASCADE ON UPDATE CASCADE
;

For accomplishment of my task I created this trigger
DELIMITER $$
CREATE trigger Remaining_money_calc
AFTER INSERT ON Salary_Transaction 
FOR EACH ROW 
BEGIN
INSERT INTO debt_alerts(Remaining_money,Employee_Id,Salary_Transaction_Id) 
SELECT y.y-x.x,NEW.Employee_Id ,NEW.Salary_Transaction_Id
FROM ( SELECT SUM(money_amount) x
       FROM Salary_Transaction 
       WHERE Salary_Transaction_Id =  Salary_Transaction_Id ) x
JOIN ( SELECT Employees.Salary y 
       FROM Employees
       WHERE NEW.Employee_Id=Employees.Employee_Id 
       LIMIT 1 ) y;
END
$$ DELIMITER ;

The problem this trigger calculate the sum of all remaining_money in the debt_alerts table, I want it to do that for specific employee only.

Options: ReplyQuote


Subject
Views
Written By
Posted
How I can make the trigger calculates the sum of a column for specific condition
257
October 03, 2020 10:25PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.