How to create and use Mysql Trigger

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

mysql-trigger

Advantages of using SQL triggers

SQL triggers provide an alternative way to check the integrity of data.
SQL triggers can catch errors in business logic in the database layer.
SQL triggers provide an alternative way to run scheduled tasks. By using SQL triggers, you don’t have to wait to run the
scheduled tasks because the triggers are invoked automatically before or after a change is made to the data in tables.
SQL triggers are very useful to audit the changes of data in tables.

Disadvantages of using SQL triggers

SQL triggers only can provide an extended validation and they cannot replace all the validations. Some simple validations have to be done in the application layer. For example, you can validate user’s inputs in the client side by using JavaScript or in the server side using server side scripting languages such as JSP, PHP, ASP.NET, Perl, etc.
SQL triggers are invoked and executed invisibly from client-applications therefore it is difficult to figure out what happen in the database layer.
SQL triggers may increase the overhead of the database server.

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

CREATE TRIGGER trigger_name trigger_time trigger_event
 ON table_name
 FOR EACH ROW
 BEGIN
 ...
 END

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 :

employees-table

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

 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) 
 )

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.

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 ;

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:

MySQL-Trigger-Example

If you like FreeWebMentor and you would like to contribute, you can write an article and mail your article to [email protected] Your article will appear on the FreeWebMentor main page and help other developers.

How to create and use Mysql Trigger

Recommended Posts:

webmentor