473,902 Members | 3,407 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

SQL Trigger for trimming String fields

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(@TR IMMEDFIELD))
WHERE ID = @MYID
END
Jul 20 '05 #1
6 11478
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(MyS tringField))
WHERE ID IN (SELECT ID FROM inserted)
END
Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 20 '05 #2
>> 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(foo bar))),
..);

This will make the UI people do something on their tier because the
database will kick it back to them.
Jul 20 '05 #3
You can change the constraint to:
CHECK (DATALENGTH(foo bar) = DATALENGTH(RTRI M(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)
Jul 20 '05 #4
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!
Jul 20 '05 #5
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!
Jul 20 '05 #6
You can change the constraint to:
CHECK (DATALENGTH(foo bar) = DATALENGTH(RTRI M(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)
Jul 20 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
4203
by: Trevor Fairchild | last post by:
I am trying to create a very minimal auditing system for a series of databases. I am in the process of writing Update triggers for 5 Tablse. I will write a trigger for each table-the trigger's function will be to INSERT a row into my MasterChanges table everytime ANY data is changed in each of the 5 tables. I have set up MasterChanges to capture the following: what Table the change was made in
4
3477
by: Juniorlinn | last post by:
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. ;)...
9
3472
by: Martin | last post by:
Hello, I'm new with triggers and I can not find any good example on how to do the following: I have two tables WO and PM with the following fields: WO.WONUM, VARCHAR(10) WO.PMNUM, VARCHAR(10) WO.PROBLEMCODE, VARCHAR(8)
3
7292
by: takilroy | last post by:
Hi, Does anyone know of a simple way to do this? I want to create an insert trigger for a table and if the record already exists based on some criteria, I want to update the table with the values that are passed in via the insert trigger without having to use all the 'set' statements for each field (so if we add fields in the future I won't have to update the trigger). In other words, I want the trigger code to look something like...
4
2140
by: Jules Alberts | last post by:
Hello everyone, I'm working on a tiny trigger function that needs to ensure that all values entered in a field are lowercase'd. I can't use pl/pgsql because I have a dozen different columns (with different names) that need a trigger that does this and pl'pgsql can't expand variable names to fieldnames. Writing a dozen functions (one per columnname) is /way/ too blunt so I tried pl/tcl (which I don't know): ...
16
2154
by: Josué Maldonado | last post by:
Hello list, The TCL trigger that uses NEW and OLD arrays failed after after I removed a unused column, now I got this error: pltcl: Cache lookup for attribute '........pg.dropped.24........' type 0 failed I already did a vacuum, but the error remain. Any idea how to fix/avoid that?
0
2485
by: JohnO | last post by:
Thanks to Serge and MarkB for recent tips and suggestions. Ive rolled together a few stored procedures to assist with creating audit triggers automagically. Hope someone finds this as useful as I've found it educational. Note: - I build this for use in a JDEdwards OneWorld environment. I'm not sure how generic others find it but it should be fairly generic. - I use a C stored procedure GETJOBNAME to get some extra audit data,
6
4452
by: Oliver | last post by:
I'm fairly new to DB2. I have been assigned to build a delete trigger that finds the data type of each of the table's fields so that the trigger can then build a string consisting of OLD values pre-wrapped in quote marks as needed. The deleted record's field values, all strung together as a single string, would then be inserted into a single archiving table (an architecture I inherited and cannot change). I've got the trigger doing...
3
7359
by: lenygold via DBMonster.com | last post by:
Thank you very much SERGE for your help. I found example in Graeme Birchall COOKBOOK wich i think exactly what i need for SQL check in triggers: • User query joins to table function - sends DML or DDL statement to be executed. • Table function calls stored procedure - sends statement to be executed. • Stored procedure executes statement. • Stored procedure returns SQLCODE of statement to the table function.
2
1400
by: somacore | last post by:
Hello all, I am trying to implement a trigger on a Microsoft SQL Server 2000 database. I have created the trigger, and it works mostly like it should, however I have a slight problem I can't seem to get around. We use two fields for this trigger. Both fields are 3 digit numbers (varchar(3) in the database, I have no control over that). The second field is used to store the value of the first field only if the first field has changed, and...
0
9997
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
11279
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10872
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10499
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9675
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
8047
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7205
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5893
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
4307
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.