Need to have a Trigger on Table with Encrypted Column in SQL Server that inserts a new record for each update into Archive Table

Question

I have a table similar to following schema in SQL Server 2017:

Table Sample in the main database where TaxID column is encrypted using SQL Server "Always Encrypted" feature:

CREATE TABLE [dbo].[Sample]
(
    [CreatedDt] [smalldatetime] NOT NULL,
    [LastModDt] [smalldatetime] NOT NULL,
    [CompanyID] [int] IDENTITY(1,1) NOT NULL,
    [CompanyName] [varchar](250) NOT NULL,
    [CompanyTaxName] [varchar](250) NULL,
    [TaxID] [varchar](15) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = 
    [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL,
    [Active] [bit] NOT NULL
)

Then we have another table with same schema in archive database for history purposes with TaxID encrypted.

This is the table Sample in the Main_Archive database:

CREATE TABLE [dbo].[Sample]
(
    [CreatedDt] [smalldatetime] NOT NULL,
    [LastModDt] [smalldatetime] NOT NULL,
    [CompanyArchiveID] [int] IDENTITY(1,1) NOT NULL,
    [CompanyID] [int] IDENTITY(1,1) NOT NULL,
    [CompanyName] [varchar](250) NOT NULL,
    [CompanyTaxName] [varchar](250) NULL,
    [TaxID] [varchar](15) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = 
    [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL,
    [Active] [bit] NOT NULL
)

Now, we want to have a trigger on the main Sample table that inserts a new record into the archive Sample table for every update.

The trigger for the Sample table in the main database is as follows:

CREATE TRIGGER [dbo].[tr_iud_Sample] 
ON [dbo].[Sample]
FOR INSERT, UPDATE, DELETE
AS 
BEGIN
    SET NOCOUNT ON
    DECLARE @CurrDt AS SMALLDATETIME
    SELECT @CurrDt = GETDATE()

    DECLARE @CurrYear AS INT
    SELECT @CurrYear = YEAR(@CurrDt)

    UPDATE Sample
    SET LastModDt = @CurrDt,
        CreatedDt = CASE WHEN d.CompanyID IS NULL THEN @CurrDt ELSE Sample.CreatedDt END
    FROM inserted i WITH (NOLOCK)
    LEFT JOIN deleted d WITH (NOLOCK) ON d.CompanyID= i.CompanyID
    WHERE Sample.CompanyID = i.CompanyID 

    INSERT INTO [Main_Archive].[dbo].Sample
        SELECT CreatedDt, LastModDt, CompanyID, CompanyName, CompanyTaxName, TaxID, Active
        FROM deleted
END

ALTER TABLE [dbo].[Sample] ENABLE TRIGGER [tr_iud_Sample]
GO

ALTER TABLE [dbo].[Vendor] DISABLE TRIGGER [tr_iud_Sample]
GO

But this fails and I get this error:

Msg 4920, Level 16, State 0, Line 50
Operand type clash: varchar(15) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'NCI_COMMON') collation_name = 'Latin1_General_BIN2' is incompatible with varchar

Is there a way to have a trigger on encrypted table and if so, how to achieve the desired functionality?

Also, if SQL Server currently does not support that, is there any work around to achieve that?

Thank you in advance

Answer 1

As you are using Always Encrypted your SQL Server version System-Versioned Temporal Tables.

You can make your table system-versioned and leave the work of maintaining the history to the SQL Server Engine (also, when you are changing your table design, the engine will mitigate the changes to the history table).

Temporal tables can be queried using special clauses and bring to you new ways for analyzing historical data.

One disadvantage I have faced is that the history table columns must match the target table ones - so, if you need to have a ModifiedBy column in the history, you must change your application to populate such value in the original table.