MySQL的触发器和存储过程一样,都是嵌入到MySQL的一段程序。触发器是由时间来触发某个操作,这些时间包括INSERT、UODATE和DELETE语句。如果定义了触发程序,当数据库执行这些语句的时候就会触发执行相应的操作,触发程序是与表有关的命名数据库对象,当表上出现特定事件时,当激活该对象。
11.1、创建触发器
触发器是一个特殊的存储过程,不同的是,执行存储过程要使用CALL语句来调用,而触发器的执行不需要使用CALL语句来调用,也不需要手工启动,只要当预定义的时间发生的时候,就会被MySQL自动调用。
创建一个触发器的语法为:
CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_body
trigger_name:触发器名称,用户自行指定 trigger_time:触发时机,可以指定为before或after trigger_event:标识触发事件 tbl_name:标识触发器的表名 trigger_body:触发器执行语句 |
创建一个单执行语句的触发器:
mysql> CREATE TABLE account (acc_num INT,amount DECIMAL(10,2)); Query OK, 0 rows affected (0.06 sec) mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account -> FOR EACH ROW SET @sum = @sum +NEW.amount; Query OK, 0 rows affected (0.02 sec) mysql> SET @sum=0; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO account VALUES (1,1.00),(2,2.00); Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT @sum; +------+ | @sum | +------+ | 3.00 | +------+ 1 row in set (0.00 sec)
创建多个执行语句的触发器的语法:
CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW BEGIN trigger_stmt END
11.2、查看触发器
查看触发器是指查看数据库中已存在的触发器的定义、状态和触发信息等可以通过SHOW TRUGGERS和在triggers表中查看触发器信息。
mysql> CREATE TABLE myevent -> ( -> id int(11) DEFAULT NULL, -> evt_name char(20) DEFAULT NULL -> ); Query OK, 0 rows affected (0.03 sec) mysql> CREATE TRIGGER trig_update AFTER UPDATE ON account -> FOR EACH ROW INSERT INTO myevent VALUES (1,'AFTER UPDATE'); Query OK, 0 rows affected (0.05 sec) mysql> SHOW TRIGGERS \G *************************** 1. row *************************** Trigger: ins_sum Event: INSERT Table: account Statement: SET @sum = @sum +NEW.amount Timing: BEFORE Created: NULL sql_mode: Definer: root@localhost character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: utf8_general_ci *************************** 2. row *************************** Trigger: trig_update Event: UPDATE Table: account Statement: INSERT INTO myevent VALUES (1,'AFTER UPDATE') Timing: AFTER Created: NULL sql_mode: Definer: root@localhost character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: utf8_general_ci 2 rows in set (0.00 sec)
在MySQL中所有触发器的定义都存在INFORMATION_SCHEMA数据库的TRIGGERS表中,可以通过SELECT 来查看,其语法格式为:
SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE condition;
mysql> SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='trig_update' \G *************************** 1. row *************************** TRIGGER_CATALOG: def TRIGGER_SCHEMA: test TRIGGER_NAME: trig_update EVENT_MANIPULATION: UPDATE EVENT_OBJECT_CATALOG: def EVENT_OBJECT_SCHEMA: test EVENT_OBJECT_TABLE: account ACTION_ORDER: 0 ACTION_CONDITION: NULL ACTION_STATEMENT: INSERT INTO myevent VALUES (1,'AFTER UPDATE') 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: DEFINER: root@localhost CHARACTER_SET_CLIENT: utf8 COLLATION_CONNECTION: utf8_general_ci DATABASE_COLLATION: utf8_general_ci 1 row in set (0.00 sec)
11.3、触发器的使用
触发程序是与表有关的命名数据库对象,当表上出现特定时间时,将激活该对象。
创建一个在account表插入数据之后,更新myevent数据表的触发器。
mysql> CREATE TRIGGER trig_inster AFTER INSERT ON account -> FOR EACH ROW INSERT INTO myevent VALUES (2,'AFTER INSERT'); Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO account VALUES (1,1.00),(2,2.00); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM myevent; +------+--------------+ | id | evt_name | +------+--------------+ | 2 | AFTER INSERT | | 2 | AFTER INSERT | +------+--------------+ 2 rows in set (0.00 sec)
11.4、删除触发器
使用DROP TRIGGER语句可以删除MySQL中已经存在的触发器,其语法格式为:
DROP TRIGGER [schema_name.]trigger_name
删除一个触发器
mysql> DROP TRIGGER test.ins_sum; Query OK, 0 rows affected (0.02 sec)
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。