笔记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）	存放更新后的记录	存放更新前的记录