SQL Server触发器在UPDATE和INSERT上保存行修订

Question

我有以下SQL Server触发器，我想做以下事情:

• 在INSERT上，将Documents中的新行保存到ArchivedDocuments
• 在UPDATE上，保存新行修订如果WordCount或PageCount或ContributorCount已经改变了UPDATE
的当前值

我如何实现这一点?

我当前的触发器只是在INSERT或UPDATE之后保存到ArchivedDocument。

编辑:Id是文档表的主键。DocumentId是引用Document.Id的外键。

CREATE TRIGGER [TRG__Documents] 
ON [Documents]
AFTER INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @now datetimeoffset = sysdatetimeoffset();
INSERT INTO [DocumentRevisions] ([DocumentId], [Time], [WordCount], [PageCount], [ContributorCount])
SELECT
[inserted].[Id],
@now,
[inserted].[WordCount],
[inserted].[PageCount],
[inserted].[ContributorCount]
FROM  
[inserted]
END

Answer 1

您需要在deleted表中EXCEPT任何匹配的行。我假设Id是主键，如果不是，那么您需要修改它以包含主键。

create trigger [TRG__Documents] on [Documents]
after insert, update
as
begin
set nocount on;
if(not exists (select 1 from inserted))
return;
declare @now datetimeoffset = sysdatetimeoffset();
insert into [dbo].[DocumentRevisions] (
[DocumentId],
[Time],
[WordCount],
[PageCount],
[ContributorCount]
)
select
i.[Id],
@now,
i.[WordCount],
i.[PageCount],
i.[ContributorCount]
from (
select
i.[Id],
i.[WordCount],
i.[PageCount],
i.[ContributorCount]
from inserted i
except
select
d.[Id],
d.[WordCount],
d.[PageCount],
d.[ContributorCount]
from deleted d
) i
end