mysql的触发器和存储过程_mysql：触发器和存储过程

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 ;

分享到：

2016-07-31 22:46

浏览 476

分类:数据库

评论