【电气自动化会议】第八届信息科学、电气与自动化工程国际学术会议（ISEAE 2026）

SQL编程触发器什么是触发器?​ 当某个表的数据发生某件事(insert, delete, update), 然后自动触发预先编译好的若干条sql触发器

特点触发的操作和触发器的sql语句是一个事务操作具备原子性要么都执行要么都不执行

作用保证数据的完整性起到约束的作用示例1mysql create table emp_count( - emp_count_id int primary key auto_increment, - total int); Query OK, 0 rows affected (

07 sec) mysql show tables; -------------------- | Tables_in_dml_test | -------------------- | department | | emp_count | | employee | | user | -------------------- 4 rows in set (

00 sec) mysql insert into emp_count values(1,

; Query OK, 1 row affected (

01 sec) mysql select * from emp_count; --------------------- | emp_count_id | total | --------------------- | 1 | 0 | --------------------- 1 row in set (

00 sec) mysql update emp_count set total 18 where total 0; Query OK, 1 row affected (

01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql select * from emp_count; --------------------- | emp_count_id | total | --------------------- | 1 | 18 | --------------------- 1 row in set (

00 sec) #临时修改终止符以免与触发器语句发生冲突 mysql \d $ #delimiter $ mysql create trigger emp_count_p after insert - on employee for each row - begin - update emp_count set total total 1 where emp_count_id 1; - end - $ Query OK, 0 rows affected (

01 sec) mysql show triggers\G ***************************

row *************************** Trigger: emp_count_p Event: INSERT Table: employee Statement: begin update emp_count set total total 1 where emp_count_id 1; end Timing: AFTER Created:

20:10:

2

59 sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION Definer: root% character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci Database Collation: utf8mb4_0900_ai_ci 1 row in set (

01 sec) mysql insert into employee(number,name) values(23123213132,来俊希)$ Query OK, 1 row affected (

01 sec) mysql select * from emp_count$ --------------------- | emp_count_id | total | --------------------- | 1 | 19 | --------------------- 1 row in set (

00 sec)示例2mysql create table bank( - b_id int primary key auto_increment, - value int)$ Query OK, 0 rows affected (

03 sec) mysql create table u( - u_id int primary key auto_increment, - value int)$ Query OK, 0 rows affected (

04 sec) #创建触发器 mysql create trigger u_bank_t after insert - on u for each row - begin - update bank set value value 500 where b_id 1; - end - $ Query OK, 0 rows affected (

02 sec) #查看创建的触发器 mysql show triggers\G ***************************

row *************************** Trigger: emp_count_p Event: INSERT Table: employee Statement: begin update emp_count set total total 1 where emp_count_id 1; end Timing: AFTER Created:

20:10:

2

59 sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION Definer: root% character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci Database Collation: utf8mb4_0900_ai_ci ***************************

row *************************** Trigger: u_bank_t Event: INSERT Table: u Statement: begin update bank set value value 500 where b_id 1; end Timing: AFTER Created:

20:24:

5

28 sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION Definer: root% character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci Database Collation: utf8mb4_0900_ai_ci 2 rows in set (

00 sec) mysql insert into bank(b_id,value) values(1,

; Query OK, 1 row affected (

01 sec) mysql select * from bank; ------------- | b_id | value | ------------- | 1 | 0 | ------------- 1 row in set (

00 sec) mysql insert into u(u_id,value) values(1,

; Query OK, 1 row affected (

01 sec) mysql select * from bank; ------------- | b_id | value | ------------- | 1 | 500 | ------------- 1 row in set (

00 sec) mysql select * from u; ------------- | u_id | value | ------------- | 1 | 500 | ------------- 1 row in set (

00 sec) mysql select value from u where u_id 1; ------- | value | ------- | 500 | ------- 1 row in set (

00 sec) mysql select value from u group by u_id having max(value); ------- | value | ------- | 500 | ------- 1 row in set (

00 sec) mysql insert into bank(value) select value from u group by u_id having max(value); Query OK, 1 row affected (

02 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql select * from bank; ------------- | b_id | value | ------------- | 1 | 500 | | 2 | 500 | ------------- 2 rows in set (

00 sec)存储过程什么是存储过程事先经过编译并存储在数据库中的一段sql语句集合示例1mysql create procedure emp_count() - begin - select count(emp_count_id) from emp_count; - end - $ Query OK, 0 rows affected (

01 sec) #查看创建的存储过程 mysql show create procedure emp_count\G$ ***************************

row *************************** Procedure: emp_count sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION Create Procedure: CREATE DEFINERroot% PROCEDURE emp_count() begin select count(emp_count_id) from emp_count; end character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci Database Collation: utf8mb4_0900_ai_ci 1 row in set (

00 sec) #调用存储过程 mysql call emp_count(); - $ --------------------- | count(emp_count_id) | --------------------- | 1 | --------------------- 1 row in set (

00 sec)示例2mysql create table t1( - id int primary key auto_increment, - password varchar(

); - $ Query OK, 0 rows affected (

03 sec) mysql create procedure insert_many_date(in total_row) - begin - declare i int default 1; - while (i rows) do - insert into t1 values(i,md5(i)); - set i i 1; - end while; - end - $ mysql create procedure user_password(in pwd varchar(

) - begin - select * from user where password pwd; - end$ Query OK, 0 rows affected (

01 sec) mysql call user_password(

$ Empty set (

01 sec)示例3mysql select * from user; - $ ----------------------------------------------- | user_id | username | password | name | hire_date | ----------------------------------------------- | 1 | ljx | ljxbbfjw | cj |

| | 2 | ljx | ljxbbfjw | cj |

| | 3 | lisi | 123 | ls |

| | 4 | wangwu | 123 | ww |

| | 5 | cj | 123 | cj |

| ----------------------------------------------- 5 rows in set (

00 sec) mysql create procedure user_password(in pwd varchar(

) - begin - select * from user where password pwd; - end$ Query OK, 0 rows affected (

01 sec) mysql call user_password(

$ ----------------------------------------------- | user_id | username | password | name | hire_date | ----------------------------------------------- | 3 | lisi | 123 | ls |

| | 4 | wangwu | 123 | ww |

| | 5 | cj | 123 | cj |

| ----------------------------------------------- 3 rows in set (

00 sec) Query OK, 0 rows affected (

00 sec)示例4mysql select a; - $ ------------ | a | ------------ | NULL | ------------ 1 row in set (

00 sec) mysql set a 1; - $ Query OK, 0 rows affected (

01 sec) mysql select a; - $ ------ | a | ------ | 1 | ------ 1 row in set (

00 sec) mysql \d $ mysql create procedure user_count_a(out number int) - begin - select count(

into number from user; - end$ Query OK, 0 rows affected (

01 sec) mysql show create procedure user_count_a\G$ ***************************

row *************************** Procedure: user_count_a sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION Create Procedure: CREATE DEFINERroot% PROCEDURE user_count_a(out number int) begin select count(

into number from user; end character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci Database Collation: utf8mb4_0900_ai_ci 1 row in set (

00 sec) mysql call user_count_a(a); - $ Query OK, 1 row affected (

01 sec) mysql select a - $ ------ | a | ------ | 5 | ------ 1 row in set (

00 sec) mysql \d ; mysql select count(

from user; ---------- | count(

| ---------- | 5 | ---------- 1 row in set (

00 sec)示例5mysql \d $ mysql create procedure count_emp_name(in dep_name varchar(

,out count_emp int) - begin - select count(*) into count_emp from employee where department_NO (select number from department where name dep_name); - end$ Query OK, 0 rows affected (

02 sec) mysql \d ; mysql show create procedure count_emp_name\G; ***************************

row *************************** Procedure: count_emp_name sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION Create Procedure: CREATE DEFINERroot% PROCEDURE count_emp_name(in dep_name varchar(

,out count_emp int) begin select count(*) into count_emp from employee where department_NO (select number from department where name dep_name); end character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci Database Collation: utf8mb4_0900_ai_ci 1 row in set (

00 sec) mysql call count_emp_name(上海中心,a); Query OK, 1 row affected (

00 sec) mysql select a - ; ------ | a | ------ | 2 | ------ 1 row in set (

00 sec) mysql select count(*) from employee where department_NO (select number from department where name 上海中心); ---------- | count(*) | ---------- | 2 | ---------- 1 row in set (

00 sec)

jmcomic.3.0.mic2.0.3sdf-jmcomic.3.0.mic2.0.3sdf最新版v.29.47.08-2285安卓网应用