mysql：触发器和存储过程

1.触发器
1.1 编写触发器

delimiter $
create trigger tg_insertTestWhenInsertUser
after insert on tb_user
for each row
begin
insert into tb_test(id) values(1);
end$
delimiter ;

1.2.查看触发器
 

mysql> select * from information_schema.`triggers` \G;
*************************** 1. row ***************************
           TRIGGER_CATALOG: def
            TRIGGER_SCHEMA: db_jersey
              TRIGGER_NAME: tg_insertTestWhenInsertUser
        EVENT_MANIPULATION: INSERT
      EVENT_OBJECT_CATALOG: def
       EVENT_OBJECT_SCHEMA: db_jersey
        EVENT_OBJECT_TABLE: tb_user
              ACTION_ORDER: 0
          ACTION_CONDITION: NULL
          ACTION_STATEMENT: begin
call pd_insertTest();
end
        ACTION_ORIENTATION: ROW
             ACTION_TIMING: AFTER
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
  ACTION_REFERENCE_OLD_ROW: OLD
  ACTION_REFERENCE_NEW_ROW: NEW
                   CREATED: NULL
                  SQL_MODE: NO_ENGINE_SUBSTITUTION
                   DEFINER: root@localhost
      CHARACTER_SET_CLIENT: utf8
      COLLATION_CONNECTION: utf8_general_ci
        DATABASE_COLLATION: latin1_swedish_ci
1 row in set (0.00 sec)

 

2.存储过程
2.1 编写存储过程
   

delimiter $
create procedure pd_insertTest()
begin
insert into tb_test(id) values(2);
end$
delimiter ;  

2.2 查看存储过程

 mysql> show procedure status ;
+-----------+---------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db        | Name          | Type      | Definer        | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+-----------+---------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| db_jersey | pd_insertTest | PROCEDURE | root@localhost | 2016-07-31 21:53:31 | 2016-07-31 21:53:31 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
+-----------+---------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

 mysql> show create procedure pd_insertTest ;
+---------------+------------------------+------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure     | sql_mode               | Create Procedure                                                                                           | character_set_client | collation_connection | Database Collation |
+---------------+------------------------+------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| pd_insertTest | NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `pd_insertTest`()
begin
insert into tb_test(id) values(2);
end | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
+---------------+------------------------+------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

3.在触发器中调用存储过程

  delimiter $
create trigger tg_insertTestWhenInsertUser
after insert on tb_user
for each row
begin
call pd_insertTest();
end$
delimiter ;