MySql Trigger Example

Trigger is the re-usable piece of code in mysql database, Trigger are precompiled database object, which gets executed automatically based on some events on database table.

• We cannot execute trigger explicitly from SQL code.
• Trigger gets executed automatically, when some changes happen in database table
• To Create a trigger in mysql, we use create trigger keywords

Here is the basic trigger in mysql database syntax.

We can specify when a trigger should gets fired like before or after INSERT | UPDATE| DELETE etc.

Create trigger in mysql

Let’s understand the syntax for creating trigger in mysql database.

CREATE TRIGGER triggerName
{BEFORE | AFTER} {INSERT | UPDATE| DELETE }
ON tableName FOR EACH ROW
trigger_body;

Before we start with trigger example, we need to have two tables to understand the impact.

1. Table one tbStudent, we create trigger on this table, when we insert, update or delete some action should happen.
2. Table two tbLog, this is just a normal table to capture the impact, we will inset some data to check if trigger working properly

use DatabaseName;
CREATE TABLE tbstudent (
  StuId int(11) NOT NULL,
  firstName varchar(100) DEFAULT NULL,
  LastName varchar(100) DEFAULT NULL,
  RegDate datetime DEFAULT NULL,
  email varchar(100) DEFAULT NULL,
  PRIMARY KEY (StuId)
)


create TABLE tbLog (
  lId int NOT NULL AUTO_INCREMENT,
  comment nvarchar(500) NOT NULL,
  lDate datetime NOT NULL,
  primary key(lId)
);

Here you will learn following mysql triggers example.

• mysql trigger after insert
• mysql trigger after update
• mysql trigger after delete
• drop trigger example
• alter trigger example

Here is one mysql trigger after insert example , when any new student added in tbstudent table one log message will be inserted into tblog.

delimiter |
create TRIGGER triLog after INSERT ON tbstudent
  FOR EACH ROW
  BEGIN
    INSERT INTO tblog
    (comment,ldate)
    values
    (concat(NEW.firstName, '', NEW.lastName, ' new student'), curdate());
  END;
|
delimiter ;

When we write any SQL statement, we use semicolon at the end of the statement like below.

Select * from tbStudent;
Select * from tbTeacher;
        

We want mysql client to treat the whole trigger as one statement, that’s why we need to define a DELIMITER with some different character then again set it back to original character.

DELIMITER delimiterCharacter

In above DELIMITER command you can use delimiterCharacter as single or double character like $$ or // etc, but make sure you don't use any system defined keyword or character, for example if you use backslash \ then that will be treated as escape character in MySQL.

Note : DELIMITER command will be case sensitive, for example if you define DELIMITER character as "AA" and then use as small letter "aa", then that will not work.

DELIMITER AA
 
select * from tbstudent AA
select * from tbemaillist AA
 
/* Change DELIMITER back to semicolon */  
DELIMITER ;

This trigger will be executed when any row is updated in tbStudent table, it will execute an insert query to tbLog table.

delimiter |
create TRIGGER triUpdateStu after update ON tbstudent
  FOR EACH ROW
  BEGIN
    INSERT INTO tblog
    (comment,ldate)
    values
    (concat(NEW.firstName, ' ', NEW.lastName, ' updated'), curdate());
  END;
|
delimiter ;

We execute the following update query for tbStudent table.

update tbstudent
set lastname='markson'
where StuId=2;

Now check the tbLog table, if a update log query inserted correctly

select * from tbLog;

This is mysql delete trigger example, after delete any student details from student table, we want to keep the history in log table, so inserting the details of deleted student into tbLog table.

delimiter |
create TRIGGER triDelStu after delete ON tbstudent
  FOR EACH ROW
  BEGIN
    INSERT INTO tblog
    (comment,ldate)
    values
    (concat(Old.firstName, ' ', Old.lastName, ' deleted'), curdate());
  END;
|
delimiter ;

To drop or delete any trigger you can use following statement, The statement will drop the trigger object if exists.

drop trigger if exists triLog;

Unfortunately, there is no ALTER TRIGGER or REPLACE TRIGGER in MySQL, We simply need to drop the trigger and create it again.

Here is how we can alter the above trigger, first drop the object and then create again.

First we lock the table, then drop the trigger, create the trigger and then unlock the table

LOCK TABLES tbstudent WRITE; drop trigger if exists triLog;
delimiter |
create TRIGGER triLog after INSERT ON tbstudent
FOR EACH ROW
BEGIN
INSERT INTO tblog
(comment,ldate)
values
(concat(NEW.firstName, ' ', NEW.lastName, ' new student'), curdate());
  END;
|
delimiter ;
UNLOCK TABLES;

Note: Without using lock statement also, you can drop and re-create the trigger, this is just considered as best practice, to lock the object when you are doing any modification.

You may be interested to know how to write trigger in MS SQL server.