hi,I get a very strange error message when I try to save my table:- Unable to preserve trigger 'SecurityLog'. ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name '['.[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name ']'.
There is no column named [ in my table, nor is there a column without a name. Actually I did not change anything that has to do with table names or triggers at all. The only thing I changed is the data format of an existing column from datetime to smalldatetime. Only this and nothing more. I can also close the table without saving, open it again to retry...same error.Where does this error come from?I really did not change anything about the trigger and the trigger is working perfectly fine.I have attached the trigger code, it's not my own I copied it from the net and adapted it, what it does is actually check if a flag is set in a table and if not it logs the changed made to the table. It deals with all columns there are, so the trigger would work with any table. Don't aks me what each single line of this trigger does, all I can say is that it works perfectly fine...it's just that I get an error when I try to change the data type of a column from datetime to smalldatetime. Testing some other functions, I also can't save if I try to rename a column or insert a column...what is going on?CREATE trigger SecurityLog on myTable for insert, update, deleteasdeclare @bit int , @field int , @maxfield int , @char int , @fieldname varchar(128) , @TableName varchar(128) , @PKCols varchar(1000) , @sql varchar(2000), @UpdateDate varchar(21) , @UserName varchar(128) , @Type char(1) , @PKSelect varchar(1000) select @TableName = 'myTable' -- date and user select @UserName = system_user , @UpdateDate = convert(varchar(8), getdate(), 112) + ' ' + convert(varchar(12), getdate(), 114) if exists (select * from TableLocks where AccessFunctionID=20 and LockType>0) return -- Action if exists (select * from inserted) if exists (select * from deleted) select @Type = 'U' else select @Type = 'I' else select @Type = 'D' -- get list of columns select * into #ins from inserted select * into #del from deleted -- Get primary key columns for full outer join select @PKCols = coalesce(@PKCols + ' and', ' on') + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk , INFORMATION_SCHEMA.KEY_COLUMN_USAGE c where pk.TABLE_NAME = @TableName and CONSTRAINT_TYPE = 'PRIMARY KEY' and c.TABLE_NAME = pk.TABLE_NAME and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME -- Get primary key select for insert 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 c where pk.TABLE_NAME = @TableName and CONSTRAINT_TYPE = 'PRIMARY KEY' and c.TABLE_NAME = pk.TABLE_NAME and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME if @PKCols is null begin raiserror('no PK on table %s', 16, -1, @TableName) return end select @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName 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 + "]" from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = @field select @sql = 'insert tSecurityLog (Type, TableName, PK, FieldName, OldValue, NewValue, UpdateDate, UserName)' select @sql = @sql + ' select ''' + @Type + '''' select @sql = @sql + ',''' + @TableName + '''' select @sql = @sql + ',' + @PKSelect select @sql = @sql + ',''' + @fieldname + '''' select @sql = @sql + ',convert(varchar(1000),d.' + @fieldname + ')' select @sql = @sql + ',convert(varchar(1000),i.' + @fieldname + ')' select @sql = @sql + ',''' + @UpdateDate + '''' select @sql = @sql + ',''' + @UserName + '''' select @sql = @sql + ' from #ins i full outer join #del d' select @sql = @sql + @PKCols select @sql = @sql + ' where i.' + @fieldname + ' <> d.' + @fieldname select @sql = @sql + ' or (i.' + @fieldname + ' is null and d.' + @fieldname + ' is not null)' select @sql = @sql + ' or (i.' + @fieldname + ' is not null and d.' + @fieldname + ' is null)' exec (@sql) end endAny help would be apprechiated. thxsth_Weird
Stange error message when saving table
Posted - 2009-07-16 : 09:03:00
