SQL Trigger Update to Uppercase doesn't work using SQLBulkCopy Insertion

sqlbulkcopy sql-server-2008-r2 triggers

Question

I'm using .NET SQLBulkCopy to batch insert data into database. What I'm trying to do is to convert all specified string to uppercase when the data is inserted into the table. Here is my trigger script:

ALTER TRIGGER [dbo].[EmployeeTable_UpperCase]
   ON  [dbo].EmployeeTable
  AFTER INSERT
AS 
BEGIN   

UPDATE EmployeeTable SET 
        [CompanyCode] = UPPER([CompanyCode])
       ,[EmpId] = UPPER([EmpId])
       ,[EmpCode] = UPPER([EmpCode])          
Where Id in (Select Id from inserted)   
END

The script above doesn't work. None of my data is converted to uppercase at all. Does it has anything to do with using SQLBulkCopy in .net to do insertion? How should I make things work in my case?

1
0
3/5/2017 6:11:34 AM

Popular Answer

When you are creating your SqlBulkCopy object add SqlBulkCopyOptions.FireTriggers like so:

SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.FireTriggers);

FireTriggers
When specified, cause the server to fire the insert triggers for the rows being inserted into the database. - SqlBulkCopyOptions Enumeration - MSDN

1
3/5/2017 1:36:32 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow