【mysql】-创建存储和触发器

显示test1表数据

mysql> select * from test1;
Empty set (0.00 sec)

创建存储

mysql>  \d//
mysql> create procedure p2()
    -> begin
    -> set @i=3;
    -> while @i<=100 do
    -> insert into test1(name) values(concat("user",@i));
    -> set @i=@i+1;
    -> end while;
    -> end//
Query OK, 0 rows affected (0.10 sec)

显示存储状态

mysql> show procedure status \G
*************************** 1. row *********************
                  Db: t_user
                Name: p2
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2017-03-24 20:21:33
             Created: 2017-03-24 20:21:33
       Security_type: DEFINER
             Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
*************************** 2. row *********************
                  Db: user
                Name: p2
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2017-03-29 20:51:34
             Created: 2017-03-29 20:51:34
       Security_type: DEFINER
             Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
2 rows in set (0.01 sec)

唤醒存储

mysql> call p2;
Query OK, 0 rows affected (0.01 sec)

显示test1执行存储的结果

mysql> select * from test1;
+----+---------+
| id | name    |
+----+---------+
|  1 | user3   |
|  2 | user4   |
|  3 | user5   |
|  4 | user6   |
|  5 | user7   |
|  6 | user8   |
|  7 | user9   |
|  8 | user10  |
|  9 | user11  |
| 10 | user12  |
| 11 | user13  |
| 12 | user14  |
| 13 | user15  |
| 14 | user16  |
| 15 | user17  |
| 16 | user18  |
| 17 | user19  |
| 18 | user20  |

·····································································································

插入触发器

mysql> \d//
mysql> create trigger test1 before insert on test1 for each row
    -> begin
    -> insert into test2(name) values(new,name);
    -> end//
Query OK, 0 rows affected (0.19 sec)

显示触发器

mysql> show triggers\G
*************************** 1. row ***************************
             Trigger: test1
               Event: INSERT
               Table: test1
           Statement: begin
insert into test2(name) values(new,name);
end
              Timing: BEFORE
             Created: NULL
            sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.01 sec)

更新触发器

mysql> create trigger test3 before update on test1 for  each row
    -> begin
    -> update test3 set name=new.name where name=old.name;
    -> end//
Query OK, 0 rows affected (0.11 sec)

删除触发器

mysql> create trigger test2 before delete on test1 for each row
    -> begin
    -> delete from test2 where id=old.id;
    -> end//
Query OK, 0 rows affected (0.17 sec)