mosh数据库——第十章

Xiao_die888

已于 2024-06-03 12:55:55 修改

阅读量1.3k

点赞数 19

分类专栏： mosh数据库文章标签：数据库 sql

于 2024-06-03 12:55:25 首次发布

本文链接：https://blog.csdn.net/xiao_die888/article/details/139399537

版权

mosh数据库专栏收录该内容

9 篇文章 1 订阅

订阅专栏

1.触发器

触发器是在插入、更新和删除语句前后自动执行的一堆SQL代码，通常我们使用触发器增强数据一致性

所以首先我们需要修改默认分隔符

DELIMITER $$

CREATE TRIGGER payments_after_insert

意思是这个触发器关联到付款表,并且会在我们插入一条记录以后点燃

命名方法：先是明确表名，接着输入”之后”或“之前”表示触发器在SQL语句之前还是之后触发，最后写上触发触发器的SQL语句类型（这就是个名字不会产生实质影响）

所以我们还要明确告诉MySQL我们想让这个触发器在付款表上的插入语句之后触发

DELIMITER $$

CREATE TRIGGER payments_after_insert
	AFTER INSERT ON payments
	FOR EACH ROW
BEGIN
	UPDATE invoices
	SET payment_total = payment_total + NEW.amount
	WHERE invoice_id = NEW.invoice_id;
END $$
DELIMITER ;

AFTER INSERT ON payments-- 也可以用更新或删除，具体看我们想要什么（这里使用插入）FOR EACH ROW-- 意思是这个触发器会作用于每一个受影响的行(如果我们插入5行每一行都会触发)

有些DBMS还支持表级别的触发器只需要触发一次就可以了(所以如果你插入1行或者5行触发器也只会发动一次), my sql 还不支持这个功能

在测试新触发器前有一件事，这个触发器中我们可以修改任何表中的数据 , 除了这个触发器所在表，否则就会变成无尽轮回，因为这个触发器能自燃，所以我们可以修改除了这张表的任意表中的数据

使用触发器：

INSERT INTO payments
VALUES (DEFAULT, 5, 3, '2019-01-01', 10, 1)

执行并刷新结果，现在我们可以看到付款总计而这张发票自动更新了，因为使用了触发器

2.查看触发器

我们可以用SHOW TRIGGERS语句来查看当前数据库的所有触发器

这里我们可以筛选显示的结果，如果你只想查看有关payments表的触发器，可以用LIKE操作符,像这样写：

SHOW TRIGGERS LIKE 'payments%'-- 之后就会返回名称中是payments开头这样模式的触发器

之后就会返回名称中是payments开头这样模式的触发器

如果按照命名规则进行命名，那么很好查找筛选（表名_before/after _insert/update/delete）

首先是表的名字,然后写之前还是之后，最后是SQL语句的的类型,即插入,修改还是删除

3.删除触发器

删除触发器很简单，方法和删除存储过程很像，我们用DROP TRIGGER语句，可选并且我建议你们加上IF EXISTS关键字，之后输入触发器的名字,payments_after_insert

DROP TRIGGER IF EXISTS payments_after_insert

最好的做法应该是：我们应该把删除与创建语句放到同一个脚本文件，并录入一个源代码库中，这里的话,不仅团队中的每个人都可以创建同样的数据库，我们还可以看到历史数据库修改记录

DELIMITER $$

DROP TRIGGER IF EXISTS payments_after_insert; -- 先删除已有的触发器

CREATE TRIGGER payments_after_insert
	AFTER INSERT ON payments-- 也可以用更新或删除，具体看我们想要什么（这里使用插入）
	FOR EACH ROW-- 意思是这个触发器会作用于每一个受影响的行(如果我们插入5行每一行都会触发)
BEGIN-- 输入BEGIN和END表明这个触发器的主体部分
	UPDATE invoices -- 触发器的主体我们可以写任意SQL代码修改数据来增强一致性，我们可以直接写SQL代码或者调用存储过程
	SET payment_total = payment_total + NEW.amount -- NEW关键字,它会返回我们刚刚插入的行,
							-- 通过添加一个点，我们就可以获取单独属性
							-- OLD关键字：payments_after_insert这在更新或者删除行的时候很有用，OLD关键字会返回更新前的行以及对应数值
	WHERE invoice_id = NEW.invoice_id;
END $$ -- 输入BEGIN和END表明这个触发器的主体部分
-- 这里还需要输入新的分隔符，再改回分号
DELIMITER ;

4.使用触发器进行审计

触发器的另一个常见用途是为了之后审计的目的而记录对数据库的修改

当一个人增加或删除了某条记录的时候，我们可以把这个操作记录下来，这样以后就回过头知道，是谁在什么时候做了什么操作

建立审计表

USE sql_invoicing;

CREATE TABLE payments_audit
(
client_id   INT            NOT NULL,
date        DATE           NOT NULL,
amount      DECIMAL(9, 2)  NOT NULL,
action_type VARCHAR (50)   NOT NULL,
action_date DATETIME       NOT NULL
)

更新invoices表之后,我们在审计表中插入一条记录

DELIMITER $$

DROP TRIGGER IF EXISTS payments_after_insert; 

CREATE TRIGGER payments_after_insert
	AFTER INSERT ON payments
	FOR EACH ROW
BEGIN
	UPDATE invoices 
	SET payment_total = payment_total + NEW.amount 
	WHERE invoice_id = NEW.invoice_id;
    
    INSERT INTO payments_audit-- 新添
	VALUES (NEW. client_id, NEW.date, NEW.amount, 'Insert', NOW());
END $$ 
DELIMITER ;

DELIMITER $$

DROP TRIGGER IF EXISTS payments_after_delete; 

CREATE TRIGGER payments_after_delete
	AFTER delete ON payments
	FOR EACH ROW
BEGIN
	UPDATE invoices 
	SET payment_total = payment_total -old.amount 
	WHERE invoice_id = old.invoice_id;
    
    INSERT INTO payments_audit-- 新添
	VALUES (OLD. client_id, OLD.date, OLD.amount, 'Delete', NOW());
END $$ 
DELIMITER ;

回到审计表刷新结果

强调一下,我使用这张简化了的审计表来让你更好理解—怎么用触发器去记录变更。在实际应用中,你或许需要为多张表记录变更，那样的话不建议为你数据库中的每张表分别创建审计表，太重复劳动了，你可以建立一个总架构来记录变更，你可以建立一个总架构来记录变更，不过现在还不用考虑这个问题，之后的课程会讲到怎样设计数据库。那时你就会设计一个总审计表了。

5.事件|Event

我们使用事件帮助数据库维护任务实现自动处理

事件是根据计划执行的任务或一堆SQL代码,你可以只执行一次，也可以按照某种规律执行——比如每天早上十点或者每月一次，诸如此类。

所以，通过事件，我们可以自动化数据库维护任务，比如删除已经过期数据，或把数据从一张表复制到存档表，或者汇总数据生成报告，事件非常有用。

在开始设计一个事件之前，我们首先要打开MySQL事件调度器（这是一个后台程序，它每时每刻都在是找需要执行的事件）

首先，输入SHOW VARIABLES，可以看到MySQL所有的系统变量

但我们只要找事件管理器变量，这就要用到LIKE操作符：

这是时间调度器且默认设置为开启。如果关闭的话，你可以通过SET语句把它打开

SHOW VARIABLES LIKE 'event%';
SET GLOBAL event_scheduler = OFF

DELIMITER $$-- 首先我们要修改默认分隔符

CREATE EVENT yearly_delete_stale_audit_rows -- 之后使用CREATE EVENT语句，为这个事件取个名字
ON SCHEDULE-- 事件命名之后,输入ON SCHEDULE（为事件提供一个计划）
	-- AT '2019-05-01'-- 如果你只想执行一次，就要使用AT关键字,后面跟上日期时间值
	EVERY 1 YEAR STARTS '2019-01-01' ENDS '2029-01-01'-- （可以给他一个起始时间，也可以选择加上结束时间，这两块都不是必要的。）
    -- 想定期执行，用EVERY关键字，并添加区间信息
DO BEGIN -- 之后输入DO接上BEGIN和END
	DELETE FROM payments_audit-- 具体操作
	WHERE action_date < NOW() - INTERVAL 1 YEAR;
END $$

DELIMITER ;

（以事件执行的时间间隔打头命名是一个很好的做法）根据这个命名法，我们能很容易地找到每月,每年或单次触发的事件

通过这个事件，我们可以删除所有超过一年的审计记录

6.查看、删除和更改事件

要查看当前数据库的事件，我们使用SHOW EVENTS语句

这里可以看到所有事件及它们的情况

以事件执行的时间间隔打头命名，你可以很容易地使用LIKE运算符筛选事件

按年来筛选条件：

SHOW EVENTS LIKE 'yearly%;

要删除一个事件我们使用DROP EVENT语句:

ALTER EVENT语句，用来修改事件，而不用删除再重建它（它的语法和CREATE EVENT语句完全一样）

DELIMITER $$-- 首先我们要修改默认分隔符

ALTER EVENT yearly_delete_stale_audit_rows 
ON SCHEDULE
	-- AT '2019-05-01'
	EVERY 1 YEAR STARTS '2019-01-01' ENDS '2029-01-01'
DO BEGIN
	DELETE FROM payments_audit
	WHERE action_date < NOW() - INTERVAL 1 YEAR;
END $$

DELIMITER ;

也来暂时启用或者禁用一个事件 (DISABLE)

ALTER EVENT yearly_delete_stale_audit_rows DISABLE;

又或者之后我们可以回来通过(ENABLE),修改这里再次启用事件

ALTER EVENT yearly_delete_stale_audit_rows ENABLE;