mysql创建存储过程和触发器测试的实例讲解

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

show triggers tr701\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；