How To Create Triggers In MySQL
Posted By : Kuldeep Kumar | 27-Apr-2018
In MySql, a trigger is a database Object and is related to a specific table in the database. A trigger is a PL/SQL(Procedural Language/Structured Query Language) statement that is fired when an event occurs in the database table records.the triggers can be used to validate the data inside the tables and any calculation can be done with the help of triggers inside the database tables.triggers can save your lots of backend programming calculations.
Activation of Trigger: -A trigger is activated as database table records a change in the data.generally, at INSERT, UPDATE, DELETE operations.
you can have more than one triggers for a single table.
mysql> delimiter $$
mysql> create trigger trigger_name
before/after insert/update/delete
on table_name
for each row
begin
------------------
------------------
------------------
end;
$$
delimiter;
How to remove a trigger: -A trigger can be easily removed from the table by command drop trigger trigger_name.
Example: suppose you want to design an automatic salary amount calculator based on the punch in of the biometric system.for this, you have to just design a simple trigger for the below employee table
+--------+----------+------------+------+-------+
| emp_id | emp_name | date | days | total |
+--------+----------+------------+------+-------+
| 1 | kuldeep | 2018-04-27 | 0 | 0 |
+--------+----------+------------+------+-------+
mysql> delimiter $$
mysql> create trigger mytrigger
-> before update
-> on Employee
-> for each row
-> begin
-> set new.days = new.days+1 , new.total = new.total+700; //the total salary of employee is 21000 and per day 700
-> end;
-> $$
+--------+----------+------------+------+-------+
| emp_id | emp_name | date | days | total |
+--------+----------+------------+------+-------+
| 1 | kuldeep | 2018-04-28 | 1 | 700 |
+--------+----------+------------+------+-------+
+--------+----------+------------+------+-------+
| emp_id | emp_name | date | days | total |
+--------+----------+------------+------+-------+
| 1 | kuldeep | 2018-04-29 | 2 | 1400 |
+--------+----------+------------+------+-------+
The above trigger will add one day salary to the Employee table as employee successfully punch in from biometric for the particular day.
Request for Proposal
Cookies are important to the proper functioning of a site. To improve your experience, we use cookies to remember log-in details and provide secure log-in, collect statistics to optimize site functionality, and deliver content tailored to your interests. Click Agree and Proceed to accept cookies and go directly to the site or click on View Cookie Settings to see detailed descriptions of the types of cookies and choose whether to accept certain cookies while on the site.
About Author
Kuldeep Kumar
Kuldeep is interested in developing web applications and he is currently working on Groovy on Grails. He likes to learn new technologies and love playing chess.