SQL Server trigger to save row revisions on UPDATE and INSERT

Admin 1
Viewed 66

I have the following SQL Server trigger that I want to do the following:

  • On INSERT, save the new row in Documents into ArchivedDocuments
  • On UPDATE, save the new row revision if WordCount or PageCount or ContributorCount have changed from the current values with the UPDATE

How do I achieve this?

My current trigger just saves to ArchivedDocument after each INSERT or UPDATE.

EDIT: Id is the primary key for the document table. DocumentId is a foreign key that references 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
triggers sql-server sql
1 Answers

You need to EXCEPT any matching rows in the deleted table. I assume Id is the primary key, if not then you will need to modify this to include the primary key.

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
user_148689970
Related