首页 / MYSQL / mysql——触发器——示例
mysql——触发器——示例
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysql——触发器——示例,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含3862字,纯文字阅读大概需要6分钟。
内容图文
![mysql——触发器——示例](/upload/InfoBanner/zyjiaocheng/879/b594163cffe0447388df2946b7651dc4.jpg)
数据准备:
create table employee ( num int(50), d_id int(50), name varchar(50), age int(50), sex varchar(50), homeadd varchar(50) );
insert into employee values(1,1001,'zhangsan',26,'nan','beijing'); insert into employee values(2,1001,'lisi',24,'nv','hunan'); insert into employee values(3,1002,'wangwu',25,'nan','jiangsu'); insert into employee values(4,1004,'aric',15,'nan','yingguo');
select * from employee; create table department ( d_id int(50), d_name varchar(50), functione varchar(50), address varchar(50) );
insert into department values(1001,'keyanbu','yanfachanpin','3lou5hao'); insert into department values(1002,'shengchanbu','shengchanchanp','5louyiceng'); insert into department values(1003,'xiaoshoubu','cehuaxiaoshou','1louxiaoshoudating');
select * from department;
select * from employee;
select * from department;
==========================================================================
create trigger 触发器名 before| after 触发事件 on 表名 for each row 执行语句; ---------------------------------------------------------------------------- delimiter && create trigger 触发器名 before| after 触发事件 on 表名 for each row begin 执行语句列表 end && delimiter; 触发事件是指触发条件,包括insert、update、delete; 表名指触发事件操作的表的名称;
创建一个表:
create table trigger_time ( exec_time varchar(50) );
select * from trigger_time;
----------select now();
=================================================
创建一个触发器:
create trigger dept_trig1 before insert on department for each row insert into trigger_time values ( now() );
===========================================
为了演示,先删除一条记录,再添加进去:
delete from department where d_id = 1003;
=========================================
添加刚才删除的记录:
insert into department values(1003,'xiaoshoubu','cehuaxiaoshou','1louxiaoshoudating');
===================================================================
在department表insert时,触发器会被触发,我们查看下:
select * from trigger_time;
===========================================================
==========================================================
示例02:
创建第二个演示示例用的时间表:
create table trigger_time1 ( exec_time varchar(50) ); select * from trigger_time1;
==================================================================
创建第二个触发器:
delimiter && create trigger dept_trig2 after delete on department for each row begin insert into trigger_time1 values ( now() ); insert into trigger_time values ( now() ); end && delimi
================================================
执行删除语句:
delete from department where d_id = 1003;
==========================================
查看被删除记录的表以及2个时间表:
select * from department; select * from trigger_time; select * from trigger_time1;
=================================================================================================
查看触发器
1、查看数据库中所有触发器的信息:
show triggers;
========================================================================
2、在triggers表中查看触发器信息
mysql中所有触发器的定义都存在information_schema数据库下的triggers表中,查询triggers表,可以查询数据库中所有触发器的详细信息
select * from information_schema.triggers; /*查询所有*/
select * from information_schema.triggers where trigger_name = 'dept_trig1'; /*单个指定查询*/
注意:在激活触发器时,对触发器中的执行语句存在一些限制。而且触发器有问题,会阻止程序向下执行,而且数据不能回滚。
select * from information_schema.triggers; /*查询所有*/ select * from information_schema.triggers where trigger_name = 'dept_trig1'; select * from information_schema.triggers where trigger_name = 'dept_trig2';
==================================================
3、删除触发器
drop trigger 触发器名;
select * from information_schema.triggers; drop trigger dept_trig1; drop trigger dept_trig2;
内容总结
以上是互联网集市为您收集整理的mysql——触发器——示例全部内容,希望文章能够帮你解决mysql——触发器——示例所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。