如何在SQL Server历史记录表中存储历史记录

> Table-A包含当前的数据行.
>表A历史包含历史数据

我想在Table-A中包含最新的数据行,并且包含历史行的Table-A-History.

我可以想到两种方式来实现这一点：

>每当有新数据行可用时,将当前行从Table-A移动到Table-A-History,并使用最新数据更新Table-A行(通过插入到select或select into table)

要么
>每当有新数据行可用时,更新Table-A的行并将新行插入到Table-A-History中.

关于表现方法1或2更好？有没有更好的不同的方式来完成这个？

解决方法

create trigger Table-A_LogDelete on dbo.Table-A
  for delete
as
  declare @Now as DateTime = GetDate()
  set nocount on
  insert into Table-A-History
    select SUser_SName(),'delete-deleted',@Now,*
      from deleted
go
exec sp_settriggerorder @triggername = 'Table-A_LogDelete',@order = 'last',@stmttype = 'delete'
go
create trigger Table-A_LogInsert on dbo.Table-A
  for insert
as
  declare @Now as DateTime = GetDate()
  set nocount on
  insert into Table-A-History
    select SUser_SName(),'insert-inserted',*
      from inserted
go
exec sp_settriggerorder @triggername = 'Table-A_LogInsert',@stmttype = 'insert'
go
create trigger Table-A_LogUpdate on dbo.Table-A
  for update
as
  declare @Now as DateTime = GetDate()
  set nocount on
  insert into Table-A-History
    select SUser_SName(),'update-deleted',*
      from deleted
  insert into Table-A-History
    select SUser_SName(),'update-inserted',*
      from inserted
go
exec sp_settriggerorder @triggername = 'Table-A_LogUpdate',@stmttype = 'update'

应始终将记录触发器设置为最后触发.否则,后续触发器可能会回滚原始事务,但日志表将已更新.这是一个混乱的事态.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!