By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,733 Members | 925 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,733 IT Pros & Developers. It's quick & easy.

uppercase trigger

P: n/a
How can I create a trigger that obliges UPPERCASE of a field in the
database?

thanks
Jul 23 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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)
Jul 23 '05 #2

P: n/a
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
Jul 23 '05 #3

P: n/a
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

Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.