upper on multiple colums as trigger? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2008 Forums

Transact-SQL (2008)

upper on multiple colums as trigger?

Author

Topic

mase2hot
Starting Member

36 Posts

Posted - 2010-04-25 : 15:23:09

I have a few columns that when I enter data into my table that I want upper to run on them would I have it as a trigger? If so how would I lay it out for multiple colums? I presume the below wouldnt work...

Thanks

UPDATE tax.dbo.Clients SET Postcode = UPPER(Postcode)
UPDATE tax.dbo.Clients SET NT_NUMBER = UPPER(NT_NUMBER)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-25 : 15:30:17

That code will work. You certainly don't need a trigger for this, although it would work too. You should also use a check constraint to protect the data, or always UPPER it on display.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

mase2hot
Starting Member

36 Posts

Posted - 2010-04-25 : 15:35:02

I know the code works but I want it to automatically run this code so I dont need to. Soory I dont get what you mean by check constraint or always UPPER it on disply?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-25 : 15:39:48

A check constraint is an object that you can put on a column to protect the data. If the incoming data doesn't follow the check constraint rule, then an error is returned.

You could just run UPPER on your SELECT statements when getting the data out of the table, that way it is always in uppercase and you don't need to worry how it is stored.

To automatically uppercase the data though, a trigger is a good way to do it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

mase2hot
Starting Member

36 Posts

Posted - 2010-04-25 : 16:16:13

my data is linked to ms access so I guess I'll make a trigger for it. Can I simply put the statements side by side in the trigger like I have in the first post?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-25 : 17:06:37

Yes you can put them inside the same trigger.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-25 : 17:36:39

You don't need two statements.


UPDATE tax.dbo.Clients SET Postcode = UPPER(Postcode),
                           NT_NUMBER = UPPER(NT_NUMBER)

No, you're never too old to Yak'n'Roll if you're too young to die.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-26 : 00:09:17

Since you are using 2008 (as the post is in 2008 forum) you can use the computed column directly.

Just try the below example to get more idea

Create table UpperTest
(Srno int identity,
Sname varchar(50),
UpperSname as Upper(Sname))

Insert into UpperTest (Sname)
Select 'TestName'

Select * from UpperTest

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-26 : 00:53:51

I really cant understand the reason behind storing the upper case equivalent of a column data in another column inside the table. Isnt this a presentation issue which can very easily achieved at front end using formatting functions. Is it to necessary to duplicate all data in a column for this purpose?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-26 : 12:17:45

I'm with visakh on this. There's no point in duplicating the data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

mase2hot
Starting Member

36 Posts

Posted - 2010-04-26 : 18:19:07

I tried the code below with no joy, I get error:

Msg 8197, Level 16, State 4, Procedure UPPER_COLUMNS, Line 1
The object 'dbo.Clients' does not exist or is invalid for this operation.

CREATE TRIGGER UPPER_COLUMNS
ON dbo.Clients
AFTER INSERT
AS
BEGIN
UPDATE tax.dbo.Clients SET Postcode = UPPER(Postcode),
NT_NUMBER = UPPER(NT_NUMBER)
END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-26 : 18:33:54

[code]
CREATE TRIGGER UPPER_COLUMNS
ON dbo.Clients
AFTER INSERT
AS
BEGIN

UPDATE c
SET
Postcode = UPPER(i.Postcode),
NT_NUMBER = UPPER(i.NT_NUMBER)
FROM dbo.Clients c
JOIN inserted i
ON c.PKColumn = i.PKColumn

END
[/code]

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-26 : 18:34:23

Modify PKColumn to whatever is your primary key.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

mase2hot
Starting Member

36 Posts

Posted - 2010-04-26 : 18:49:18

Hi I did,

and get error: Msg 8197, Level 16, State 4, Procedure UPPER_COLUMNS, Line 1
The object 'dbo.Clients' does not exist or is invalid for this operation.

CREATE TRIGGER UPPER_COLUMNS
ON dbo.Clients
AFTER INSERT
AS
BEGIN

UPDATE c
SET
Postcode = UPPER(i.Postcode),
NT_NUMBER = UPPER(i.NT_NUMBER)
FROM dbo.Clients c
JOIN inserted i
ON c.ID = i.ID

END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-26 : 18:51:16

Perhaps you aren't in the right database. Either select the correct database in the dropdown or add it to the script:

USE dbNameGoesHere
GO

CREATE TRIGGER ...

GO

If you still get an error, then perhaps "dbo" is not the owner of the Clients table. In that case, switch "dbo" to the object owner.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

mase2hot
Starting Member

36 Posts

Posted - 2010-04-27 : 16:53:40

Thanks, yeah I wasnt on the correct database!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-27 : 17:06:05

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-27 : 17:24:06

quote:
Originally posted by mase2hot

Thanks, yeah I wasnt on the correct database!

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources