笔记226 SQLSERVER中的表触发器
笔记226 SQLSERVER中的表触发器
归档数据
方法二、使用 DELETE … OUTPUT 语句
语法:
DELETE TOP(N) FROM TableName OUTPUT Deleted.* INTO Arc_TableName WHERE Condition……
TableName 为需要归档的表名
Arc_TableName 为存储归档数据的表名
适用范围:适合实时操作较强的、较大非分区表的数据归档。
优点:其克服了方法一的两个问题,在一个语句中同时实现备份和删除,易于控制删除哪些数据、单次删除的数量。
缺点:当数据库为完整模式或者大容量日志模式时,会产生大量日志,有两种方法可以解决该问题
监控日志的变化,防止日志文件满掉。归档完成后收缩事务日志到指定大小
DBCC SHRINKFILE (N'LogFileName' , Aim_size) --Aim_size 为整数,单位是MB
归档前将归档数据所在的数据库改为简单模式,归档后再改回,然后需要重新做一个全备份
--将数据库改为简单模式
ALTER DATABASE [Archive] SET RECOVERY SIMPLE WITH NO_WAIT GO DELETE TOP(N) FROM TableName OUTPUT Deleted.* INTO Arc_TableName WHERE Condition… --归档数据后,将数据库改为完整模式 ALTER DATABASE [Archive] SET RECOVERY FULL WITH NO_WAIT GO
最佳案例 Code2:
--Code2 --创建归档表 CREATE TABLE dbo.[Arc_Calls_20240709]( [CallID] BIGINT IDENTITY(1,1) NOT NULL, [AirTime] [DATETIME] NOT NULL, [SomeInfo] [CHAR](300) NULL, ) --申明每次删除记录数 DECLARE @rowcount int = 10000; SET IDENTITY_INSERT dbo.[Arc_Calls_20240709] ON; WHILE 1=1 BEGIN DELETE TOP (@rowcount) FROM dbo.[Calls] OUTPUT DELETED.CallID, DELETED.AirTime,DELETED.SomeInfo INTO dbo.[Arc_Calls_20240709] (CallID,AirTime,SomeInfo) WHERE AirTime < '2024-07-09' IF @@ROWCOUNT<@rowcount BREAK IF @@error <> 0 THROW 911000,'ERROR Archiving data!',1; --删除@rowcount行等待1秒后再进行下一次删除 WAITFOR DELAY '00:00:01' END SET IDENTITY_INSERT dbo.[Arc_Calls_20240709] OFF; GO
注意:如果存储历史数据的表没有自增列,注释掉 Code2 中的 SET IDENTITY_INSERT dbo.[Arc_Calls_20240709] ON/OFF 语句。
1 SQLSERVER中的表触发器 2 3 触发器有两个特殊的表:插入表(instered表)和删除表(deleted表)。这两张是逻辑表也是虚表。有系统在内存中创建者两张表,不会存储在数据库中。而且两张表的都是只读的,只能读取数据而不能修改数据。这两张表的结果总是与被改触发器应用的表的结构相同。当触发器完成工作后,这两张表就会被删除。Inserted表的数据是插入或是修改后的数据,而deleted表的数据是更新前的或是删除的数据。 4 5 6 对表的操作 7
对表的操作 |
Inserted逻辑表 |
Deleted逻辑表 |
增加记录(insert) |
存放增加的记录 |
无 |
删除记录(delete) |
无 |
存放被删除的记录 |
修改记录(update) |
存放更新后的记录 |
存放更新前的记录 |