We have two database tables. while updating the table entry trigger will execute.
tbl_experience
tbl_salary
after updating the experience in the tbl_experience table. automaticaly update the salary column in the tbl_salary
Step1:
Use the below code to create tables through phpmyadmin-paste the below code into the query window.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
CREATE TABLE IF NOT EXISTS `tbl_experience` ( `id` int(10) NOT NULL AUTO_INCREMENT, `name` varchar(250) NOT NULL, `exp` int(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ; -- -- Dumping data for table `tbl_experience` -- INSERT INTO `tbl_experience` (`id`, `name`, `exp`) VALUES (1, 'vasanthan', 1), (2, 'rajesh', 2); CREATE TABLE IF NOT EXISTS `tbl_salary` ( `id` int(10) NOT NULL AUTO_INCREMENT, `empid` int(10) NOT NULL, `salary` int(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; |
Step2: execute the below script through SQL run winodow in phpmyadmin
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
delimiter | CREATE TRIGGER Salaryupdation AFTER UPDATE ON tbl_experience FOR EACH ROW BEGIN DECLARE updatecount INT; set updatecount=(select count(id) from tbl_salary where empid = NEW.id); if updatecount>0 then UPDATE tbl_salary SET salary = salary + 200 WHERE empid = NEW.id; ELSE INSERT INTO tbl_salary SET empid = NEW.id,salary=1000; END IF; END | delimiter ; |
Step3: Goto phpmyadmin browse the table tbl_experience and edit some records.
check the table tbl_salary you can see some updates in the salary field
Enjoy 🙂
Please share this tutorials with your friends