How to Insert Multiple Triggers in MySQL
Triggers are one of the new features in MySQL, the relational database management system. Triggers are related to certain tables and are activated by a particular event. You can use triggers to check whether an insertion is performed or whether an update is done. A trigger is defined to activate when an "INSERT," "DELETE" or "UPDATE" statement executes for the related tables. A trigger can be set to activate before or after the triggering statements.
Other People Are Reading
Instructions
-
-
1
Log into your PHPMyAdmin as the root user. Enter your password. Click the existing database on the right pane of the PHPMyAdmin window. Create a new table by entering the name "employee" in the text box and specifying the number of fields. Click "Go." Create another two tables: "dept" and "new_employee."
-
2
Create fields for table "employee," such as "id," "name" and "department," on the following screen. Click the drop-down options in the "Type" column and select "INT" as id's data type, and "Char" as the other two fields' data types.
-
-
3
Create fields for "new_employee" and "dept" similarly. Create the same fields for "new_employee" as in "employee" table. Create two fields for "dept," namely "dept_name" as "CHAR" type and "employee_number" as "INT" data type.
-
4
Click "Insert" tab to insert data to the "employee" table. Enter "1," "Joe" and "Sales" in the corresponding text box in the "Value" column. Click "Go."
-
5
Click "Query" tab and enter the following code to create triggers:
CREATE TRIGGER NEW_HIRED
AFTER INSERT ON EMPLOYEE
FOR EACH ROW
BEGIN
INSERT INTO new_employee (id,name, department)
VALUES (new.id, new.name, new.department)
END
CREATE TRIGGER NUMBER_OF_EMPLOYEE
AFTER INSERT ON EMPLOYEE
FOR EACH ROW
UPDATE dept
SET employee_number = employee_number+1
WHERE employee.department=depart_name
The two triggers will do the following functions: New records are inserted into "new_employee" table and when you insert new records into the employee table. The number of employees in the related department is updated when a new employee is added.
-
1
