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

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)

>> 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.

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!

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!

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)