CODEWITHSUNDEEP
c#sqlsql-serversqlbulkcopy
user3818229
user3818229

Reputation: 1637

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?

Upvotes: 0

Views: 193

Answers (1)

Jonathan Magnan
Jonathan Magnan

Reputation: 11347

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

Upvotes: 1

Related Questions