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

c# sql sqlbulkcopy sql-server

Question

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?

Accepted 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...
}


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why