mysql——触发器——示例

向右看齐 2023-10-02 08:49 25阅读 0赞

数据准备：

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

![493b48bef6e290b2ff7e0fea1a788fcf.png][]

**select \* from department;**

![bbf13810ed4811509a3a6bc0470e0666.png][]

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

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

![156a896752f0db85c936e4bdfb62b3bc.png][]

![9911ae2d6328d69cc3d2ee2fc33136ff.png][]

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

创建一个触发器：

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

![488c143e2e2a1c496c7f8af652b3dc5f.png][]

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

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

delete from department where d_id = 1003;

![8f2ac4effb11c031ac5d77c6c51b9b2f.png][]

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

添加刚才删除的记录：

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

![74652f94e792ec1922b7f7cb7b5833fc.png][]

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

在department表insert时，触发器会被触发，我们查看下：

select * from trigger_time;

![5439eba45664118b0ec6249bff9bee19.png][]

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

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

示例02：

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

create table trigger_time1 ( exec_time varchar(50)
                              );
    
    
    select * from trigger_time1;

![652c3bd5f447ddeb0b484cb0dc245add.png][]

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

创建第二个触发器：

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

![95d6cf464ab668671de8673b88c8349e.png][]

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

执行删除语句：

delete from department where d_id = 1003;

![468caba57c7f5d19197e67ed94de094e.png][]

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

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

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

![ef9ff54d5b18488c7b493d91422843c2.png][]

![a52dade593273bb4bed6ed4080aa93e5.png][]

![bc5fa9cbbeee94e88492e33055039c35.png][]

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

查看触发器

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

show triggers;

![290fce1d9e721357c6942e779bc5bb8d.png][]

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

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

![17c482b5031f4cdba76d59363d3644f6.png][]

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

3、删除触发器

drop trigger 触发器名；

select * from information_schema.triggers;
    
    
    drop trigger dept_trig1;
    
    drop trigger dept_trig2;

![a4a2f9df71a4a1b02a612d70c07cab45.png][]

[493b48bef6e290b2ff7e0fea1a788fcf.png]: https://img-blog.csdnimg.cn/img_convert/493b48bef6e290b2ff7e0fea1a788fcf.png
[bbf13810ed4811509a3a6bc0470e0666.png]: https://img-blog.csdnimg.cn/img_convert/bbf13810ed4811509a3a6bc0470e0666.png
[156a896752f0db85c936e4bdfb62b3bc.png]: https://img-blog.csdnimg.cn/img_convert/156a896752f0db85c936e4bdfb62b3bc.png
[9911ae2d6328d69cc3d2ee2fc33136ff.png]: https://img-blog.csdnimg.cn/img_convert/9911ae2d6328d69cc3d2ee2fc33136ff.png
[488c143e2e2a1c496c7f8af652b3dc5f.png]: https://img-blog.csdnimg.cn/img_convert/488c143e2e2a1c496c7f8af652b3dc5f.png
[8f2ac4effb11c031ac5d77c6c51b9b2f.png]: https://img-blog.csdnimg.cn/img_convert/8f2ac4effb11c031ac5d77c6c51b9b2f.png
[74652f94e792ec1922b7f7cb7b5833fc.png]: https://img-blog.csdnimg.cn/img_convert/74652f94e792ec1922b7f7cb7b5833fc.png
[5439eba45664118b0ec6249bff9bee19.png]: https://img-blog.csdnimg.cn/img_convert/5439eba45664118b0ec6249bff9bee19.png
[652c3bd5f447ddeb0b484cb0dc245add.png]: https://img-blog.csdnimg.cn/img_convert/652c3bd5f447ddeb0b484cb0dc245add.png
[95d6cf464ab668671de8673b88c8349e.png]: https://img-blog.csdnimg.cn/img_convert/95d6cf464ab668671de8673b88c8349e.png
[468caba57c7f5d19197e67ed94de094e.png]: https://img-blog.csdnimg.cn/img_convert/468caba57c7f5d19197e67ed94de094e.png
[ef9ff54d5b18488c7b493d91422843c2.png]: https://img-blog.csdnimg.cn/img_convert/ef9ff54d5b18488c7b493d91422843c2.png
[a52dade593273bb4bed6ed4080aa93e5.png]: https://img-blog.csdnimg.cn/img_convert/a52dade593273bb4bed6ed4080aa93e5.png
[bc5fa9cbbeee94e88492e33055039c35.png]: https://img-blog.csdnimg.cn/img_convert/bc5fa9cbbeee94e88492e33055039c35.png
[290fce1d9e721357c6942e779bc5bb8d.png]: https://img-blog.csdnimg.cn/img_convert/290fce1d9e721357c6942e779bc5bb8d.png
[17c482b5031f4cdba76d59363d3644f6.png]: https://img-blog.csdnimg.cn/img_convert/17c482b5031f4cdba76d59363d3644f6.png
[a4a2f9df71a4a1b02a612d70c07cab45.png]: https://img-blog.csdnimg.cn/img_convert/a4a2f9df71a4a1b02a612d70c07cab45.png