Home > MySql > Triggers in MySQL

Triggers in MySQL

MySQL is indeed the most powerful database management system and the reason for this lies in a number of tools integrated with it. Triggers in MySQL are the most significant methods to automate the updating of one table in the database, corresponding to any type of change brought in other table. Triggers are fastest way to reduce the probability of inconsistent data occurring in database, without demanding the interference of database user. Here is the fact list regarding triggers in MySQL, which may help you to understand these codes in an accurate manner:

  • Triggers in MySQL reside on the server side and thus, consumes less time to get activated and perform the task for which these are created.
  • These triggers are activated in response to SQL queries submitted to the database. Thus, you don’t need to follow any specific procedure to trigger them.
  • Insert, Update and Delete are the queries employed on a particular table, which encourage the triggers applied on the table to arouse and do the needed function.

How To Create Triggers

The triggers in MySQL are created using a simple syntax, as mentioned below:

CREATE TRIGGER triggername1 AFTER INSERT ONtablename1 FOR EACH ROW UPDATE tablename2 perform function;

Here is the explanation of the above code:

  1. CREATE TRIGGER statement is used to create a trigger on table (tablename1).
  2. AFTER INSERT defines the SQL query which must be used to activate this particular trigger. You may also use AFTER DELETE syntax to set a trigger on work.
  3. UPDATE syntax is used to bring in the modifications in another table (tablename2).
  4. Finally a particular function is assigned to the trigger, which will be performed by it, once it gets activated in the above statement.

SHOW TRIGGER statement is used to list all active triggers finding space on the server side of your database. This will also provide information about various attributes and statements associated with different triggers.

The triggers in MySQL can’t be changed simply using ALTER statement or any other procedure. Thus, one has to DROP the existing trigger and then CREATE a new one, specifying the expected modifications.

A Simple Example
Suppose you have two tables named sell and inventory in your database. The sell table is used to keep the record of sales made in a day and table inventory maintains the record of total quantity of each product in the store. Now, corresponding to each sale, the inventory table must be updated to decrease the count of a product on each successful sale. Thus, one may define a trigger to update inventory, with each sale record updated in table sell.

Advantages Of Using Triggers

Triggers in MySQL exhibit a number of advantages to the users and thus, have caught popularity amongst database users. Here are most prominent advantages offered by triggers:

  1. Triggers help in maintaining the integrity of database and thus, there is no scope of wrong information stored in the data tables.
  2. Triggers don’t require the user to add separate application code for activation and thus, it saves the efforts of database designer.
  3. Triggers are easy to activate and most importantly, consume less time in this process.
  4. Triggers are easy to get ported from one platform to another, along with complete module of the database.

MySql

Comments are closed.