运维日记| SQL server 的DML触发器 – 作者:database

某天，一位客户寻求帮助：数据库有人篡改了某张表。美创运维人员一听，这个问题应该难度不大，赶紧回应客户：

您数据库有保留最近一次完整备份以及之后的完整日志吗？

1、有的话可以用完整备份恢复+日志备份（误操作前的LSN恢复）

2、也可以通过sys.fn_dblog()获取二进制日志，再解析出来误操作的语句进行恢复

结果客户的需求并不是该操作如何恢复，而是想确定这个篡改表的始作俑者，以及如何去记录以后数据库这种类似的情况。我很清楚的告诉客户，再没部署任何监控记录的基础上，解析日志也只能获取相关的数据记录，无法获取更过的信息（当然，不排除某种高端工具可能会获取）。

至于以后如何防患，如何记录，这个可以通过SQL server触发器或者审计功能来实现，而本文，将着重介绍DML触发器以及它如何实现客户的需求，请看下文。

01  DML触发器介绍

DML触发器是一种特殊类型的存储过程，它在指定的表中的数据发生变化时自动生效。唤醒调用触发器以响应 INSERT、UPDATE 或 DELETE 语句。

02  常见的DML触发器

2.1 创建语句

2.2 插入测试

可见，因为触发器的缘故，不满足要求的数据无法插入（这里的age>=100虽有悖常理，实则祝大家都长命百岁，哈哈）

 回到我们的主旨，这种触发器虽然能起到防患的作用，但是依旧无法达到我们预期的效果，我们不仅要防患于未然，更要能事事追踪溯源，话不多说，请看下文，也是我们这篇文章最最重要的干货！！！

03  如何用DML触发器记录操作

实现原理：

DML操作记录，简单的说，就是日志类触发器，也就是尽量全面地反映数据库表所进行的insert、update、delete操作，便于日后翻阅。

1、创建测试表：

2、创建DML日志表：

3、创建DML触发器：

4、查看当前触发器日志表：

5、用hostname\administrator执行DML操作：

执行结果为：

6、再用dsz登录执行DML操作：

执行结果为：

7、查看DML日志表：

显而易见，刚才的6次insert，2次update（一次update对应两条记录，因为分别记录了name的更改前后的值—），以及2次delete，都清楚的记录在该表内。

眼尖的网友可能要问，USERID和HOSTNAME很清楚，最后这两列有什么特殊的含义吗？

我们的触发器日志表如果仅仅记录一些基础的信息，还远远不够，我们还要将更改的数据挖掘出来，以便在需要的时候进行逆向DML。

那么，我们是不是也可以修改日志表，添加更多我们需要捕获的信息呢，答案是肯定的，不过这个就交给广大网友去探索、去更改了……

8、truncate 掉test表，再查看DML日志表：

可以看到，依旧只有12条记录，这是为什么呢？明明test的表都被删干净了啊？？?

莫慌莫慌，大家都知道，truncate和delete不同，它属于DDL语句，所以，关于DDL的触发器，后续将进行分享。

---

美创运维中心数据库服务团队拥有Oracle ACE 1人、OCM 10余人、数十名Oracle OCP、MySQL OCP、红帽RHCA、中间件weblogic、tuxedo认证、达梦工程师 ，著有《Oracle DBA实战攻略》，《Oracle数据库性能优化方法和最佳实践》，《Oracle内核技术揭秘》等多本数据运维优化书籍。目前运维各类数据库合计2000余套，精通Oracle、MySQL、SQLServer、DB2、PostgreSQL、达梦等主流商业和开源数据库。并成为首批国内达梦战略合作伙伴之一，拥有海量经验和完善的人员培养体系。并同时提供超融合，私有云整体解决方案。

来源：freebuf.com 2020-06-05 16:05:41 by: database