[SQL] Implicit conversion of varchar value to varchar cannot be performed due to a collation...

When I try to change the collation of a column in SQL Server I get the following error.

Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict between "Latin1_General_CS_AI" and "SQL_Latin1_General_CP1_CI_AS" in add operator.
​

Below is the specific query I am trying to run. I get the same error regardless of whether I run the query manually or use the GUI.

ALTER TABLE myTable ADD myColumn NVARCHAR(152) COLLATE Latin1_General_CS_AI NULL;


Edit: Upon doing some more digging I think the issue may occur when sql server tries to rebuild one of the triggers on the table. Partially shown below... I apologize for the awful formatting but I can't seem to get it to format properly

Edit 2: Thanks for fixing my formatting! The line at the bottom of the code section below seems to be where the error is originating.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER trigger [dbo].[payee_audit_update_trigger] on [dbo].[payee] for update as
begin
set nocount on
declare @userId varchar(50), @sqlStatement varchar(255), @appId int, @transactionId int, @insertChangedValues varchar(255), @auditEnabled char(1), @timezoneOffsetMinutes int
exec AuditGetSessionInfo @@rowcount, @userId output, @sqlStatement output, @appId output, @transactionId output, @insertChangedValues output, @auditEnabled output, @timezoneOffsetMinutes output
if (@auditEnabled = 'N')
return
declare @auditDate datetime
set @auditDate = dateadd(MINUTE, @timezoneOffsetMinutes, getutcdate())
insert into audit_log_tx (company_id, application_id, change_date_time, id, table_name, transaction_id, user_id, sql_statement, primary_key_crc, primary_key_values, changed_values)
select inserted.company_id,@appId, @auditDate, dbo.AuditGetRowId(newId()), 'payee', @transactionId, @userId, @sqlStatement, binary_checksum(rtrim(inserted.id)), rtrim(inserted.id),
case when (inserted.company_id = deleted.company_id) or (inserted.company_id is null and deleted.company_id is null) then '' else 'company_id|' + case when deleted.company_id is null then '<null>' else rtrim(deleted.company_id) end + '|' + case when inserted.company_id is null then '<null>' else rtrim(inserted.company_id) end + '^' end + ...
------
case when (inserted.w4_status = deleted.w4_status) or (inserted.w4_status is null and deleted.w4_status is null) then '' else 'w4_status|' + case when deleted.w4_status is null then '<null>' else rtrim(deleted.w4_status) end + '|' + case when inserted.w4_status is null then '<null>' else rtrim(inserted.w4_status) end + '^' end +

Continue reading...