mysql——触发器——示例

数据准备:

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;

mysql——触发器——示例

 

 select * from department;

mysql——触发器——示例

 

 

==========================================================================

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();

mysql——触发器——示例

 

 mysql——触发器——示例

 

 

 

=================================================

创建一个触发器:

create  trigger dept_trig1 before insert on department for each row insert into trigger_time values ( now() );

mysql——触发器——示例

 

 

 

===========================================

为了演示,先删除一条记录,再添加进去:

delete from department where d_id = 1003;

mysql——触发器——示例

 

 

 

 

 

=========================================

添加刚才删除的记录:

insert into department values(1003,'xiaoshoubu','cehuaxiaoshou','1louxiaoshoudating'); 

mysql——触发器——示例

 

 

 

 

 

===================================================================

在department表insert时,触发器会被触发,我们查看下:

select * from trigger_time;

mysql——触发器——示例

 

 

===========================================================

==========================================================

 

示例02:

创建第二个演示示例用的时间表:

create table trigger_time1 ( exec_time varchar(50)
                          );


select * from trigger_time1;

mysql——触发器——示例

 

 

 

 

==================================================================

 

创建第二个触发器:

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

 

 mysql——触发器——示例

 

 

 

 

 

 

================================================

执行删除语句:

delete from department where d_id = 1003;

mysql——触发器——示例

 

 

 

 

 

 

==========================================

查看被删除记录的表以及2个时间表:

select * from department;
select * from trigger_time;
select * from trigger_time1;

mysql——触发器——示例

 

 

mysql——触发器——示例

 

 mysql——触发器——示例

 

 

 

 

 

=================================================================================================

查看触发器

1、查看数据库中所有触发器的信息:

       show triggers;

 

mysql——触发器——示例

 

 

 

 

========================================================================

 

 

 

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'; 

mysql——触发器——示例

 

 

 

 

 

 

 

 

==================================================

 

3、删除触发器

     drop trigger 触发器名;

 

select * from information_schema.triggers;


drop trigger dept_trig1;

drop trigger dept_trig2;

mysql——触发器——示例

上一篇:进阶6:连接查询 二、sql99语法


下一篇:LanderHu Mysql学习笔记(008)-连接查询