Global UPPER function with trigger
-
We decided to have all data in our database be in uppercase. Is there a way using a trigger that would globally, not just on certain tables, catch all updates and inserts and use the UPPER function to convert the updated/inserted values?
Thanks,
Karl
-
No, you'd have to set triggers on all tables. Or set a job to go through periodically and just set all data. Be cacreful of logging if you do the latter.
-
Here is a script to look for and potentially update all fields which meet your needs.
I have commented out the Execute statement so you can see what will happen. I would highly suggest you go through each of these statements and make sure you will update those columns you want, (you can omit tables; you will need to alter this to omit specific fields).
Triggers will not update what already exists. I would suggest you go through the painful process of checking each table independently. It is easier than trying to fix what you did not want to update.
SELECT ISC.TABLE_NAME, ISC.COLUMN_NAME, ISC.DATA_TYPE
INTO #TableInformation
FROM INFORMATION_SCHEMA.COLUMNS ISC
INNER JOIN sysobjects SO ON( ISC.TABLE_NAME = SO.name)
WHERE xtype IN( 'U', 'S')
AND name NOT LIKE 'sys%'
-- AND name NOT IN( 'dir04_LAB', 'sub_human_code')
AND UID = 1.0 -- Indicates a dbo created table
DECLARE @TableName varchar(100),
@ColumnName varchar(100),
@DataType varchar(50),
@SLQUpdate varchar(1000)
DECLARE TableInformation INSENSITIVE CURSOR FOR
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM #TableInformation
WHERE TABLE_NAME IS NOT NULL
AND COLUMN_NAME IS NOT NULL
ORDER BY TABLE_NAME, COLUMN_NAME ASC
OPEN TableInformation
next_record:
FETCH NEXT FROM TableInformation INTO
@TableName, @ColumnName, @DataType
IF @@FETCH_STATUS = 0
BEGIN
IF ISNULL( @TableName, 'N/A') <> 'N/A' AND ISNULL( @ColumnName, 'N/A') <> 'N/A'
BEGIN
IF @DataType IN( 'char', 'nchar', 'nvarchar', 'text', 'varchar')
BEGIN
SELECT @SLQUpdate = 'UPDATE [' + @TableName + '] SET ' + CHAR(13) + CHAR(9) +
@ColumnName + ' = UPPER( [' + @ColumnName + ']) ' + CHAR(13) +
'WHERE ' + @ColumnName + ' <> UPPER( [' + @ColumnName + ']) '
PRINT @SLQUpdate
PRINT SPACE(1)
-- EXECUTE( @SLQUpdate)
END
END
GOTO next_record
END
CLOSE TableInformation
DEALLOCATE TableInformation
-- DROP TABLE #TableInformation
Hope this helps.
I wasn't born stupid - I had to study.
-
Thanks.
-
I just checked this again and the WHERE statement may prevent Updating
I wasn't born stupid - I had to study.
-
This may be a better solution since you only want to convert those records that aren't already UPPER case. I am assuming the first character may be UPPER case, but the second character is not...
WHERE ASCII( SUBSTRING( LONG_PROJECT_TITLE, 2, 1)) <> ASCII( SUBSTRING( UPPER( LONG_PROJECT_TITLE), 2, 1))
(Sorry, I should have checked this more thoroughly before posting
)I wasn't born stupid - I had to study.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply