I recently had a person ask the following question:
For the employees table, every time I insert a new value in salary column the previous value is overwritten. Can we add new inserted value to the previous salary value before it is overwritten?
Yes, we can accomplish this by using MySQL Triggers. Here’s a quick run of the application so you can see the output.
[code]
Employee Salaries
Doe John Salary=55000.00 Previous Salary=0.00
Public Mary Salary=75000.00 Previous Salary=0.00
Queue Susan Salary=130000.00 Previous Salary=0.00
Enter new salary for employees: 25000
Employee Salaries
Doe John Salary=25000.00 Previous Salary=55000.00
Public Mary Salary=25000.00 Previous Salary=75000.00
Queue Susan Salary=25000.00 Previous Salary=130000.00
[/code]
MySQL supports triggers. Triggers are similar to stored procedures but they are “triggered” when certain events happen on the database. For example, you can have SQL code that is triggered when data is inserted, updated or deleted. You can read more details about MySQL triggers at this link. But for now, I’ll cover the basics of MySQL triggers to address the question at hand.
We’ll use a trigger to copy the old salary information into a new column for later use. Here’s the database table we’ll use, make note of the new column: previous_salary.
[code]
CREATE TABLE `employees` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`last_name` varchar(64) DEFAULT NULL,
`first_name` varchar(64) DEFAULT NULL,
`email` varchar(64) DEFAULT NULL,
`department` varchar(64) DEFAULT NULL,
`salary` DECIMAL(10,2) DEFAULT NULL,
`previous_salary` DECIMAL(10,2) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
[/code]
Here’s the code for the trigger. In a nutshell, it will copy the old salary information to the new previous_salary column. This is SQL code that you will execute with your MySQL admin tool.
[code]
delimiter $$
CREATE TRIGGER salary_trigger BEFORE UPDATE on employees
FOR EACH ROW BEGIN
set NEW.previous_salary=OLD.salary;
END$$
[/code]
Now that the trigger is in place, whenever we perform an update on the database, then it will execute the trigger. Here’s a Java code snippet where we read a new salary from the user. Then we perform the update in the database:
[code]
//
// UPDATE SALARIES … read information from the user
//
// read new salary from command line
scanner = new Scanner(System.in);
System.out.print("Enter new salary for employees: ");
double newSalary = scanner.nextDouble();
// perform salary update
salaryStmt = myConn.prepareStatement("update employees set salary=?");
salaryStmt.setDouble(1, newSalary);
salaryStmt.executeUpdate();
[/code]
That’s it. You can download the full source code, including the SQL scripts, using the link below.
