我有2个表,表A和表A历史.
> 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'
应始终将记录触发器设置为最后触发.否则,后续触发器可能会回滚原始事务,但日志表将已更新.这是一个混乱的事态.
