Home » Mysql » Triggers in MySQL.

Triggers in MySQL.

A trigger is a set of sql statement (sql query) which is associated with a table and stored in the database catalog. A trigger is fired when a specified event (INSERT, UPDATE and DELETE) is fired on the table. Trigger can be invoked before or after the event.

Different Type of Triggers in MySQL

Triggers are available in MySQL 5.0.2 and later version. There are two type of triggers in MySQL

Before Triggers : before trigger are fired before the execution of DML Insert, Update and Delete operation.

After Triggers : After trigger are fired after the execution of DML Insert, Update and Delete operation.

Creating a Trigger

First we have start to create a trigger, to create demo database. I have Create my database with the name of demodata.

We have create a demo database for a blogging application. And create two tables for this tutorials, first table create post to save all post entry, and second one to save the updated record history in database.

post: stores a unique post ID, the title, description, and a deleted flag.

audit: stores a basic set of historical changes with a record ID, the post ID, the change type (NEW, EDIT or DELETE) and the date/time of that change.

The following SQL creates the posts and indexes the deleted column:

We have create a Posts table in demodata database

CREATE TABLE posts ( id mediumint(8) unsigned NOT NULL AUTO_INCREMENT, title text, description text, deleted tinyint(1) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (id), KEY ix_deleted (deleted) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='Blog post';

Insert some Test data in Posts Table

Now we have create second table with named audit`. All columns are indexed and a foreign key is defined for audit.post_id which references posts.id. Therefore, when we physically DELETE a post entry, it’s full audit history is also removed.

Basic syntax of trigger

trigger_name :  All triggers must have unique names with in a schema. But In different schemas we can use same name of the trigger.
trigger_time :  trigger_time is the trigger action time. It can be BEFORE / AFTER to indicate that the trigger active before or after the each row to be modified.
trigger_event :  trigger_event indicates the kind of operation[INSERT/UPDATE/DELETE] that activates the trigger. These trigger_event values are permitted:
tbl_name: Name of the table which is associate with this trigger.
trigger_body: set of sql statement which is executed when this trigger is fired

Create a Trigger in Mysql[After Update]
we have create a new Trigger which is fired when we update any record in the posts table. At this time over "post_after_update" Trigger is fired and insert a new record in "audit" tables.

Advantage of triggers :
To test the newly created trigger we have update a record in over posts table

Create a Trigger in Mysql[After Update]
we have create a new Trigger which is fired when we update any record in the posts table. At this time over "post_after_update" Trigger is fired and insert a new record in "audit" tables.

Test the trigger : 
To test the newly created trigger we have INSERT a record in over posts table

When we insert a record in posts table post_after_insert trigger is fired and a new entry insert in audit table also.We can also verify to check your audit table.

Triggers in MYSQL

Drop a Trigger in MySQL : 

, , , , , , , , ,

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">