1.创建数据库test3:create databse test3;
2.创建表t2:
use test3;
create table t2 (id int,name char(50),dtime timestamp);
mysql> select * from t2;
Empty set (0.00 sec)
3.创建procedure来循环插入数据到新建的t2表中,创建trigger禁止插入数据如果该表行数达到12
mysql> DELIMITER $$
mysql> CREATE PROCEDURE t2insert(IN count INT)
-> BEGIN
-> DECLARE i INT DEFAULT 0;
-> WHILE i<count
-> DO
-> insert into t2 values(i,concat('yoo',i),CURRENT_TIMESTAMP());
-> SET i=i+1;
-> END WHILE ;
-> END$$
Query OK, 0 rows affected (0.06 sec)
mysql> create trigger tr701 before insert on t2 for each row
-> begin
-> declare msg varchar(50);
-> set msg="row is over 11,cannot insert again";
-> set @num=(select count(* ) from t2);
-> if @num >11 then
-> signal sqlstate 'HY000' set message_text=msg;
-> end if;
-> end$$
Query OK, 0 rows affected (0.11 sec)
4.查看存储过程和trigger的信息:
DELIMITER ;
show procedure status like '%insert%' \G;
show create procedure t2insert \G;
select * from information_schema.routines where routine_name = 't2insert' \G;
DELIMITER ;
show procedure status like '%insert%' \G;
show create procedure t2insert \G;
select * from information_schema.routines where routine_name = 't2insert' \G;
show triggers tr701\G
select * from information_schema.triggers where trigger_name like '%tr%'\G
select * from information_schema.triggers where trigger_name like '%tr%'\G
mysql> show procedure status like '%insert%' \G;
*************************** 1. row ***************************
Db: test3
Name: t2insert
Type: PROCEDURE
Definer: root@localhost
Modified: 2018-06-10 22:13:09
Created: 2018-06-10 22:13:09
Security_type: DEFINER
Comment:
character_set_client: gbk
collation_connection: gbk_chinese_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)
5.调用存储过程:
mysql> call t2insert(10);
Query OK, 1 row affected (0.65 sec)
mysql> select * from t2;
+------+------+---------------------+
| id | name | dtime |
+------+------+---------------------+
| 0 | yoo0 | 2018-06-10 22:18:25 |
| 1 | yoo1 | 2018-06-10 22:18:26 |
| 2 | yoo2 | 2018-06-10 22:18:26 |
| 3 | yoo3 | 2018-06-10 22:18:26 |
| 4 | yoo4 | 2018-06-10 22:18:26 |
| 5 | yoo5 | 2018-06-10 22:18:26 |
| 6 | yoo6 | 2018-06-10 22:18:26 |
| 7 | yoo7 | 2018-06-10 22:18:26 |
| 8 | yoo8 | 2018-06-10 22:18:26 |
| 9 | yoo9 | 2018-06-10 22:18:26 |
+------+------+---------------------+
10 rows in set (0.00 sec)
6.测试触发器
mysql> insert into t2 values(10,'foo',CURRENT_TIMESTAMP());
Query OK, 1 row affected (0.06 sec)
mysql> insert into t2 values(11,'foo2',CURRENT_TIMESTAMP());
Query OK, 1 row affected (0.11 sec)
----当插入条目达到12,就拒绝插入并抛出错误信息:
mysql> insert into t2 values(12,'foo3',CURRENT_TIMESTAMP());
ERROR 1644 (HY000): row is over 11,cannot insert again
7.
可以使用alter修改存储过程的简单特性但并不能修改过程体,所以要修改过程体的SQL语句只能
drop删除后重新创建
alter procedure
drop procedure t2insert;
drop trigger i701;