Triggers in MySQL

Jan 8, 2012 by Bhumi

Almost all developers are heard about Triggers and all knows that mysql support triggers and triggers are adding an advantages to mysql.Triggers are the SQL statements are stored in 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.

Sometimes developers are prefer to use store procedures rather than triggers but triggers are one kind of store procedures which contain procedural code into 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.

SYNTAX:

CREATE TRIGGER   trigger_name trigger_time
trigger_event ON table_name
FOR EACH ROW
trigger_body

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

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

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

CREATE TABLE <code>customers</code> (

<code>customer_id</code> int(11) NOT NULL,

<code>lastName</code> varchar(50) NOT NULL,

<code>firstName</code> varchar(50) NOT NULL,

PRIMARY KEY (<code>customer_id</code>)

)

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

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 basis of relational table.If i delete user or customer from main table customers then triggers are occurs which delete 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 ;