Check for an encrypted value before insert

[djj55]

Hello, SQL 2008R2
What is the best way to check for a duplicate value when inserting into an encrypted column?
I know I can do this:

INSERT INTO MyTable (EncryptedNumber) 
SELECT DISTINCT EncryptByKey(Key_GUID('My_Key_01'), '123123456123456789') AS EncryptedNumber 
WHERE NOT EXISTS (SELECT * FROM MyTable 
                    WHERE CONVERT(varchar, DecryptByKey(EncryptedNumber)) = '123123456123456789')

however, I would like to put safe guards on the table to keep someone from inserting a douplicat by on criteria.
Something like a instead of trigger.

Thanks,

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[djj55]

Hello,
Well I found a way. I was thinking of an instead of trigger, however, by using an update trigger and a rollback transaction it works.

I use example C of the Create Trigger BOL. I decrypted the table value and joined to the insert table decrypted value.

IF EXISTS (SELECT * FROM MyTable a 
           JOIN inserted i 
           ON CONVERT(varchar, DecryptByKey(a.EncryptedNumber)) 
                = CONVERT(varchar, DecryptByKey(i.EncryptedNumber))
          )
BEGIN
RAISERROR('Duplicate number',16, 1);
ROLLBACK TRANSACTION;
RETURN
END;

No need to open the key if it is already open by the stored procedure.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!