MySql之触发器【过度变量newold】
trigger是由事件触发某个操作。这些事件包括insert语句、update语句和delete语句。当数据库执⾏这些事件时，就会激活触发器执⾏相应的操作。
【1】只有⼀个执⾏语句
create trigger trig_bookSum after insert
on t_book for each row
update t_booktype set bookSum=bookSum+1 where new.bookTypeId=t_booktype.id;
当插⼊时，图书类别的书的数量加1
【2】多个执⾏语句
delimiter &&
...body
&&
delimiter ;
建⽴⼀张t_log表⽤来保存⽇志（可视化创建），
delimiter &&
create trigger trig2 after delete
on t_book for each row
begin
update t_bookType set bookSum=bookSum-1 where old.bookTypeId=t_bookType.id;
insert into t_log values(NULL,now(),'book⾥删除了⼀条信息');
end
&&
delimiter ;
delete from t_book where id=10；之后，查看bookSum和t_log⽇志
触发器使⽤详解
update语法大全创建触发器语法
CREATE TRIGGER trigger_name
trigger_time
trigger_event ON tbl_name
FOR EACH ROW
trigger_stmt
其中：
trigger_name：标识触发器名称，⽤户⾃⾏指定；
trigger_time：标识触发时机，取值为 BEFORE 或 AFTER；
trigger_event：标识触发事件，取值为 INSERT、UPDATE 或 DELETE；
tbl_name：标识建⽴触发器的表名，即在哪张表上建⽴触发器；
trigger_stmt：触发器程序体，可以是⼀句SQL语句，或者⽤ BEGIN 和 END 包含的多条语句。
由此可见，可以建⽴6种触发器，即：BEFORE INSERT、BEFORE UPDATE、BEFORE DELETE、AFTER INSERT、AFTER UPDATE、AFTER DELETE。
另外有⼀个限制是不能同时在⼀个表上建⽴2个相同类型的触发器，因此在⼀个表上最多建⽴6个触发器。
trigger_event 详解
MySQL 除了对 INSERT、UPDATE、DELETE 基本操作进⾏定义外，还定义了 LOAD DATA 和 REPLACE 语句，这两种语句也能引起上述6中类型的触发器的触发。
LOAD DATA 语句⽤于将⼀个⽂件装⼊到⼀个数据表中，相当与⼀系列的 INSERT 操作。
REPLACE 语句⼀般来说和 INSERT 语句很像，只是在表中有 primary key 或 unique 索引时，如果插⼊的数据和原来 primary key 或 unique 索引⼀致时，会先删除原来的数据，然后增加⼀条新数据，也就是说，⼀条 REPLACE 语句有时候等价于⼀条。
INSERT 语句，有时候等价于⼀条 DELETE 语句加上⼀条 INSERT 语句。
INSERT 型触发器：插⼊某⼀⾏时激活触发器，可能通过 INSERT、LOAD DATA、REPLACE 语句触发；
UPDATE 型触发器：更改某⼀⾏时激活触发器，可能通过 UPDATE 语句触发；
DELETE 型触发器：删除某⼀⾏时激活触发器，可能通过 DELETE、REPLACE 语句触发。
BEGIN … END 详解
在MySQL中，BEGIN … END 语句的语法为：
BEGIN
[statement_list]
END
其中，statement_list 代表⼀个或多个语句的列表，列表内的每条语句都必须⽤分号（;）来结尾。
⽽在MySQL中，分号是语句结束的标识符，遇到分号表⽰该段语句已经结束，MySQL可以开始执⾏了。因此，解释器遇到statement_list 中的分号后就开始执⾏，然后会报出错误，因为没有到和 BEGIN 匹配的 END。
这时就会⽤到 DELIMITER 命令（DELIMITER 是定界符，分隔符的意思），它是⼀条命令，不需要语句结束标识，语法为：
DELIMITER new_delemiter
new_delemiter 可以设为1个或多个长度的符号，默认的是分号（;），我们可以把它修改为其他符号，如$：
DELIMITER $
在这之后的语句，以分号结束，解释器不会有什么反应，只有遇到了$，才认为是语句结束。注意，使⽤完之后，我们还应该记得把它给修改回来。
触发器是⼀个特殊的存储过程，不同的是存储过程要⽤CALL来调⽤，⽽触发器不需要使⽤CALL
也不需要⼿⼯启动，只要当⼀个预定义的事件发⽣的时候，就会被MYSQL⾃动调⽤。
创建触发器
语法如下：
CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt
触发程序是与表有关的命名数据库对象，当表上出现特定事件时，将激活该对象。
触发程序与命名为tbl_name的表相关。tbl_name必须引⽤永久性表。不能将触发程序与临时表表或视图关联起来。
trigger_time是触发程序的动作时间。它可以是BEFORE或AFTER，以指明触发程序是在激活它的语句之前或之后触发。
trigger_event指明了激活触发程序的语句的类型。trigger_event可以是下述值之⼀：
· INSERT：将新⾏插⼊表时激活触发程序，例如，通过INSERT、LOAD DATA和REPLACE语句。
·
UPDATE：更改某⼀⾏时激活触发程序，例如，通过UPDATE语句。
· DELETE：从表中删除某⼀⾏时激活触发程序，例如，通过DELETE和REPLACE语句。
请注意，trigger_event与以表操作⽅式激活触发程序的SQL语句并不很类似，这点很重要。
例如，关于INSERT的BEFORE触发程序不仅能被INSERT语句激活，也能被LOAD DATA语句激活。
可能会造成混淆的例⼦之⼀是INSERT INTO .. ON DUPLICATE UPDATE ...语法：BEFORE INSERT触发程序对于每⼀⾏将激活，后跟AFTER INSERT触发程序，或BEFORE UPDATE和AFTER UPDATE触发程序，具体情况取决于⾏上是否有重复键。
对于具有相同触发程序动作时间和事件的给定表，不能有两个触发程序。
例如，对于某⼀表，不能有两个BEFORE UPDATE触发程序。
但可以有1个BEFORE UPDATE触发程序和1个BEFORE INSERT触发程序，或1个BEFORE UPDATE触发程序和1个AFTER UPDATE触发程序。
trigger_stmt是当触发程序激活时执⾏的语句。
如果你打算执⾏多个语句，可使⽤BEGIN ... END复合语句结构。这样，就能使⽤存储⼦程序中允许的相同语句。
创建⼀个单执⾏语句的触发器
CREATE TABLE account(acct_num INT ,amount DECIMAL(10,2));
CREATE TRIGGER ins_sum BEFORE INSERT ON account
FOR EACH ROW SET @SUM=@SUM+new.amount;
⾸先创建⼀个account表，表中有两个字段，分别为：acct_num字段（定义为int类型）
amount字段（定义成浮点类型）；其次创建⼀个名为ins_sum的触发器，触发的条件是向数据表account插⼊数据之前，
对新插⼊的amount字段值进⾏求和计算
DECLARE @num INT
SET @num=0
INSERT INTO account VALUES(1,1.00),(2,2.00)
SELECT @num
⾸先创建⼀个account表，在向表account插⼊数据之前，计算所有新插⼊的account表的amount值之和，
触发器的名称为ins_num，条件是在向表插⼊数据之前触发。
创建有多个执⾏语句的触发器，语法如下：
DELIMITER |
CREATE TRIGGER testref BEFORE INSERT ON test1
FOR EACH ROW BEGIN
INSERT INTO test2 SET a2 = NEW.a1;
DELETE FROM test3 WHERE a3 = NEW.a1;
UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
END
|
查看触发器
查看触发器是指数据库中已存在的触发器的定义、状态、语法信息等。
可以使⽤SHOW TRIGGERS 和在TRIGGERS 表中查看触发器信息
SHOW TRIGGERS
Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
------- ------ ------- ------------------------ ------ ------- -------- -------------- -------------------- -------------------- ------------------
ins_sum INSERT account set @sum=@sum+new.amount BEFORE (NULL) root@localhost utf8 utf8_general_ci utf8_general_ci
EVENT表⽰激活触发器的事件，这⾥的触发事件为插⼊操作INSERT，TABLE表⽰激活触发器的对象表，这⾥为account 表
Timing表⽰触发器的时间，为插⼊之前（BEFORE）；Statement 表⽰触发器执⾏的操作，还有⼀些其他信息，⽐如SQL模式，触发器的定义帐户和字符集等
在TRIGGERS 表中查看触发器信息
information_schema数据库的TRIGGERS 表中，可以通过查询查看触发器信息
SELECT * FROM `information_schema`.`TRIGGERS` WHERE `TRIGGER_NAME`='ins_sum'
TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEME --------------- -------------- ------------ ------------------ -------------------- ------------------- ------------------ ------------ ---------------- ------------------------ ------------------ ------------- -------------------------- -------------------------- ------------------------ ------------------------ ------- -------- ----def school ins_sum INSERT def school account 0 (NULL) set @sum=@sum+new.amount ROW BEFORE (NULL) (NULL) O
TRIGGER_SCHEMA 表⽰触发器所在的数据库
TRIGGER_NAME表⽰触发器的名称
EVENT_OBJECT_TABLE表⽰在哪个表上触发
ACTION_STATEMENT 表⽰触发器触发的时候执⾏的具体操作
ACTION_ORIENTATION是ROW，表⽰在每条记录上都触发
ACTION_TIMING表⽰触发的时刻是BEFORE
删除触发器
使⽤DROP TRIGGER 语句可以删除MYSQL中已经定义的触发器，删除触发器的基本语法
DROP TRIGGER [schema_name.]trigger_name
其中（schema_name）是可选的
如果省略了schema（⽅案），将从当前⽅案中舍弃触发程序。
删除ins_sum触发器
DROP TRIGGER `school`.`ins_sum`
触发器ins_sum删除成功
总结
对于相同的表，相同的事件只能创建⼀个触发器，⽐如对表account创建了BEFORE INSERT触发器
那么如果对表account再次创建⼀个BEFORE INSERT触发器，MYSQL就会报错，此时，只可以在表account上
创建AFTER INSERT或者BEFORE UPDATE类型的触发器
发表评论