Monday, April 4, 2016

MySQL Triggers

Image credit : onewoman365.wordpress.com


This is another important topic in DBMS. You may look following scenario. 

Think you are going to save some data of employees when they join to a company. You  need to save his name and email address only. Then this will be very simple. You can create a database and then you can create a table in this database adding columns for employee name and email with an id. Then you can insert data to that table. 

Think you need to update a separate table to save employee joining date with his id at the same time. This is normally done for auditing purposes. So then what you can do ? At the time Triggers are the best option to do this task. Lets see how to do it.

Create the database and tables


create database trigger_demo;

use trigger_demo;

create table employee(
    emp_id int primary key not null auto_increment,
    name varchar(45) not null,
    email_address varchar(45) not null
);

create table employee_count(
    count_id int primary key not null auto_increment,
    joined_date timestamp not null
);

If you have any doubts, please refer this.

Types of triggers

There are two types of triggers. 
  1. Single trigger
  2. Multiple trigger

Single trigger

Here you can see the declaration of a single trigger.

CREATE TRIGGER trigger_name trigger_time trigger_event ON
trigger_table FOR EACH ROW 
statement;

  • trigger_name = name of the trigger
  • trigger_time = BEFORE / AFTER
  • trigger_event = INSERT / DELETE / UPDATE
  • trigger_table = table name of trigger happening 
  • statement = statement which is executed according to the trigger
This is very easy to understand. CREATE TRIGGER, ON, FOR EACH ROW, INSERT, DELETE and UPDATE words are keywords in MySQL. 

Then you can choose  any name for trigger_name. You can't use keywords and it would be better if you can use understandable name for trigger_name. Look at the following example for trigger in above matter.

CREATE TRIGGER user_count after insert ON
employee FOR EACH ROW 
insert into employee_count(joined_date) values(now());


  • I've created a trigger called user_count. 
  • This trigger is called after you insert data into employee table.
  • When you insert data into employee table, it will automatically update the employee_count table according to the statement. (insert the time into joined_date column in employee_count table)
  • Then you can insert data and check how trigger perform and then you can view data to see updates on tables.

insert into employee(name, email_address) values('John', 'john.uk@live.com');
select * from employee;
select * from employee_count;


What is the Delimiter ?

  • This is used to define the boundary between two lines. In programming normally we use semicolon(;) to separate lines.
  • You can use a character or a String as the delimiter.
  • Normally in coding we put ; at the end of the line, it means you end the code. But if you use any other delimiter instead of using ; then you have to use that one at the end to end the line.

Multiple triggers

  • This is also same as single triggers. 
  • Only difference is, you can have multiple statements in multiple triggers.
  • We have to use delimiters in multiple triggers, because we are using multiple statements in multiple triggers.
  • Look at the following declaration.


DELIMITER |
CREATE TRIGGER trigger_name trigger_time trigger_event ON
trigger_table FOR EACH ROW 
BEGIN
statement_1;
statement_2;
statement_3;
END
DELIMITER ;


  • In the first line I've changed the delimiter(I'm using | as the delimiter)
  • Next lines are same as single triggers, only difference is we are using several statements inside of BEGIN and END block.
  • You will be able to understand why I changed the delimiter. You can see I've to use semicolon to separate statements (statement_1 ,statement_2 ,statement_3). 
  • If you didn't change the delimiter then once it read the statement_1 it will terminate the process without reading statement_2 and statement_3.
  • Because of this, we need to change the delimiter. 

Advantages of triggers


  • This provides the alternative way to check integrity of the data.
  • Triggers can be used to validate data 
  • Triggers are very helpful when auditing and to identify the changes on data.

Disadvantages of triggers


  • Triggers may decrease the performance of the database server.
  • Increase the complexity.
  • Application developers and database developers are separate.