1.触发器
1.1 编写触发器
1.2.查看触发器
2.存储过程
2.1 编写存储过程
2.2 查看存储过程
3.在触发器中调用存储过程
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 ;