SQL Trigger for trimming String fields

[Juniorlinn]

greetings!! the below trigger works fine in SQL 2000 and doesn't take
up much resources, it's a very simple solution to trim text fields at
input in SQL. I know a lot of folks will say to create input masks at
the UI level and that SQL is a restricted back-end DB, but to be quite
honest I don't trust those UI guys and as a DBA I will have to clean
the mess when they forget to validate. Hope it can be usefull to any
of you too. ;)
----------------------------------------------------------------------

CREATE TRIGGER [TRIMMER_TGR] ON [dbo].[MyTable]
AFTER INSERT, UPDATE
AS

IF UPDATE (MyStringField)
DECLARE @TRIMMEDFIELD NVARCHAR(50)
DECLARE @MYID INT

SELECT @TRIMMEDFIELD = MyStringField from Inserted
SELECT @MYID = ID from Inserted

BEGIN
UPDATE tblDocket SET MyStringField = RTRIM(LTRIM(@TRIMMEDFIELD))
WHERE ID = @MYID
END

[Hugo Kornelis]

On 23 Apr 2004 12:12:18 -0700, Juniorlinn wrote:

greetings!! the below trigger works fine in SQL 2000 and doesn't take
up much resources, it's a very simple solution to trim text fields at
input in SQL. I know a lot of folks will say to create input masks at
the UI level and that SQL is a restricted back-end DB, but to be quite
honest I don't trust those UI guys and as a DBA I will have to clean
the mess when they forget to validate. Hope it can be usefull to any
of you too. ;)

Unfortunately, you didn't test it very well. For starters, you never
tested it in a database with the recursive triggers option set to on.
And you didn't test the version you posted here, since you specify it
as a trigger on MyTable, yet you perform an UPDATE of tblDocket.
You didn't try this either:

UPDATE MyTable
SET MyStringField = ' ab '
WHERE (anything that matches 2 rows or more)

(or -equivalent- an insert ... select with 2 or more rows)
And you also didn't test:

UPDATE MyTable
SET AnotherColumn = 123
WHERE (anything that matches at least 1 row)

Here's a better way to achieve what you want:

CREATE TRIGGER [TRIMMER_TGR] ON [dbo].[MyTable]
AFTER INSERT, UPDATE
AS
IF UPDATE (MyStringField)
BEGIN
UPDATE tblDocket
SET MyStringField = RTRIM(LTRIM(MyStringField))
WHERE ID IN (SELECT ID FROM inserted)
END
Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[--CELKO--]

>> I know a lot of folks will say to create input masks at the UI
level and that SQL is a restricted back-end DB, but to be quite honest
I don't trust those UI guys and as a DBA I will have to clean the mess
when they forget to validate. <<

The people that feel that way are called "experts"; but you are not
paranoid about the current crop of app developers. We seem to have a
crop of people who can pass a certification exam, but not solve a
problem.

Instead of a trigger, which is procedural, proprietary and expensive,
why not use a constraint? That is declarative, portable and is fired
only on the row(s) involved instead of the whole table?

CREATE TABLE Floob
(...
foobar VARCHAR(15) DEFAULT '{{missing}}' NOT NULL
CHECK (foobar = RTRIM(LTRIM(foobar))),
..);

This will make the UI people do something on their tier because the
database will kick it back to them.

[Gert-Jan Strik]

You can change the constraint to:
CHECK (DATALENGTH(foobar) = DATALENGTH(RTRIM(LTRIM(foobar))))

HTH,
Gert-Jan

Juniorlinn CC wrote:

Hi CELKO - The table was already in place with many many rows, so I
created that exact Constraint you exposed here using the 'Manage
Constraints' graphical tool in EM, but it didn't work. If UI sends blank
spaces after the string (coming from web-forms), they will not be
filtered off by this constraint. Any suggestions?

txn
Jrlin

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

--
(Please reply only to the newsgroup)

[Juniorlinn CC]

Very well noticed Hugo, blessings for pointing missing parts in the
strip of code I've shared here and exposing a more elegant way of
writing it, sorry for switching the table names at posting time, I had
to modify it a bit for simplicity before posting and I had a little
accident renaming the tables, promise to pay more attention next time.
Although I was aware of not being able to use recursive triggers in this
scenario, this code was appropriated for the particular problem I needed
to address and it worked very well for single row updates and inserts,
otherwise I would use Cursors. Thanks again for sharing your marvels,
much appreciated.

;)
Jrlin

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Juniorlinn CC]

Hi CELKO - The table was already in place with many many rows, so I
created that exact Constraint you exposed here using the 'Manage
Constraints' graphical tool in EM, but it didn't work. If UI sends blank
spaces after the string (coming from web-forms), they will not be
filtered off by this constraint. Any suggestions?

txn
Jrlin

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Gert-Jan Strik]

You can change the constraint to:
CHECK (DATALENGTH(foobar) = DATALENGTH(RTRIM(LTRIM(foobar))))

HTH,
Gert-Jan

Juniorlinn CC wrote:

Hi CELKO - The table was already in place with many many rows, so I
created that exact Constraint you exposed here using the 'Manage
Constraints' graphical tool in EM, but it didn't work. If UI sends blank
spaces after the string (coming from web-forms), they will not be
filtered off by this constraint. Any suggestions?

txn
Jrlin

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

--
(Please reply only to the newsgroup)