一、触发器简介
当需要某些操作在某些语句执行之前或之后执行就需要使用触发器。
例如每次插入数据时进行数据校对,每次删除数据后将删除内容备份到新表。
这些操作我们希望它(某些语句)在满足某些条件时自动执行,也可以称为这些条件触发了这些操作。
既然某些操作可以触发某些操作,那么关键要定义两点:
1.触发条件。
2.触发后执行的操作。
MySQL中触发器只对INSERT、DELETE、UPDATE语句有效,即执行这些语句可以触发设置的触发器。
触发器根据触发的时间可分为BEFORE、AFTER,一种是在被执行语句执行前触发,一种是在被执行语句执行后触发。
创建触发器需要定义触发器名(最好当前数据库唯一)、触发器关联的表、触发语句(INSERT|DELETE|UPDATE)、
触发时机(BEFORE|AFTER)。
CREATE TRIGGER newProduct02 AFTER INSERT ON products FOR EACH ROW SELECT 'add info' INTO @info;
-- INSERT INTO products(...) VALUES(...);
-- SELECT @info;
创建了一个名为newProduct02的触发器,该触发器在执行INSERT语句之后执行。
FOR EACH ROW代表行级触发,表示任何一条记录上的操作满足触发事件都会触发该触发器,
也就是说触发器的触发频率是针对每一行数据触发一次。
SELECT ‘add info’ INTO @info 代表触发器执行的操作,将‘add info’ 设置给变量info(用户变量采用@开头),
当向products表执行INSERT语句后,会执行触发器将值设置给@info.
通过SELECT @info;可显示变量值。
注:一个表中
二、删除触发器
DROP TRIGGER 触发器名。
三、使用触发器
3.1 INSERT触发器
INSERT触发器的触发时机可以是BEFORE、AFTER。
在INSERT触发器内可以引用一个名为NEW的虚拟表。
该虚拟表表示将要插入(BEFORE)或已经插入(AFTER)的数据。
在BEFORE中代表将要插入的数据
在AFTER中代表插入后的数据,
INSERT INTO tableName(id,name) VALUES('1', 'hcf'):可以看做:
NEW.id = '1', NEW.name = 'hcf';
BEFORE INSERT TRIGGER...(此时NEW代表将要插入的数据,如果此时对NEW.id的值进行修改,最后插入的值也会被修改)
INSERT INTO tableName(id,name) VALUES(NEW.id, NEW.name);
AFTER INSERT TRIGGER...(此时NEW.id代表插入后的数据。插入完成后不可对NEW进行修改)
下面看一个例子理解。
CREATE TRIGGER newProduct03 AFTER INSERT ON products FOR EACH ROW SELECT NEW.prod_id INTO @info;
-- INSERT INTO products(prod_id,...) VALUES(123,...);
-- SELECT @info
newProduct03触发器为执行后触发,所以NEW代表插入的数据。
NEW.prod_id代表插入后的数据123.后续通过SELECT显示出来,
即每插入一条数据都会显示该数据的prod_id。
插入前(BEFORE)可以通过SET NEW.xxx = XXX来修改值。
3.2 DELETE触发器
DELETE触发器的触发事件同样可以是BEFORE,AFTER。
DELETE中可以引用一张名为OLD的虚拟表,代表删除前或删除后的数据。
OLD表为只读无法修改,所以BEFORE、AFTER的OLD表代表数据是一样的。
DLETE FROM tableName WHERE id = 1;可看做:
OLD.id = 1, OLD.name = 'hcf' ...代表删除前表中所有列的数据。
BEFORE DELETE TRIGGER...(OLD只读,无法修改,OLD代表删除前数据)
DELETE FROM tableName WHERE id = OLD.id;
BEFORE DELETE TRIGGER...(OLD只读,无法修改,OLD代表删除前数据)
在删除触发器中编写语句,将删除的数据存放在一张表中,如果出现误操作后续可还原被删除数据。
DELIMITER // CREATE TRIGGER productsDeleteInfo AFTER DELETE ON products FOR EACH ROW BEGIN INSERT INTO `productsin`(`prod_id`, `vend_id`, `prod_name`, `prod_price`, `prod_desc`) VALUES(OLD.prod_id, OLD.vend_id, OLD.prod_name, OLD.prod_price, OLD.prod_desc); END// DELIMITER ; DELETE FROM products WHERE prod_id = 'RYL01';
当对products表执行删除操作后,会将删除的数据写入productsin表中。(productsin表要存在)
OLD代表删除前的数据,由于OLD只读不能修改,所以也代表删除后的数据。
3.3 UPDATE触发器
UPDATE触发器的触发时机同样分为两种,一种是执行前(BEFORE)、一种是执行后(AFTER)。
UPDATE触发器中提供OLD表用于访问旧数据,即执行UPDATE语句之前表中的数据。
无论是BEFORE.还是AFTER,OLD都代表更新前的数据。可以看做是原表中的数据,OLD只读不可修改。
NEW可以看做UPDATE语句中的值,例如UPDATE tableName SET id=2 WHERE id = 1;
此时NEW.id就代表2,也可以说2就是NEW.id。
UPDATE tableName SET id=2 WHERE id = 1;语句可看做:
NEW.id = 2;
UPDATE BEFORE TRIGGER...(可对NEW.id进行修改; SET NEW.id = xxx)
UPDATE tableName SET id= NEW.id WHERE id = 1;
UPDATE AFTER TRIGGER... (更新后,不可对NEW.id进行修改)
其中BEFORE TRIGGER是执行UPDATE语句之前的触发器,
如果在UPDATE BEFORE TRIGGER...中将NEW.id进行修改,
最后更新的值也会被修改。
在UPDATE AFTER TRIGGER...中NEW.id就相当于最终更新的值。
DELIMITER // CREATE TRIGGER updateSet BEFORE UPDATE ON products FOR EACH ROW BEGIN SELECT NEW.prod_id, OLD.prod_id INTO @nid, @oid; END // DELIMITER ; UPDATE products SET prod_id = 'GCMH' WHERE prod_id = '123'; SELECT @nid, @oid;
NEW表示更新后的数据,此处为‘GCMH’,old代表更新前数据‘123’;
参考资料:
《MySQL必知必会》