Trigger - Upper case a column field records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Trigger - Upper case a column field records

Author

Topic

micnie_2020
Posting Yak Master

232 Posts

Posted - 2009-05-21 : 22:34:01

Dear all,

Warmest Regards.

How to auto Upper case for a column what ever style font they key in either small/cap; all character will auto convert to be upper case in database column by trigger. Did any one here know the solution?

Please advise

Thank you

Regards,
Michelle

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-21 : 22:40:30

[code]
create trigger tu_table1 on table1 for update
as
begin
update t
set col = upper(i.col)
from table1 t inner join inserted i
on t.pk = i.pk
end
[/code]

KH
[spoiler]Time is always against us[/spoiler]

micnie_2020
Posting Yak Master

232 Posts

Posted - 2009-05-21 : 23:09:05

Dear Sir,

Great Solution.
Thank you very much.

I have do ammendments using for insert, here is the solution:-

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER trigger upp_addressbook on addressbook for insert
as
begin
update t
set givenname = upper(givenname)
from addressbook t
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Regards,
Michelle

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-21 : 23:11:49

quote:
Originally posted by micnie_2020

Dear Sir,

Great Solution.
Thank you very much.

I have do ammendments using for insert, here is the solution:-

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER trigger upp_addressbook on addressbook for insert
as
begin
update t
set givenname = upper(givenname)
from addressbook t
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Regards,
Michelle

That will updates ALL records in the table every time record(s) are inserted. You should INNER JOIN to the inserted table as in my example.

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-21 : 23:12:26

read this http://www.sqlteam.com/article/an-introduction-to-triggers-part-i

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-22 : 03:31:11

why are you concerned about this in first place? Regardless of the case in which you store the data, you can always display the data in case you want in front end using formatting functions. I don't prefer using trigger just to change case of data that is stored unless i'm using some case sensitive collation for my database.

micnie_2020
Posting Yak Master

232 Posts

Posted - 2009-05-22 : 04:15:46

Dear Sir,

KH - Thanx for pointing my mistake.
Visakh16 - I now in front end can do it, but just wanted to know the method of using trigger to do such thing. To expand more knowledge about this, so at least the skill can be expanded even seem useless to others; everyone have different style. I love to explore... :)

Thanx for your reply, guys.

Regards,
Michelle

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-22 : 04:22:25

ok...no problem in exploring :). i was just telling that in practical case i would have prefered to let front end do this

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources