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?
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