mysql——触发器——示例

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

数据准备：

  1. create table employee ( num int(50),
  2.                         d_id int(50),
  3.                         name varchar(50),
  4.                         age int(50),
  5.                         sex varchar(50),
  6.                         homeadd varchar(50)
  7.                        );
  10. insert into employee values(1,1001,'zhangsan',26,'nan','beijing');
  11. insert into employee values(2,1001,'lisi',24,'nv','hunan');
  12. insert into employee values(3,1002,'wangwu',25,'nan','jiangsu');
  13. insert into employee values(4,1004,'aric',15,'nan','yingguo');
  16. select * from employee;
  20. create table department ( d_id int(50),
  21.                           d_name varchar(50),
  22.                           functione varchar(50),
  23.                           address varchar(50)
  24.                         );
  27. insert into department values(1001,'keyanbu','yanfachanpin','3lou5hao');
  28. insert into department values(1002,'shengchanbu','shengchanchanp','5louyiceng');
  29. insert into department values(1003,'xiaoshoubu','cehuaxiaoshou','1louxiaoshoudating');
  32. select * from department;

select * from employee;

493b48bef6e290b2ff7e0fea1a788fcf.png

select * from department;

bbf13810ed4811509a3a6bc0470e0666.png

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

  1. create trigger 触发器名  before| after 触发事件
  2. on 表名 for each row
  4. 执行语句；
  6. ----------------------------------------------------------------------------
  8. delimiter &&
  9. create trigger 触发器名  before| after 触发事件
  10. on 表名 for each row
  11.   begin
  12.         执行语句列表
  13.   end
  14.   &&
  15. delimiter;
  18. 触发事件是指触发条件，包括insert、update、delete;
  20. 表名指触发事件操作的表的名称；

创建一个表：

  1. create table trigger_time ( exec_time varchar(50)
  2.                           );
  6. select * from trigger_time;
  10. ----------select now();

156a896752f0db85c936e4bdfb62b3bc.png

9911ae2d6328d69cc3d2ee2fc33136ff.png

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

创建一个触发器：

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

488c143e2e2a1c496c7f8af652b3dc5f.png

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

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

  1. delete from department where d_id = 1003;

8f2ac4effb11c031ac5d77c6c51b9b2f.png

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

添加刚才删除的记录：

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

74652f94e792ec1922b7f7cb7b5833fc.png

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

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

  1. select * from trigger_time;

5439eba45664118b0ec6249bff9bee19.png

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

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

示例02：

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

  1. create table trigger_time1 ( exec_time varchar(50)
  2.                           );
  5. select * from trigger_time1;

652c3bd5f447ddeb0b484cb0dc245add.png

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

创建第二个触发器：

  1. delimiter &&
  2. create  trigger dept_trig2 after delete on department for each row 
  3. begin
  4.       insert into trigger_time1 values ( now() );
  5.       insert into trigger_time values ( now() );
  6. end 
  7. &&
  8. delimi

95d6cf464ab668671de8673b88c8349e.png

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

执行删除语句：

  1. delete from department where d_id = 1003;

468caba57c7f5d19197e67ed94de094e.png

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

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

  1. select * from department;
  2. select * from trigger_time;
  3. 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’; /*单个指定查询*/

注意：在激活触发器时，对触发器中的执行语句存在一些限制。而且触发器有问题，会阻止程序向下执行，而且数据不能回滚。

  1. select * from information_schema.triggers; /*查询所有*/
  3. select * from information_schema.triggers where trigger_name = 'dept_trig1'; 
  5. select * from information_schema.triggers where trigger_name = 'dept_trig2';

17c482b5031f4cdba76d59363d3644f6.png

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

3、删除触发器

drop trigger 触发器名；

  1. select * from information_schema.triggers;
  4. drop trigger dept_trig1;
  6. drop trigger dept_trig2;

a4a2f9df71a4a1b02a612d70c07cab45.png

发表评论

表情：
评论列表 （有 0 条评论，86人围观）

还没有评论，来说两句吧...

相关阅读