我们有一个表用作应用程序的审计跟踪表。我们称之为它ProductAudit
,我们称之为这个审计表的数据表Products
。ProductsAudit
由于一个错误,大约有 1.5 亿行被污染。大约有 200 万个有效行需要保留。
所以为了清理,我想:
- 创建一个新表,
ProductAudit_TEMP
- 复制超过 200 万行
- 删除表
ProductAudit
- 重命名
ProductAudit_TEMP
为ProductAudit
.
这将比删除 1.5 亿条无效行快得多——即使是批量删除也是如此。
我的问题是, tableProducts
有 INSERT、UPDATE 和 DELETE 触发器,它们写入 table ProductAudit
。我的计划会破坏触发器吗?我将让所有用户退出系统,甚至在执行清理时禁用触发器以确保安全。
编辑:
只是为了澄清,ProductAudit
没有触发器,Products
有。ProductAudit
由属于 table 的 INSERT、UPDATE 和 DELETE 触发器填充Products
。
正如@AaronBertrand 在他的评论中指出的那样,对象通过object_id附加到其他对象,而不是通过 object name。话虽如此,有一种方法可以将数据从一个表移动到另一个表,而无需使用
sp_rename
.由于示例代码是展示某项工作原理的最简洁方式,因此我创建了以下测试台代码来说明如何使用
ALTER TABLE ... SWITCH
语法来启用数据迁移。首先,我们将创建一个“现有”表
Product
,ProductAudit
在表上使用触发器Product
,将行插入ProductAudit
表中:在这里,我们将在表中插入 100,000 行
Product
:SELECT
上述查询的结果:这将创建一个“临时”表来保存我们想要保留的行:
此代码将现有表中的数据“迁移”到新表中,保留原始表中存在的触发器,而不重新创建它们。该
ALTER TABLE ... SWITCH
语法是一种非常快速的纯元数据操作,使其成为迁移数据的好方法。下面的代码设置事务隔离级别以锁定涉及的表,以防止其他进程在任何涉及的表中插入或更新数据。结果:
在这里,我们将在表中再插入 10 行
dbo.Product
来证明触发器仍然有效:结果:
如果触发器在表 Products 上,并且正在写入表 ProductAudit,那么您应该没问题,只要在触发器运行时存在名为 ProductAudit 的表。
诀窍是确保在您认为您已经将所有好的行从 ProductAudit 移动到 ProductAudit_TEMP 之后,触发器不会写入审计行。确保不会发生这种情况的简单方法是将数据库设置为类似 RESTRICTED_USER 模式并确保没有其他人可以连接。如果您需要在不完全关闭应用程序的情况下执行此操作,那么您可能需要在涉及一些 TABLOCK、XLOCK、HOLDLOCK 提示的事务中完成整个操作,或者临时修改触发器以直接写入 ProductAudit_TEMP。