mysql 触发器

实现功能

当更新表1(name)的数据时,根据条件(name != 'lucifer'),将更新的数据插入表2(tmp)

 

1 建表

create table name(name varchar(20));
create table tmp(name varchar(20));

2 插入数据

insert into name(name) values('leon');
insert into name(name) values('lucifer');

3 建触发器

DELIMITER //
CREATE TRIGGER mt BEFORE UPDATE ON name FOR EACH ROW
BEGIN
if(old.name<>'lucifer') then
INSERT INTO tmp set name=new.name;
end if;
END
//

4 update数据,查看结果

结果1. name表中name字段值不为'lucifer',插入数据,结果正确.

update name set name='len' where name='leon';

select * from tmp;
+------+
| name |
+------+
| len |
+------+
1 row in set (0.00 sec)

结果2. name表中name字段等于'lucifer',未插入数据,结果正确

update name set name='aaa' where name='lucifer';

select * from tmp;
+------+
| name |
+------+
| len |
+------+
1 row in set (0.00 sec)