A trigger is a MySQL program, which can be executed automatically by the Oracle server when an event is raised. MySQL Triggers executes before or after an insert, update or delete statement. MySQL Triggers are fired, when a appropriate event occurs for the table. MySQL Triggers are used to perform verification checks on data values for insertion and perform calculations. MySQL Triggers are of 4 types:

MySQL Before insert trigger will fire the trigger before performing insert records values inside the table.

trigger_name => The name of the trigger to be created. table_name => The accurate table in the database.

• Database table - Set of database elements.
• User defined function - Which are defined by the user.
• Error log - Error that created during error logs.
• OSSES agent - Connection between specific client and server.

By viewing the below example, the concept of before insert trigger can be easily understood. [sql]mysql> select * from employee; +--------+----------+-----------+-------------------+--------+-------+ | emp_id | emp_name | city | designation | salary | perks | +--------+----------+-----------+-------------------+--------+-------+ | 1 | david | delhi | manager | 12000 | 855 | | 2 | shaha | mumbai | assistant manager | 13000 | 853 | | 3 | sha | puna | scales manager | 11000 | 850 | | 4 | jack | bangalore | designer | 14000 | 854 | | 5 | james | mangalore | web designer | 15000 | 1124 | +--------+----------+-----------+-------------------+--------+-------+ 5 rows in set (0.00 sec) mysql delimiter $$ mysql CREATE TRIGGER inst_trigger BEFORE INSERT ON Employee -> FOR EACH ROW -> BEGIN -> UPDATE Employee SET Salary=Salary-400 where perks>500; -> end; -> $$ Query OK, 0 rows affected (0.42 sec) mysql> delimiter ; mysql> insert into employee values(6,'mike','chennai','develpoer',15500,840); Query OK, 1 row affected (0.10 sec) mysql> select * from employee; +--------+----------+-----------+-------------------+--------+-------+ | emp_id | emp_name | city | designation | salary | perks | +--------+----------+-----------+-------------------+--------+-------+ | 1 | david | delhi | manager | 11600 | 855 | | 2 | shaha | mumbai | assistant manager | 12600 | 853 | | 3 | sha | puna | scales manager | 10600 | 850 | | 4 | jack | bangalore | designer | 13600 | 854 | | 5 | james | mangalore | web designer | 14600 | 1124 | | 6 | mike | chennai | developer | 15100 | 840 | +--------+----------+-----------+-------------------+--------+-------+ 6 rows in set (0.00 sec) [/sql] In the above example, before insert, trigger will fire the condition before inserting a record inside the table or on the table, and update command is performed on the column salary(Set Salary=Salary-400 where perks>500). Then the salary of all the employee changes as all the employee perks are greater then the applied condition.

MySQL Before update trigger will fired the trigger before performing update records values inside the table.

trigger_name => The name of the trigger to be create. table_name => The accurate table in the database.

By viewing the below example, the concept of before update trigger can be easily understood. [sql]mysql> select * from employee; +--------+----------+-----------+-------------------+--------+-------+ | emp_id | emp_name | city | designation | salary | perks | +--------+----------+-----------+-------------------+--------+-------+ | 1 | david | delhi | manager | 12000 | 855 | | 2 | shaha | mumbai | assistant manager | 13000 | 853 | | 3 | sha | puna | scales manager | 11000 | 850 | | 4 | jack | bangalore | designer | 14000 | 854 | | 5 | james | mangalore | web designer | 15000 | 1124 | | 6 | mike | chennai | develpoer | 15500 | 840 | +--------+----------+-----------+-------------------+--------+-------+ 6 rows in set (0.00 sec) mysql> delimiter // mysql> CREATE TRIGGER updtrigger BEFORE UPDATE ON Employee -> FOR EACH ROW -> BEGIN -> IF NEW.Salary=500 THEN -> SET NEW.Salary=10000; -> ELSEIF NEW.Salary>500 THEN -> SET NEW.Salary=15000; -> END IF; -> END -> // Query OK, 0 rows affected (0.01 sec) mysql> delimiter ; mysql> UPDATE Employee -> SET Salary=500; Query OK, 5 rows affected (0.04 sec) Rows matched: 7 Changed: 5 Warnings: 0 mysql> select * from employee; +--------+----------+-----------+-------------------+--------+-------+ | emp_id | emp_name | city | designation | salary | perks | +--------+----------+-----------+-------------------+--------+-------+ | 1 | david | delhi | manager | 10000 | 855 | | 2 | shaha | mumbai | assistant manager | 10000 | 853 | | 3 | sha | puna | scales manager | 10000 | 850 | | 4 | jack | bangalore | designer | 10000 | 854 | | 5 | james | mangalore | web designer | 10000 | 1124 | | 6 | mike | chennai | develpoer | 10500 | 840 | +--------+----------+-----------+-------------------+--------+-------+ 6 rows in set (0.00 sec)[/sql] In the above example, before update, MySQL Triggers will fire the condition before updating a record inside the table or on the table, and update command is performed on the column salary(update if Salary=Salary>11000 and perks>500 then change to 10000). Then the salary of all the employee changes, as all the employee perks are greater then the applied condition.

MySQL After Insert trigger will fire the trigger after performing insert records values inside the table.

trigger_name =>The name of the trigger to be create. table_name => The accurate table in the database.

By viewing the below example, the concept of after insert trigger can be easily understood. [sql]mysql create table student_table(stu_id int,stu_name varchar(255),stu_class int ); Query OK, 0 rows affected (0.38 sec) mysql create table student_log1(user_id varchar(255),description varchar(255)); Query OK, 0 rows affected (0.48 sec) mysql CREATE TRIGGER student_insert AFTER insert ON student_table FOR EACH ROW BEGIN INSERT into student_log1(user_id,description) VALUES (user(),CONCAT('INSER T student records',new.stu_id,' ',new.stu_name,' ',new.stu_class)); END$$ Query OK, 0 rows affected (0.15 sec) mysql delimiter ; mysql insert into student_table values(1,'mike',10); Query OK, 1 row affected (0.10 sec) mysql insert into student_table values(2,'mad',20); Query OK, 1 row affected (0.11 sec) mysql insert into student_table values(3,'mack',30); Query OK, 1 row affected (0.10 sec) mysql select * from student_log1; +----------------+---------------------------------+ | user_id | description | +----------------+---------------------------------+ | root@localhost | INSERT student records1 mike 10 | | root@localhost | INSERT student records2 mad 20 | | root@localhost | INSERT student records3 mack 30 | +----------------+---------------------------------+ 3 rows in set (0.00 sec)[/sql] In the above example, after insert, trigger will fire the condition after inserting a record inside the table or on the table i.e, after creating student_insert table, it will fire on the student_log1 table and all the values will display on student_log1 table which was inserted in student_table. 

MySQL After update trigger of MySQL Triggers will fire the trigger after performing insert  records values inside the table.

trigger_name => The name of the trigger to be create. table_name => The accurate table in the database.

By viewing the below example, the concept of after update trigger can be easily understood. [sql]Create Table Student_Table02( Stu_Id int, Stu_Name varchar(255),Stu_Class int); create table stu_log02( user_id VARCHAR(255), description VARCHAR(255)); delimiter $$ mysql>CREATE TRIGGER stu_update ->AFTER UPDATE ON stu_table FOR EACH ROW ->BEGIN ->INSERT into stu_log(user_id, description) -> VALUES (user(), CONCAT('Update Student Record ->(',old.stu_id,' ',old.stu_name,' ',old.stu_class, ') to (',new.stu_id,' ',new.stu_name,' ',new.stu_class,')')); ->END$$ mysql>delimiter ; mysql> select * from student_table02; +--------+----------+-----------+ | stu_id | stu_name | stu_class | +--------+----------+-----------+ | 1 | david | 9 | | 2 | shah | 9 | | 3 | mike | 9 | | 4 | james | 9 | +--------+----------+-----------+ 4 rows in set (0.00 sec) mysql> update student_table02 set stu_class=stu_class+1; Query OK, 4 rows affected (0.20 sec) Rows matched: 4 Changed: 4 Warnings: 0 mysql> select * from student_table02; +--------+----------+-----------+ | stu_id | stu_name | stu_class | +--------+----------+-----------+ | 1 | david | 10 | | 2 | shah | 10 | | 3 | mike | 10 | | 4 | james | 10 | +--------+----------+-----------+ 4 rows in set (0.00 sec) mysql>select * from stu_log02; +----------------+--------------------------------------------------------+ | user_id | description | +----------------+--------------------------------------------------------+ | root@localhost | Update Student Record (1 david 9) to (1 david 10) | | root@localhost | Update Student Record (2 shah 9) to (2 shah 10) | | root@localhost | Update Student Record (3 mike 9) to (3 mike 10) | | root@localhost | Update Student Record (4 james 9) to (4 james 10) | +----------------+--------------------------------------------------------+ [/sql] In the above example, after update, trigger will fire the condition after update a record inside the table or on the table. i.e, after creating student_update table it will fire on the student_log2 table and all the values will display on student_log2 table which was inserted in student_table02.

MySQL Triggers automatically executes when an event is raised.

• Before Insert Trigger fire before inserting the values.
• Before Update Trigger fire before updating the values.
• After Insert Trigger fire after inserting the values.
• After Update Trigger fired updating the values.