Posted by Erland Sommarskog on 04/29/06 00:38

rcamarda (robc390@hotmail.com) writes:
> more research I have:
> CREATE TRIGGER F_Student_SSN.eSSN
> ON ds_v6_staging.F_STUDENT_SSN
> update, insert
> AS
> update f_student_ssn
> set essn = cast(EncryptByAsymKey(AsymKey_ID('Student_aKey'),
> cast(eSSN as nvarchar(11))) as nvarchar(40))
>
> But still get error

I realise that you are working with sensitive data, and may want to
disclose much. However, with knowing what error you get, it's difficult
to assist.

Of course the trigger as such is not a good one, since you are updating
the entire table, you will encrypt already encrypted data on each
INSERT or UPDATE.

You need to add:

update f_student_ssn
set essn = cast(EncryptByAsymKey(AsymKey_ID('Student_aKey'),
cast(eSSN as nvarchar(11))) as nvarchar(40))
from f_student_ssn f
join inserted i ON f.pkcol = i.pkcol

The table "inserted" holds an after-image of the inserted/updated rows.
Note the plural: a trigger fires one per statement.

But the above only makes sense for an INSERT trigger. For an UPDATE
it's tricker. You could add:

IF UPDATE(eSSN)
BEGIN
UPDATE ...
END

But if someone for some reason says:

UPDATE tbl SET eSSN = eSSN

you will end up encrypting the encrypted value.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Re: Compound Primary Key - order not as expected
• Prev in forum: Re: Update statement
• Thread view: Re: Trigger to encrypt field before write

[Reply to this message]