Back to Top

How to use Triggers in MySQL

Almost all developers are heard about Triggers and all knows that MySQL support triggers and triggers are adding advantages to MySQL.Triggers are the SQL statements are stored in the database.

Triggers are the SQL statements which add functionality to your tables so that they perform a certain series of actions when a some queries are executed. We can say in easy language is Triggers are some conditions performed when INSERT, UPDATE or DELETE events are made in the table without using two separate queries.A trigger is a kind of database event which is used to activate on some event and the corresponding sql statement executed immediately.

Sometimes developers prefer to use store procedures rather than triggers but triggers are one kind of store procedures which contain procedural code into the body.The difference between a trigger and a stored procedure is that a trigger is called when an event occurs in a table whereas a stored procedure must be called explicitly.You will also like to read To pass limits with store procedure in MySQL and MySQL FLUSH Commands.

SYNTAX:

CREATE TRIGGER   trigger_name trigger_time
trigger_event ON table_name FOR EACH ROW
trigger_body

CREATE TRIGGER statement is used to create triggers.Trigger action time can be BEFORE or AFTER.You can use BEFORE when you want to process action before changes are made in the table and AFTER if you need to process action after changes are made in the table.trigger_body is the statement to execute when the trigger activates

trigger_event indicates the type of statement that activates the trigger. trigger_event can be one of the following values:

INSERT: When insert a new record into the table, trigger is activated. For example, Through INSERT, LOAD DATA, and REPLACE statements.

UPDATE: When update any of the record, activate the trigger using the UPDATE statements.

DELETE: When delete a record from the table, trigger is activated using DELETE and REPLACE statements.

Let’s start creating the trigger in MySQL with simple example.In the database, we have table customers as follows

CREATE TABLE customers (
     customer_id int(11) NOT NULL,
     lastName varchar(50) NOT NULL,
     firstName varchar(50) NOT NULL,
     PRIMARY KEY  ( customer_id )
)

Now let’s create relational table for customer.you need to create a new table called customer_info_rel.

CREATE TABLE customer_info_rel (
	id int(11) NOT NULL AUTO_INCREMENT,
	customer_id  int(11) NOT NULL,
	email varchar(50) NOT NULL,
	PRIMARY KEY (id)
)

So now I am going to apply trigger on the basis of the relational table.If I delete user or customer from main table customers then triggers occur which delete the record from relational table customer_info_rel also.

DELIMITER $$
CREATE TRIGGER trigger_customer BEFORE
	DELETE ON customers
	FOR EACH ROW
	BEGIN
		DELETE FROM customer_info_rel WHERE customer_id = OLD.id;
	END$$
DELIMITER;

NOTE: For Trigger you need to use DELIMITER $$ before the trigger and DELIMITER ; after the trigger.

ADVANTAGES:

  1. Triggers are the alternate way for data integrity.
  2. Trigger is useful when you want to audit the changes of data in a database table.

DISADVANTAGES: (Some points Which are not supported in Trigger)

  1. Transaction isn’t allow in trigger.
  2. We can’t use return statement in trigger.
  3. Can’t call store procedure with trigger.
  4. Can’t use trigger with views.

Next we will check for stored procedures.Go ahead & use MySQL triggers in your projects. If you have any question, feel free to ask via the comment section. If you find this tutorial useful, do share it on Facebook & Twitter.

Comments (1)

  1. Thank you for the particular smart critique. Me & my personal neighbour have been preparing to be diligent about that. All of us received an excellent book upon that issue from our nearby library and a lot books wherever not as influensive as your details. Now i’m really glad to see these information i was hunting for a lengthy period.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Most Popular Posts

The Readers’ Poll – October 2012

Posted on 6 years ago

Bhumi

How to use _remap function Codeigniter

Posted on 7 years ago

Bhumi

Hello world

Updated 1 month ago

Nick Carter

In depth Understanding of Laravel Core

Updated 3 months ago

Bhumi

ECMAScript 6 New Features – 2

Updated 1 year ago

Bhumi

The Reader’s Poll – June 2015

Posted on 4 years ago

Bhumi

The Reader’s Poll – August 2014

Posted on 5 years ago

Bhumi

The Reader’s Poll – June 2014

Posted on 5 years ago

Bhumi

The Readers’ Poll – May 2014

Posted on 5 years ago

Bhumi

The Readers’ Poll – August 2013

Posted on 6 years ago

Bhumi