MySQL Tiggers

MySQL Tiggers

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.

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. 

Advantages:

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

Disadvantages:

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.

Trigger Implementation in MySQL

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)

Creating the first trigger in MySQL

-------- employees------------

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

)

--------------------- employees_audit--------------

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. 

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 ;