Bookmark and Share
MySQL trigger
(Publish Date: 2009-9-17 11:36pm, Total Visits: 1162, Today: 1, This Week: 1, This Month: 7)

Test a mysql trigger in MySQL replication.

1. create a table on master

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

2. create a trigger on master

delimiter //
create trigger bi_emps_fer before insert on emps for each row
begin

declare namelength numeric;
set namelength = length(new.emp_name);
set new.salary = new.salary * namelength;

end
//

4. "show triggers" on both master and slave, trigger is replicated to
the slave automatically.

5. on master

insert into emps values(4,'JoJo','1',null,1000);

6. check data on the master and slave. The result is the same.

mysql> select * from emps;
+--------+----------+---------+-----------+---------+
| emp_id | emp_name | dept_id | dept_name | salary |
+--------+----------+---------+-----------+---------+
| 1 | Roger | 1 | NULL | 2000.00 |
| 2 | John | 2 | NULL | 2500.00 |
| 3 | Alan | 1 | NULL | 2100.00 |
| 5 | JoJo | 1 | NULL | 4000.00 |
+--------+----------+---------+-----------+---------+
4 rows in set (0.00 sec)

The example is from:

http://forge.mysql.com/wiki/Triggers