MySQL必知必会学习笔记第二十五章使用触发器

小咪咪 2022-12-30 08:24 153阅读 0赞

触发器在MySQL 5中增加。

触发器可以在MySQL响应DELETE、INSERT、UPDATE语句时自动执行一条SQL语句。

MySQL 5中触发器名在每个表中唯一而不是在一个数据库中唯一。其他DBMS有的重名限制是数据库范围，以后MySQL可能会使命名规则更加严格，最好在一个库中使用唯一的触发器名。

创建触发器：

CREATE TRIGGER triggerName AFTER INSERT ON tableName
    FOR EACH ROW SELECT 'added';

触发器可以在一个操作执行前或后执行，上例在插入操作成功后执行，插入每一行后，显示一次added。但我在MySQL 5.6.24中测试时已经不允许触发器返回值了：  
![在这里插入图片描述][6d55a790121045fe8741b01598fe8271.png]

只有表支持触发器，视图和临时表不支持。

每个表每个事件只允许一个触发器，因此，一个表最多支持六个触发器（每条增删改前后）。单个触发器不能与多个事件或多个表关联。

如果BEFORE触发器失败，则MySQL不会执行请求的操作。如果BEFORE触发器或请求语句执行失败，MySQL不执行AFTER触发器。

删除触发器：

DROP TRIGGER triggerName;

触发器不能被更新或覆盖，为修改一个触发器，必须先删除它。

INSERT触发器要点：  
1.INSERT触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行。  
2.BEFORE INSERT触发器中，NEW中的值也能被更新（允许更改被插入的值）。  
3.对于AUTO\_INCREMENT列，NEW在INSERT执行前包含0，在INSERT执行后包含新的自增值。

查看本次插入的订单的自动增长的订单号值：

CREATE TRIGGER triggerName AFTER INSERT ON orders
    FOR EACH ROW SELECT NEW.order_num;

在我进行测试的MySQL版本中，以上触发器也因返回了值而创建失败。

DELETE触发器要点：  
1.在DELETE触发器代码内，可以引用名为OLD的虚拟表，访问被删除的行。  
2.OLD中的值全都是只读的，不能更新。

将要被删除的行保存到存档表中：

CREATE TRIGGER triggerName BEFORE DELETE ON orders
    FOR EACH ROW
    BEGIN
        INSERT INTO archive_orders(order_num, order_date, cust_id)
        VALUES(OLD.order_num, OLD.order_date, OLD.cust_id);
    END;

以上使用BEFORE DELETE的优点是如果存档失败，DELETE操作也会被放弃。

上例触发器使用了BEGIN和END，使用BEGIN和END语句块的好处是能容纳多条SQL语句，不用BEGIN和END语句块时只能有一句SQL。上例中其实不需要这样，但也没坏处。

UPDATE触发器要点：  
1.在UPDATE触发器代码中，可以用名为OLD的虚拟表访问UPDATE之前的值，同时可以用名为NEW的虚拟表访问UPDATE之后的值。  
2.在BEFORE UPDATE触发器中，NEW中的值能被更新，即允许更改将要用于UPDATE语句中的值。  
3.OLD中的值是只读的，不能更新。

在插入值前将其大写：

CREATE TRIGGER triggerName BEFORE UPDATE ON tableName
    FOR EACH ROW SET NEW.field = Upper(NEW.field);

创建触发器需要特殊的安全访问权限，但执行是自动的。

应该用触发器保证数据一致性（大小写、格式等），它总能执行这种处理，且是透明地进行，与客户机应用无关。

触发器可创建审计跟踪，使用触发器，可把更改记录到另一个表中。

MySQL触发器中不能调用存储过程。

[6d55a790121045fe8741b01598fe8271.png]: /images/20221120/8a182e84ce674bf7a5e55f62c23821a8.png