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

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

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.

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.

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

Most Popular Posts

LESS CSS – Nested Rules

Posted on 9 years ago

Bhumi

The Readers’ Poll – December 2012

Posted on 11 years ago

Bhumi