1.8(SQL学习笔记)触发器

时间:2022-01-21 23:39:13

一、触发器简介

  当需要某些操作在某些语句执行之前或之后执行就需要使用触发器。

  例如每次插入数据时进行数据校对,每次删除数据后将删除内容备份到新表。

  这些操作我们希望它(某些语句)在满足某些条件时自动执行,也可以称为这些条件触发了这些操作。

  

  既然某些操作可以触发某些操作,那么关键要定义两点:

  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;

1.8(SQL学习笔记)触发器

 

NEW表示更新后的数据,此处为‘GCMH’,old代表更新前数据‘123’;  

  

参考资料:

《MySQL必知必会》

https://www.cnblogs.com/geaozhang/p/6819648.html