March 26, 2009 at 3:07 am
All,
I have to convert lower case letters into upper case automatically. I dont want to force the user to enter UPPER case. whatever they enter simply i need to convert into UPPER and then need to insert into the corresponding table.
Initially i have created one CHECK constraint which will force the user to enter UPPER case only. But the requirement is to change the lower case into UPPER automaticaly, I used trigger.
create table t1
(
eno char(2)
)
drop trigger i_t1
go
create trigger i_t1
on t1
for insert
as
update t1
set eno = upper(inserted.eno)
from inserted
where t1.eno = inserted.eno
insert into t1 values ('aa')
insert into t1 values ('bb')
insert into t1 values ('cc')
insert into t1 values ('dd')
Any suggestions about my code? any other work around is always welcome!
karthik
March 26, 2009 at 3:41 am
Why not do this client-side? It's formatting, after all.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 26, 2009 at 4:41 am
If it has to be done server-side then I would go with the trigger approach as well but remember to handle updates to the column as well if necessary.
March 26, 2009 at 5:20 am
HI,
Before inserting into the table you just made the data to upper then insert,
It’s so simple than trigger.
ARUN SAS
March 26, 2009 at 5:39 am
Why not use a computed column:
create table #t1
(
eno char(2)
,enoUpper as Upper(eno)
)
Buzz
March 26, 2009 at 5:42 am
I would do it with an instead of trigger and not with a trigger.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 29, 2009 at 12:28 am
Change the insert statement to something like INSERT table UPPER(string). we do this in many of the apps we support.
March 29, 2009 at 1:25 pm
If you had enforced stored procedures for table access, then you could do it there.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 30, 2009 at 1:26 am
RBarryYoung (3/29/2009)
If you had enforced stored procedures for table access, then you could do it there.
That's a good point, and is pretty much our philosphy for a variety of reasons ranging from better security to less bandwidth usage across our WAN. It's also much easier to change a single stored procedure on one server than it is to distribute a new executable to 500 desktops. In our environment, a new executable can take months to get through all of the testing and layering process, so we tend to make the client as thin as possible, and put all of the logic in the database.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply