How to use CHECKSUM in a trigger

I’m trying to create my own logic for tables synchronization in SQL Server Express 2019. I was hoping that such simple task would work:

1. Have a Customers table

2. Have a Synchronization table

    CREATE TABLE [dbo].[Synchronization]
    (
        [PK] [uniqueidentifier] NOT NULL,
        [TableName] [nchar](50) NOT NULL,
        [RecordPK] [uniqueidentifier] NOT NULL,
        [RecordChecksum] [int] NOT NULL,
        [RecordDate] [datetime] NOT NULL,
        [RecordIsDeleted] [bit] NOT NULL
    )
   

3. Have a trigger on Customers:

    CREATE TRIGGER trgCustomers_INSERT 
    ON Customers 
    AFTER INSERT
    AS
        INSERT INTO Synchronization(PK, TableName, RecordPK, RecordChecksum,
                                    RecordDate, RecordIsDeleted)
        VALUES (NEWID(), 'Customers',
                (SELECT PK FROM inserted),
                (SELECT CHECKSUM(*) FROM inserted),
                GETDATE(), 0)
   

… but I got an error about the SELECT CHECKSUM(*) FROM inserted part:

Cannot use CHECKSUM(*) in a computed column, constraint, default definition, or INSERT statement.

Is there any other way to add new Customer‘s CHECKSUM or some hash to the Synchronization table?

Answer

Don’t use the VALUES syntax when inserting and you won’t get an error using CHECKSUM while inserting.

Example:

declare @t table (val int)

-- works
insert into @t select checksum(*) from ( select ID from (select 1 as ID union select 2) b ) a

-- reproduce error
insert into @t
values
((select top 1 checksum(*) C from ( select ID from (select 1 as ID union select 2) b ) a))

Implementing the concept in your trigger:

 CREATE TRIGGER trgCustomers_INSERT 
 ON Customers 
 AFTER INSERT
 AS
 begin
     INSERT INTO Synchronization(PK, TableName, RecordPK, RecordChecksum,
                                 RecordDate, RecordIsDeleted)

     select NEWID() as PK, 
     'Customers' as TableName,
             PK as RecordPK,
             checksum(*) as RecordChecksum,
             GETDATE() as RecordDate, 
             0 as RecordIsDeleted
     from inserted
     
 end