CREATE　[DEFINER = { 'user' | CURRENT_USER }]　
TRIGGER trigger_name
trigger_time trigger_event
ON table_name
FOR EACH ROW
[trigger_order]
trigger_body

DELIMITER $
... --觸發(fā)器創(chuàng)建語(yǔ)句；
$  --提交創(chuàng)建語(yǔ)句；
DELIMITER ;

-- 變量直接賦值
set @num=999;
 
-- 使用select語(yǔ)句查詢出來(lái)的數(shù)據(jù)方式賦值，需要加括號(hào)：
set @name =(select name from table);

-- 簡(jiǎn)單的if語(yǔ)句：
set sex = if (new.sex=1, '男', '女');
 
-- 多條件if語(yǔ)句：
if old.type=1 then
  update table ...;
elseif old.type=2 then
  update table ...;
end if;

-- 通過(guò)information_schema.triggers表查看觸發(fā)器：
select * from information_schema.triggers;
 
-- mysql 查看當(dāng)前數(shù)據(jù)庫(kù)的觸發(fā)器
show triggers;
 
-- mysql 查看指定數(shù)據(jù)庫(kù)"aiezu"的觸發(fā)器
show triggers from aiezu;

drop trigger trigger_name;

drop trigger if exists trigger_name

-- 創(chuàng)建測(cè)試表
drop table if exists tmp1;
create table tmp1 (n1 int, n2 int);
 
-- 創(chuàng)建觸發(fā)器
DELIMITER $
drop trigger if exists tmp1_insert$
create trigger tmp1_insert
before insert on tmp1
for each row
begin
  set new.n2 = new.n1*5;
end$
DELIMITER ;

mysql> insert tmp1(n1) values(18);
Query OK, 1 row affected (0.01 sec)
 
mysql> insert tmp1(n1) values(99);
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from tmp1;
+------+------+
| n1  | n2  |
+------+------+
|  18 |  90 |
|  99 | 495 |
+------+------+
2 rows in set (0.00 sec)

-- 創(chuàng)建測(cè)試表和插入測(cè)試數(shù)據(jù)
drop table if exists tmp1;
drop table if exists tmp2;
create table tmp1 (id int, name varchar(128)) default charset='utf8';
create table tmp2 (fid int, name varchar(128)) default charset='utf8';
insert into tmp1 values(1, '愛(ài)E族');
insert into tmp2 values(1, '愛(ài)E族');
 
-- 創(chuàng)建觸發(fā)器
DELIMITER $
drop trigger if exists tmp1_update$
create trigger tmp1_update
after update on tmp1
for each row
begin
  update tmp2 set name=new.name where fid=new.id;
end$
DELIMITER ;

mysql> select * from tmp1;
+------+---------+
| id  | name  |
+------+---------+
|  1 | 愛(ài)E族  |
+------+---------+
1 row in set (0.00 sec)
 
mysql> select * from tmp2;
+------+---------+
| fid | name  |
+------+---------+
|  1 | 愛(ài)E族  |
+------+---------+
1 row in set (0.00 sec)
 
mysql> update tmp1 set name='aiezu.com' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
 
mysql> select * from tmp1;
+------+-----------+
| id  | name   |
+------+-----------+
|  1 | aiezu.com |
+------+-----------+
1 row in set (0.00 sec)
 
mysql> select * from tmp2;
+------+-----------+
| fid | name   |
+------+-----------+
|  1 | aiezu.com |
+------+-----------+
1 row in set (0.00 sec)