Introduction to SQL Triggers

Introduction to SQL Triggers

SQL trigger is an SQL statements or a set of SQL statements which is stored to be activated or fired when an event associating with a database table occurs. The event can be any event including INSERT, UPDATE and DELETE.

Sometimes a trigger is referred as a special kind of stored procedure in term of procedural code inside its body. The difference between a trigger and a stored procedure is that a trigger is activated or called when an event happens in a database table, a stored procedure must be called explicitly. For example you can have some business logic to do before or after inserting a new record in a database table.

Before applying trigger in your database project, you should know its pros and cons to use it properly.

Advantages of using SQL trigger

• SQL Trigger provides an alternative way to check integrity.

• SQL trigger can catch the errors in business logic in the database level.

• SQL trigger provides an alternative way to run scheduled tasks. With SQL trigger, you don’t have to wait to run the scheduled tasks. You can handle those tasks before or after changes being made to database tables.

• SQL trigger is very useful when you use it to audit the changes of data in a database table.

Disadvantages of using SQL trigger

• SQL trigger only can provide extended validation and cannot replace all the validations. Some simple validations can be done in the application level. For example, you can validate input check in the client side by using javascript or in the server side by server script using PHP or ASP.NET.

• SQL Triggers executes invisibly from client-application which connects to the database server so it is difficult to figure out what happen underlying database layer.

• SQL Triggers run every updates made to the table therefore it adds workload to the database and cause system runs slower.

Trigger Implementation in MySQL

MySQL finally supports one of the most important features of an enterprise database server which is called trigger since version 5.0.2. Trigger is implemented in MySQL by following the syntax of standard SQL:2003. When you create a trigger in MySQL, its definition stores in the file with extension .TRG in a database folder with specific name as follows:

/data_folder/database_name/table_name.trg

The file is in plain text format so you can use any plain text editor to modify it.

While trigger is implemented in MySQL has all features in standard SQL but there are some restrictions you should be aware of like following:

• It is not allowed to call a stored procedure in a trigger.

• It is not allowed to create a trigger for views or temporary table.

• It is not allowed to use transaction in a trigger.
• Return statement is disallowed in a trigger.
• Creating a trigger for a database table causes the query cache invalidated. Query cache allows you to store the result of query and corresponding select statement. In the next time, when the same select statement comes to the database server, the database server will use the result which stored in the memory instead of parsing and executing the query again.
• All trigger for a database table must have unique name. It is allowed that triggers for different tables having the same name but it is recommended that trigger should have unique name in a specific database. To create the trigger, you can use the following naming convention: (BEFORE | AFTER)_tableName_(INSERT| UPDATE | DELETE)

Create the First Trigger in MySQL

Let’s start creating the first trigger in MySQL by following a simple scenario. In the sample database, we have table employees as follows:

CREATE TABLE `employees` (

`employeeNumber` int(11) NOT NULL,

`lastName` varchar(50) NOT NULL,

`firstName` varchar(50) NOT NULL,

`extension` varchar(10) NOT NULL,

`email` varchar(100) NOT NULL,

`officeCode` varchar(10) NOT NULL,

`reportsTo` int(11) default NULL,

`jobTitle` varchar(50) NOT NULL,

PRIMARY KEY (`employeeNumber`)

)

Now you want to keep the changes of employee's data in another table whenever data of an employee's record changed. In order to do so you create a new table called employees_audit to keep track the changes.

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)

)

In order to keep track the changes of last name of employee we can create a trigger that is fired before we make any update on the employees table. Here is the source code of the trigger

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 ;

You can test the trigger which created by updating last name of any employee in employees table. Suppose we update last name of employee which has employee number is 3:

UPDATE employees

SET lastName = 'Phan'

WHERE employeeNumber = 1056

Now when you can see the changes audited automatically in the employees_audit table by executing the following query

SELECT *

FROM employees_audit

In order to create a trigger you use the following syntax:

CREATE TRIGGER trigger_name trigger_time trigger_event

ON table_name

FOR EACH ROW

BEGIN

...

END

• CREATE TRIGGER statement is used to create triggers.
• The trigger name should follow the naming convention [trigger time]_[table name]_[trigger event], for example before_employees_update
• Trigger activation time can be BEFORE or AFTER. You must specify the activation time when you define a trigger. You use BEFORE when you want to process action prior to the change being made in the table and AFTER if you need to process action after changes are made.
• Trigger event can be INSERT, UPDATE and DELETE. These events cause trigger to fire and process logic inside trigger body. A trigger only can fire with one event. To define trigger which are fired by multiple events, you have to define multiple triggers, one for each event. Be noted that any SQL statements make update data in database table will cause trigger to fire. For example, LOAD DATA statement insert records into a table will also cause the trigger associated with that table to fire.
• A trigger must be associated with a specific table. Without a table trigger does not exist so you have to specify the table name after the ON keyword.
• You can write the logic between BEGIN and END block of the trigger.
• MySQL gives you OLD and NEW keyword to help you write trigger more efficient. The OLD keyword refers to the existing row before you update data and the NEW keyword refers to the new row after you update data.

In this tutorial, you have learned how to create the first trigger in MySQL. You've written the first trigger to audit changes of last name of employee in employees table.