Posted by Erland Sommarskog on 07/12/05 12:16

Fernand St-Georges (fernand.st-georges@videotron.ca) writes:
> How can I create a trigger that obliges UPPERCASE of a field in the
> database?

CREATE TRIGGER uppercase_tri ON tbl FOR INSERT, UPDATE AS
IF UPDATE(uppercase_col)
BEGIN
UPDATE tbl
SET uppercase_col = upper(i.uppercase_col)
FROM tbl t
JOIN inserted i ON t.keycol = i.keycol
END

If the data comes from GUI entry, I tend however to agree with MGFoster,
that this should be done in the client, because it's confusing for the
user type 'nisse' and next time he looks at the data he sees 'NISSE'.

In this case you may still want enforce the business rule in the database.
To this end a constraint would do. If you are using a case-sensitive
collation it's as easy as:

col varchar(20) NULL
CONSTRAINT tbl_col_uppercase (CHECK col = upper(col))

If you use a case-insensitive collation, it's trickier. But this should
work:

col varchar(20) NULL
CONSTRAINT tbl_col_uppercase
(CHECK col COLLATE Finnish_Swedish_CS_AS = upper(col))

Instead of Finnish_Swedish_CS_AS, use the case-sensitive collation that
matches your case-insensitive collation.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]