Prem Tiwari - - Mysql, Oracle
Introduction to SQL Trigger
A SQL trigger is a special type of stored procedure. It is special because it is not called directly like a stored procedure. The main difference between a trigger and a stored procedure is that a trigger is called automatically when a data modification event is made against a table whereas a stored procedure must be called explicitly. It is the easy step to know How to create and use Mysql Trigger.
It is important to understand SQL trigger’s advantages and disadvantages so that you can use it appropriately. In the following sections, we will discuss about the advantages and disadvantages of using SQL triggers
Advantages of using SQL triggers
Disadvantages of using SQL triggers
How to Create Trigger in MySQL
In order to create a trigger you use the CREATE TRIGGER statement. The following illustrates the syntax of the CREATE TRIGGER statement
1 2 3 4 5 6 | CREATE TRIGGER trigger_name trigger_time trigger_event ON table_name FOR EACH ROW BEGIN ... END |
MySQL trigger example
First, we have employees table in our MySQL sample database as follows :
Second, we create a new table named employees_audit to keep the changes of the employee records. The following script creates the employee_audit table
1 2 3 4 5 6 7 8 9 | CREATE TABLE employees_audit ( id int(11) NOT NULL AUTO_INCREMENT, employeeNumber int(11) NOT NULL, lastname varchar(50) NOT NULL, changedon datetime DEFAULT NULL, action varchar(50) DEFAULT NULL, PRIMARY KEY (id) ) |
Third, we create a BEFORE UPDATE trigger to be invoked before a change is made to the employees table.
1 2 3 4 5 6 7 8 9 10 11 12 | DELIMITER $$ CREATE TRIGGER before_employee_update BEFORE UPDATE ON employees FOR EACH ROW BEGIN INSERT INTO employees_audit SET action = 'update', employeeNumber = OLD.employeeNumber, lastname = OLD.lastname, changedon = NOW(); END$$ DELIMITER ; |
If you take a look at the schema, you will see before_employee_update trigger under the employees table as follows:
Tags: Database
