Creating a Trigger
Topic Extracted from Knowledge Xpert for MySQL

A trigger is a stored unit of code that is attached to a table within the database. A trigger cannot be called or used in a select statement in the same way a procedure or function can but are called automatically when an action takes place on the table it is associated with. There are 12 different types of triggers that are possible and these can be split into 3 different groups, they all have the same functionality and limitations but are called at different stages of a table action.

To create a trigger the code needs to be entered into the MySQL command line either by typing it in long hand or by using a source file. In this topic triggers will be created against the tables contained in the setup.myp script.

drop database if exists pers
g
create database pers
g
use pers
g
create table emps(emp_id int NOT NULL,
             emp_name varchar(30),
             dept_id int,
             dept_name varchar(30),
             salary decimal(7,2),
             primary key(emp_id))
g
insert into emps (emp_id,emp_name,dept_id,salary)
            values (1,'Roger',1,2000.00),(2,'John',2,2500.00),(3,'Alan',1,2100.00)
g
select * from emps
g
create table dept (dept_id int NOT NULL,
             description varchar(30),
             primary key(dept_id))
g
insert into dept (dept_id,description)
            values (1,'Information Technology'),(2,'Sales')
g
select * from dept
g

The first trigger will be a simple one line trigger to demonstrate the basics of trigger creation. The clients are a group of parallel universe accountants and when ever they enter any names they do so in reverse order. You need to create a simple trigger so that on entry of names into the emps table the value is reversed.

Before creating the trigger, there are a number of decisions to be made. First is what to call the trigger. MySQL makes it slightly easier in the fact you can have only one trigger per type per table. Therefore use the table name and then add what type of trigger it is. For example, with a BEFORE UPDATE trigger which effects all rows, use the following naming convention:

BU_tablename_FER

To extend the convention if you wanted an AFTER INSERT trigger, it would be

AI_tablename

and so on. It's useful to have a name that can be easily found in the future.

Now you want to update each emp_name column of the emps table when a record is inserted. You also want to change the values the trigger needs to be fired before the action is completed. Using this information, you want a BEFORE INSERT ON emps FOR EACH ROW trigger. The specification for this would be as follows:

CREATE TRIGGER bi_emps_fer BEFORE INSERT OF emps FOR EACH ROW...

You can see that the naming convention has been used. Once the specification of the trigger is complete the trigger code is then added, in this case reverse the emp_name column.

OLD and NEW

Rather than updating the table by name, use the OLD and NEW keywords to access the data in the record. OLD is used for the values of the column before the change was made and NEW holds the value of the column after the column has changed. Therefore in your program, access the emp_name column using new.emp_name.

create trigger bi_emps_fer before insert on emps for each row
set new.emp_name := reverse(new.emp_name);

This is a simple one line trigger so you do not need to enclose the code within a BEGIN and END. Unlike procedures or functions, triggers can not invoked manually. They must be called as a result of the relevant table action. To fire the trigger a record needs to be inserted into the relevant table.

insert into emps (emp_id,emp_name) values (4,'Dave');
Query OK, 1 row affected (0.03 sec)

At this stage there is no visual clue that the trigger has actually fired. Selecting against the table will show use if the trigger has fired and reversed the emp_name column.

mysql> select * from emps where emp_id = 4;
+--------+----------+---------+-----------+--------+
| emp_id | emp_name | dept_id | dept_name | salary |
+--------+----------+---------+-----------+--------+
|      4 | evaD     |    NULL | NULL      |   NULL |
+--------+----------+---------+-----------+--------+
1 row in set (0.02 sec)

This shows that the column was indeed reversed so the trigger must have fired. This was a relatively simple and contrived example of a trigger.