I'm using MySql 5.5 database and i have test1,test2,test3 and test4 databases. test1 database have 3 tables(Student,Employee,Trainer). test2 database have 3 tables(table1,table2,table3). Now my Question is
How to apply TRIGGER for test1 database ? so hear the TRIGGER is applied for Student,Employee,Trainer tables.
Please give me solution.
The general syntax of CREATE TRIGGER is : CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_statement
in the following example we are updating the Salary column of Employee table before inserting any record in Emp table. Example :
mysql> SELECT * FROM Employee;
+-----+---------+----------+-------------------+--------+-------+
| Eid | Ename | City | Designation | Salary | Perks |
+-----+---------+----------+-------------------+--------+-------+
| 1 | Rahul | Delhi | Manager | 10300 | 853 |
| 2 | Gaurav | Mumbai | Assistant Manager | 10300 | 853 |
| 3 | Chandan | Banglore | Team Leader | 15450 | 999 |
| 5 | Tapan | Pune | Developer | 20600 | 1111 |
| 6 | Amar | Chennai | Developer | 16000 | 1124 |
| 7 | Santosh | Delhi | Designer | 10000 | 865 |
| 8 | Suman | Pune | Web Designer | 20000 | 658 |
+-----+---------+----------+-------------------+--------+-------+
7 rows in set (0.00 sec)
mysql> delimiter //
mysql> CREATE TRIGGER ins_trig BEFORE INSERT ON Emp
-> FOR EACH ROW
-> BEGIN
-> UPDATE Employee SET Salary=Salary-300 WHERE Perks>500;
-> END;
-> //
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;
mysql> INSERT INTO Emp VALUES(9,'Rajesh','Delhi','Developer',15000,658);
Query OK, 1 row affected (0.05 sec)
mysql> SELECT * FROM Employee;
+-----+---------+----------+-------------------+--------+-------+
| Eid | Ename | City | Designation | Salary | Perks |
+-----+---------+----------+-------------------+--------+-------+
| 1 | Rahul | Delhi | Manager | 10000 | 853 |
| 2 | Gaurav | Mumbai | Assistant Manager | 10000 | 853 |
| 3 | Chandan | Banglore | Team Leader | 15150 | 999 |
| 5 | Tapan | Pune | Developer | 20300 | 1111 |
| 6 | Amar | Chennai | Developer | 15700 | 1124 |
| 7 | Santosh | Delhi | Designer | 9700 | 865 |
| 8 | Suman | Pune | Web Designer | 19700 | 658 |
+-----+---------+----------+-------------------+--------+-------+
7 rows in set (0.00 sec)
You create a trigger on a particular table in a particular database.
See triggers to see how this is done.
