Encryption and trigger table

Question

Our database has triggers to insert records into separate audit tables that contain the same columns as the base table. If we are encrypting some of the columns on the base table then I assume that the corresponding audit table will have to have those same columns encrypted. Is this correct?

Answer 1

At a minimum you need to encrypt the audit columns with the same key and in the same way (Randomized or deterministic.) I am not fully sure that it will work, but I am short on time, and I don't have the time to test. But if things are not set up correctly, you will get an error when you insert, or when you create the trigger.

(Ben, since I have been answering several of David's questions recently, I know that he is looking at Always Encrypted.)

Answer 2

Depending on the type of encryption, but I would say yes as if they are unencrypted in the audit table 1) you have to do a decent amount of plumbing to get it unencrypted just for audit, and 2) you probably invalidate an audit since the purpose of encrypting the data is to not have compromise, so if audit has them unencrypted, then that would be a target to get the unencrypted data.