uppercase trigger

[Fernand St-Georges]

How can I create a trigger that obliges UPPERCASE of a field in the
database?

thanks

[MGFoster]

Fernand St-Georges wrote:

How can I create a trigger that obliges UPPERCASE of a field in the
database?

Don't use a trigger. Set up the front-end to change data to upper case.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

[Erland Sommarskog]

Fernand St-Georges (fe****************@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, es****@sommarskog.se

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

[Razvan Socol]

If you have enabled recursive triggers (by default, they are disabled),
you may want to do something like this:

ALTER TRIGGER uppercase_tri ON tbl FOR INSERT, UPDATE AS
IF @@ROWCOUNT>0 AND UPDATE(uppercase_col)
BEGIN
UPDATE tbl SET uppercase_col = upper(i.uppercase_col)
FROM tbl t JOIN inserted i ON t.keycol = i.keycol
WHERE t.uppercase_col COLLATE Romanian_CS_AS
<> UPPER(i.uppercase_col)
END

(of course, replace Romanian_CS_AS with the case-sensitive collation
that matches your case-insensitive collation)

Razvan