user3818229 user3818229 - 7 months ago 26
SQL Question

DB trigger won't execute after SqlBulkCopy write to server new rows

I'm trying to update

ssn
column after new row is inserted. Specify I need to set null to
ssn
column and set that encrypting value to another column. Is there any mistakes or what's wrong with my trigger?

ALTER TRIGGER [dbo].[MemberInsert]
ON [dbo].[Member]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;

OPEN SYMMETRIC KEY SSN_Key
DECRYPTION BY CERTIFICATE MyCer;

UPDATE Member
SET [EncryptedSSN] = EncryptByKey(Key_GUID('SSN_Key'), inserted.SSN)
FROM inserted
INNER JOIN dbo.Member On inserted.MemberId = Member.MemberId

UPDATE Member
SET [SSN] = null
FROM inserted
INNER JOIN dbo.Member On inserted.MemberId = Member.MemberId
END


Right now nothing happend with added rows. Also I have to add I insert new rows using
SqlBulkCopy
. Is it problem for trigger to catch event by this way?

Answer

SqlBulkCopy doesn't check trigger by default, you must specify options: SqlBulkCopyOptions

var options = SqlBulkCopyOptions.FireTriggers | SqlBulkCopyOptions.CheckConstraints;
using (var bulkCopy = new SqlBulkCopy(connection, options, transaction))
{
    // ...Code...
}
Comments