mysql 触发器语法与应用示例

本文实例讲述了mysql 触发器语法与应用。分享给大家供大家参考，具体如下：

例子：创建触发器，记录表的增、删、改操作记录

//创建user表；
drop table if exists `user`;
create table `user` (
 `id` bigint(20) not null auto_increment,
 `account` varchar(255) default null,
 `name` varchar(255) default null,
 `address` varchar(255) default null,
 primary key (`id`)
) engine=innodb default charset=utf8;
 
//创建对user表操作历史表
drop table if exists `user_history`;
create table `user_history` (
 `id` bigint(20) not null auto_increment,
 `user_id` bigint(20) not null,
 `operatetype` varchar(200) not null,
 `operatetime` datetime not null,
 primary key (`id`)
) engine=innodb default charset=utf8;

delimiter：改变输入的结束符，默认情况下输入结束符是分号;，这里把它改成了两个分号;;，这样做的目的是把多条含分号的语句做个封装，全部输入完之后一起执行，而不是一遇到默认的分号结束符就自动执行；

• new：当触发插入和更新事件时可用，指向的是被操作的记录
• old： 当触发删除和更新事件时可用，指向的是被操作的记录

insert:

drop trigger if exists `tri_insert_user`;
delimiter ;;
create trigger `tri_insert_user` after insert on `user` for each row begin
  insert into user_history(user_id, operatetype, operatetime) values (new.id, 'add a user', now());
end
;;
delimiter ;

update:

drop trigger if exists `tri_update_user`;
delimiter ;;
create trigger `tri_update_user` after update on `user` for each row begin
  insert into user_history(user_id,operatetype, operatetime) values (new.id, 'update a user', now());
end
;;
delimiter ;

delete:

drop trigger if exists `tri_delete_user`;
delimiter ;;
create trigger `tri_delete_user` after delete on `user` for each row begin
  insert into user_history(user_id, operatetype, operatetime) values (old.id, 'delete a user', now());
end
;;
delimiter ;