在预期条件的上下文中指定的非布尔类型的表达式，靠近 ')' [英] An expression of non-boolean type specified in a context where a condition is expected, near ')'

问题描述

如果 T_Referral 表上发生任何更新，我在触发器中有一个脚本来插入审计表，并且我正在使用 Decrypt 函数来解密其中一列中的数据.我的触发器代码是:

I have a script in trigger to insert the audit table if any update happens on T_Referral table and i am using a Decrypt function to Decrypt the data in one of the column. My code in trigger is :

DECLARE @Sql_Insert nvarchar(max)
SET @Sql_Insert = ''
SET @Sql_Insert='INSERT INTO [Logg].AuditLogData(TableName, ColumnName, OldValue, OldValue_Decode, NewValue, NewValue_Decode, AuditSubCategoryID,[GuID])
select TableName, ColumnName, OldValue_Decode, case when ColumnName = ''BookingUserReferenceValue'' then utl.sfDecrypt(NewValue,0) Else NewValue end, NewValue_Decode, AuditSubCategoryID,[GuID] from #AuditLogData
where ISNULL(OldValue,'') != ISNULL([NewValue],'')'

exec utl.uspOpenOrCloseEncryptionKey 'open'
exec(@Sql_Insert )
exec utl.uspOpenOrCloseEncryptionKey 'close'

<小时>

我的更新脚本是

---

My update script is

Update RTS.T_Referral   
set BookingUserReferenceValue =  cast ('John Wayne' as varbinary(256))
where ReferralId = 20

我正在将记录更新为 John Wayne 作为 T_Referral 表中的 varbinary(记录看起来像 0x4A6F686E205761796E65)，当更新触发器被调用时会将该记录加载为审计表中的 John Wayne.当记录插入 BookingUserReferenceValue 时，它​​将被加密.BookingUserReferenceValue 列的数据类型是 Varbinary(256).当我尝试更新该列中的记录时，出现错误:

I am Updating the record to John Wayne as varbinary in T_Referral table(record looks like 0x4A6F686E205761796E65) and when update trigger is called it would load that record as John Wayne in Audit table. When the record is inserted in BookingUserReferenceValue it will be encrypted. The datatype of column BookingUserReferenceValue is Varbinary(256). when i try to update the record in that column i get error :

在上下文中指定的非布尔类型的表达式，其中 a条件是预期的，靠近)".

An expression of non-boolean type specified in a context where a condition is expected, near ')'.

知道有什么问题吗?谢谢

Any Idea whats wrong? Thanks

推荐答案

你需要转义单引号:

SET @Sql_Insert='
INSERT INTO [Logg].AuditLogData(TableName, ColumnName, OldValue, OldValue_Decode, NewValue, NewValue_Decode, AuditSubCategoryID,[GuID])
select 
    TableName, ColumnName, OldValue, OldValue_Decode, 
    case when ColumnName = ''BookingUserReferenceValue'' then utl.sfDecrypt(NewValue,0) Else NewValue end,
    NewValue_Decode, AuditSubCategoryID,[GuID] 
from #AuditLogData
where 
    ISNULL(OldValue, cast('''' as varbinary(256))) != 
    ISNULL([NewValue], cast('''' as varbinary(256)))
';

这篇关于在预期条件的上下文中指定的非布尔类型的表达式，靠近 ')'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！