触发函数与其他表一起使用，但不是这个.讯息102 [英] Trigger functions with other tables, but not this one. Msg 102

问题描述

我要从我以前的帖子中撤消，

I'm pulling over from a previous post of mine, MSSQL Msg 102 '.' and column name begins with number.

当我尝试运行UPDATE时，在存储过程中始终收到Msg 102错误的语法.

I keep getting a Msg 102 incorrect syntax within a stored procedure, when I try to run an UPDATE.

好的，我想我已经找到问题所在了.它在TRIGGER中，用于插入，更新，删除:

OK, I think I have found where the issue is at. It is in a TRIGGER, for insert, update, delete:

DECLARE
@bit INT ,
@field INT ,
@maxfield INT ,
@char INT ,
@fieldname VARCHAR(64) ,
@TableName VARCHAR(64) ,
@PKCols VARCHAR(1000) ,
@sql VARCHAR(2000) ,
@UpdatedDate VARCHAR(21) ,
@UserName VARCHAR(64) ,
@TYPE CHAR(1) ,
@PKSelect VARCHAR(1000) ,
@OldValueSQL VARCHAR(128) ,
@NewValueSQL VARCHAR(128) ,
@fieldtype VARCHAR(32) ,
@compatible_triggerfield BIT

select @TableName = 'tblItems'

SELECT @UserName = dbo.fnGetContextInfoString() ,@UpdateDate = CONVERT(VARCHAR(8), GETDATE(), 112)
+ ' ' + CONVERT(VARCHAR(12), GETDATE(), 114)

IF EXISTS (SELECT * FROM inserted)
IF EXISTS (SELECT * FROM deleted)
SELECT @Type = 'U'
ELSE
SELECT @Type = 'I'
ELSE
SELECT @Type = 'D'

SELECT [ID],[ResourceName],[DevItemNum],[Email],[ItemOrg],[ItemPOC],[24_7POC],[ItemDesc],[Active],[EntryArea],[Entry] INTO #ins FROM inserted
SELECT [ID],[ResourceName],[DevItemNum],[Email],[ItemOrg],[ItemPOC],[24_7POC],[ItemDesc],[Active],[EntryArea],[Entry] INTO #del FROM deleted

SELECT @PKCols = COALESCE(@PKCols + 'and', 'on')
  + ' i.' + cu.COLUMN_NAME + ' = d.' + cu.COLUMN_NAME
  FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
 INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu
  WHERE   pk.TABLE_NAME = @TableName
  AND     CONSTRAINT_TYPE = 'PRIMARY_KEY'
  AND     cu.TABLE_NAME = pk.TABLE_NAME
  AND     cu.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

SELECT @PKSelect = COALESCE(@PKSelect+'+'.'')
  + '''<' + COLUMN_NAME
  + '=''convert(varchar(100), coalesce(i.' + COLUMN_NAME + ',d.' + COLUMN_NAME + '))+''>'''
  FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk,
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu
  WHERE pk.TABLE_NAME = @TableName
  AND     CONSTRAINT_TYPE = 'PRIMARY_KEY'
  AND     cu.TABLE_NAME = pk.TABLE_NAME
  AND     cu.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
IF @PKCols IS NULL
BEGIN
  RAISERROR('no PK on table %s', 16, -1, @TableName)
  RETURN
END
IF @Type = 'I' or @ Type = 'D'
BEGIN
SET @maxfield = 1
SET @field = 0
END
ELSE
BEGIN
SELECT field = 0,
  @maxfield = MAX(ORDINAL_POSITION)
  FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName
END
WHILE @field < @maxfield
BEGIN
SELECT @field = MIN(ORDINAL_POSITION)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = @TableName
  AND ORDINAL_POSITION > @field
SELECT @bit = (@field -1)% 8 + 1
SELECT @bit = POWER(2,@bit -1)
SELECT @char = ((@field - 1) / 8) + 1
IF SUBSTRING(COLUMNS_UPDATED(),@char, 1) & @bit > 0
  OR @Type IN ('I','D')
BEGIN
SELECT @fieldname = COLUMN_NAME, @fieldtype = DATA_TYPE
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = @TableName
  AND ORDINAL_POSITION = @field
IF @fieldtype = 'text' or @fieldtype = 'ntext' or @fieldtype = 'image' SET @compatible_triggerfield = 0 else SET @compatible_triggerfield = 1
SET @OldValueSQL = ',convert(varchar(1000),d.' + @fieldname + ')'
SET @NewValueSQL = ',convert(varchar(1000),i.' + @fieldname + ')'
IF @compatible_triggerfield = 0
SET @OldValueSQL = ',''n/a'''
SET @NewValueSQL = ',''n/a'''
END
SELECT @sql = '
insert Audit (    Type,
  TableName,
  PrimaryKeyField,
  FieldName,
  FieldType,
  OldValue,
  NewValue,
  UpdateDate,
  UserName)
select ''' + @Type + ''','''
+ @TableName + ''',' + @PKSelect
+ ',''' + @fieldname + ''''
+ ',''' + @fieldtype + ''''
+ @OldValueSQL
+ @NewValueSQL
+ ',''' + @UpdateDate + ''''
+ ',''' + @UserName + ''''
+ ' from #ins i full outer join #del d'
+ @PKCols
IF @compatible_fieldtrigger = 1
BEGIN
  SET @sql = @sql
  + ' where i.' + @fieldname + '<> d.' + @fieldname
  + ' or (i.' + @fieldname + ' is null and  d.'
+ @fieldname
+ ' is not null)'
  + ' or (i.' + @fieldname + ' is not null and d.'
+ @fieldname
+ ' is null)'
END
EXEC(@sql)
END
END

这是踢脚线.如果我注释掉 SELECT @PKSELECT = ... 语句，它似乎可以正常运行.

Here's the kicker. If I comment out the SELECT @PKSELECT = ... statement, it seems to run fine.

这里有任何帮助，将是很好的.

Anymore help here, would be nice.

推荐答案

这应该可以做到.我在代码中添加了更多的'['/']'(方括号).所有这些都没有@Larnu的帮助，后者有点像Arse.拉努(Larnu)一直想指出出什么问题了，而不是提出正确的事情.

This should do it. I added a few more '['/']' (square brackets) in the code. All this, without help from @Larnu, who is somewhat an Arse. Larnu always wants to point out what is wrong, and not offer what could be right.

ALTER TRIGGER [dbo].[tblItems_ChangeTracking] on [dbo].[tblItems] for insert, update, delete
AS
DECLARE @bit INT ,
@field INT ,
@maxfield INT ,
@char INT ,
@fieldname VARCHAR(64) ,
@TableName VARCHAR(64) ,
@PKCols VARCHAR(1000) ,
@sql VARCHAR(2000) ,
@UpdatedDate VARCHAR(21) ,
@UserName VARCHAR(64) ,
@TYPE CHAR(1) ,
@PKSelect VARCHAR(1000) ,
@OldValueSQL VARCHAR(128) ,
@NewValueSQL VARCHAR(128) ,
@fieldtype VARCHAR(32) ,
@compatible_triggerfield BIT

select @TableName = 'tblItems'

SELECT @UserName = dbo.fnGetContextInfoString() ,@UpdateDate = CONVERT(VARCHAR(8), GETDATE(), 112)
+ ' ' + CONVERT(VARCHAR(12), GETDATE(), 114)

IF EXISTS (SELECT * FROM inserted)
IF EXISTS (SELECT * FROM deleted)
SELECT @Type = 'U'
ELSE
SELECT @Type = 'I'
ELSE
SELECT @Type = 'D'

SELECT [ID],[ResourceName],[DevItemNum],[Email],[ItemOrg],[ItemPOC],[24_7POC],[ItemDesc],[Active],[EntryArea],[Entry] INTO #ins FROM inserted
SELECT [ID],[ResourceName],[DevItemNum],[Email],[ItemOrg],[ItemPOC],[24_7POC],[ItemDesc],[Active],[EntryArea],[Entry] INTO #del FROM deleted

SELECT @PKCols = COALESCE(@PKCols + 'and', 'on')
  + ' i.[' + cu.COLUMN_NAME + '] = d.[' + cu.COLUMN_NAME + ']'
  FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
 INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu
  WHERE   pk.TABLE_NAME = @TableName
  AND     CONSTRAINT_TYPE = 'PRIMARY_KEY'
  AND     cu.TABLE_NAME = pk.TABLE_NAME
  AND     cu.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

SELECT @PKSelect = COALESCE(@PKSelect+'+'.'')
  + '''<' + COLUMN_NAME
  + '=''convert(varchar(100), coalesce(i.[' + COLUMN_NAME + '],d.[' + COLUMN_NAME + ']))+''>'''
  FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk,
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu
  WHERE pk.TABLE_NAME = @TableName
  AND     CONSTRAINT_TYPE = 'PRIMARY_KEY'
  AND     cu.TABLE_NAME = pk.TABLE_NAME
  AND     cu.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
IF @PKCols IS NULL
BEGIN
  RAISERROR('no PK on table %s', 16, -1, @TableName)
  RETURN
END
IF @Type = 'I' or @ Type = 'D'
BEGIN
SET @maxfield = 1
SET @field = 0
END
ELSE
BEGIN
SELECT field = 0,
  @maxfield = MAX(ORDINAL_POSITION)
  FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName
END
WHILE @field < @maxfield
BEGIN
SELECT @field = MIN(ORDINAL_POSITION)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = @TableName
  AND ORDINAL_POSITION > @field
SELECT @bit = (@field -1)% 8 + 1
SELECT @bit = POWER(2,@bit -1)
SELECT @char = ((@field - 1) / 8) + 1
IF SUBSTRING(COLUMNS_UPDATED(),@char, 1) & @bit > 0
  OR @Type IN ('I','D')
BEGIN
SELECT @fieldname = COLUMN_NAME, @fieldtype = DATA_TYPE
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = @TableName
  AND ORDINAL_POSITION = @field
IF @fieldtype = 'text' or @fieldtype = 'ntext' or @fieldtype = 'image' SET @compatible_triggerfield = 0 else SET @compatible_triggerfield = 1
SET @OldValueSQL = ',convert(varchar(1000),d.[' + @fieldname + '])'
SET @NewValueSQL = ',convert(varchar(1000),i.[' + @fieldname + '])'
IF @compatible_triggerfield = 0
SET @OldValueSQL = ',''n/a'''
SET @NewValueSQL = ',''n/a'''
END
SELECT @sql = '
insert Audit (    Type,
  TableName,
  PrimaryKeyField,
  FieldName,
  FieldType,
  OldValue,
  NewValue,
  UpdateDate,
  UserName)
select ''' + @Type + ''','''
+ @TableName + ''',' + @PKSelect
+ ',''[' + @fieldname + ']'''
+ ',''' + @fieldtype + ''''
+ @OldValueSQL
+ @NewValueSQL
+ ',''' + @UpdateDate + ''''
+ ',''' + @UserName + ''''
+ ' from #ins i full outer join #del d'
+ @PKCols
IF @compatible_fieldtrigger = 1
BEGIN
  SET @sql = @sql
  + ' where i.[' + @fieldname + '] <> d.[' + @fieldname
  + '] or (i.[' + @fieldname + '] is null and  d.['
+ @fieldname
+ '] is not null)'
  + ' or (i.[' + @fieldname + '] is not null and d.['
+ @fieldname
+ '] is null)'
END
EXEC(@sql)
END
END

我确实得到了同事的帮助，这似乎行得通.

I did have help from a coworker, and this appears to work.

这似乎可以工作，不需要输入'['+ COLUMN_NAME +']'，但是比后悔要安全得多.

It seems to work, without needing the entry of '[' + COLUMN_NAME + ']', but better safe than sorry.

这篇关于触发函数与其他表一起使用，但不是这个.讯息102的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！