修改mysql结束符 delimiter name
触发器语法：
create trigger 触发器名称
after/before 触发时间 //错误 ERROR 1362 (HY000): Updating of NEW row is not allowed in after trigger
insert/update/delete 监视事件
on 表明 监视地址
for each row //每一行受影响 ，触发器都执行，叫做行级触发器
begin
N条sql语句
new 新数据
old 旧数据
end
分析
监视谁： tablename
监视动作： insert delete update
触发时间： after
触发事件：update
查看已有的triggers: show triggers
删除已有triggers : drop trigger name
例： create trigger t1
after
insert
on shop
for each row
begin
insert into city (name,count) values(new.dealer,new.article);
end$
mysql> show triggers\G$
*************************** 1. row ***************************
Trigger: t5
Event: INSERT
Table: ord
Statement: begin
declare
rnum int;
select num into rnum from goods where gid=new.gid;
if new.much > rnum then
set new.much = rnum;
end if;
update goods set num = num-new.much where gid = new.gid;
end
Timing: BEFORE
Created: NULL
sql_mode: NO_ENGINE_SUBSTITUTION
Definer: root@localhost
character_set_client: gbk
collation_connection: gbk_chinese_ci
Database Collation: latin1_swedish_ci
*************************** 2. row ***************************
Trigger: t4
Event: UPDATE
Table: ord
Statement: begin
update goods set num = num+old.much-new.much where gid=old.gid;
end
Timing: BEFORE
Created: NULL
sql_mode: NO_ENGINE_SUBSTITUTION
Definer: root@localhost
character_set_client: gbk
collation_connection: gbk_chinese_ci
Database Collation: latin1_swedish_ci
*************************** 3. row ***************************
Trigger: t3
Event: DELETE
Table: ord
Statement: begin
update goods set num = num+old.much where gid=old.gid;
end
Timing: AFTER
Created: NULL
sql_mode: NO_ENGINE_SUBSTITUTION
Definer: root@localhost
character_set_client: gbk
collation_connection: gbk_chinese_ci
Database Collation: latin1_swedish_ci
3 rows in set (0.01 sec)
