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. 



Final Vs Finally Vs Finalize()



This is very important interview question. Lets get one by one.

Final

Final is a non access modifier that can be used with a variables, methods and also classes.

Final variables
    • Final variables cannot be changed, because they are constants. 
    • Look at the following code, it won't compile.

public class Fruits {

   public static void main(String[] args) {
       final int MAX_DIS = 5;
       MAX_DIS = 10;
  
       System.out.println(MAX_DIS);
   }
}


Final methods
    • Final methods cannot be overridden. 
    • Following code won't compile.

public class Fruits {
   final void eat() { }
}

class Apple extends Fruits {
   void eat() { }
}


Final classes
    • Final classes cannot be extended.
    • Following code won't compile.

public final class Fruits {

}

class Apple extends Fruits {

}


Finally

  • This is always associated with try-catch block.
  • It is normally used to handle any clean up codes like closing a file, closing a database to prevent resource leak.
  • This finally block runs every time either exception happen or not.
  • Look at the following example.

public class FinallyBlockDemo {

   public static void main(String args[]) {

       double num1=0, num2=0;
       Scanner scn = new Scanner(System.in);

       try{
           System.out.print("Enter number one: ");
           num1 = scn.nextInt();

           System.out.print("Enter number two: ");
           num2 = scn.nextInt();

           double div = num1 + num2;
           System.out.println("Answer is : " + div);
   
       }catch(InputMismatchException e){
            System.out.println("Please enter number only.");

       }finally{
            System.out.println("Service stopped.");

       }
   }
}


Finalize() 

  • This method is presented in Object class (Java.lang.Object.finalize())
  • This is called by the Garbage collector just before destroying any object.
  • If it determines any object without a reference, it calls this method anytime. 
  • This is used to perform clean up activities related to any object without reference.
  • This cleanup activities means, if the object is associated with any database connection, finalize() method is used to disconnect that connection.