SQL 2000, I want to limit data in a field - how?

Question

SQL 2000, I want to limit data in a field - how?

Pete T-366679

SSCrazy

Points: 2065
More actions
June 6, 2007 at 8:44 am

#119111

I'm connecting to a SQL 2000 database with SQL 2005 Mgmt Studio. I have a string field, that I don't want to allow commas in. This is a small table, and for now it is for internal use only, so there isn't even an interface for it outside of writing a query or opening the table in Manager.
I thought I could do this with an INSERT/UPDATE trigger, but I'm getting "The logical tables INSERTED and DELETED cannot be updated.". Are Contraints 2005 only? IF not, how do you use them, because I tried making one, but it didn't work, and doesn't show up when I refresh the Object Explorer. All I want to do is replace any occurrence of a comma in this field with a blank space, when it's updated or inserted. How do I do this?

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

You're probably better off either restricting the input in the application or replace commas with spaces before inserting or updating in a stored procedure. As an example of the latter, if the value to be inserted or updated is passed to a stored procedure as a parameter, you could use

insert into gctest

values (1,replace(string_parm,',', ' '))

where gctest is the table and string_parm is the string containing commas.

Greg

Pete T-366679 SSCrazy Points: 2065 More actions · Answer 2

Thanks for the input. I might try the stored proc route, or just making a note of it for now. Like I said, for the immediate future, this tbale doesn't have any sort of application interface, and is only going to be worked with either using SQL statements or directly through Enterprise Manager opening and editing the table. Thanks again!

Raul Undreiner SSCommitted Points: 1554 More actions · Answer 3

CREATE TABLE tmp_test (

recid smallint

IDENTITY(1,1)

PRIMARY KEY CLUSTERED,

mycolumn varchar(50) NOT NULL )

create trigger tmp_test_trigger on tmp_test

for insert as

declare @id int

declare @index smallint

select @id = recid, @index = CHARINDEX(',', mycolumn) from INSERTED

if @index > 0

update tmp_test set mycolumn = replace(mycolumn,',','') where recid = @id

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

So what happens when more than 1 row is inserted in the table?

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

--===== If you have a table that looks like this...
 CREATE TABLE yourtable
        (
        RowNum INT IDENTITY(1,1) PRIMARY KEY,
        SomeString VARCHAR(100) NULL
        )
GO
--===== Add a constraint that looks like this
  ALTER TABLE yourtable
    ADD CONSTRAINT CK_yourtable CHECK (NOT(SomeString LIKE '%,%'))

--===== And I guarantee you won't be able to insert comma's like this... even directly...
 INSERT INTO yourtable (SomeString) VALUES ('comma,in,here')

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Raul Undreiner SSCommitted Points: 1554 More actions · Answer 6

Raul Undreiner

SSCommitted

Points: 1554

June 6, 2007 at 6:07 pm

#711280

trigger will check for every row inserted

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

I meant with more than 1 row inserted at the same time.

Raul Undreiner SSCommitted Points: 1554 More actions · Answer 8

Trigger will work if the input is by record, however for bulk input then you might have to use REPLACE(MyValue,',','') in the SQL that stores the info.

kuehn Newbie Points: 1 More actions · Answer 9

Use a trigger for AFTER INSERT UPDATE

declare a cursor for

select [id] from inserted where [comma_in_clause]

and update while step trough cursor

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

How about you search how to make a trigger work on a set of rows. I'm sure that this come in handy someday!!

JacekO SSCertifiable Points: 6610 More actions · Answer 11

You can use two INSTED OF triggers to do it.

Using Jeff's table definition (Thanks):

CREATE TABLE yourtable

(

RowNum INT IDENTITY(1,1) PRIMARY KEY,

SomeString VARCHAR(100) NULL

)

CREATE TRIGGER T1

ON yourtable

INSTEAD OF INSERT

AS

INSERT INTO yourtable (SomeString)

SELECT REPLACE(SomeString, ',', ' ') FROM inserted

CREATE TRIGGER T2

ON yourtable

INSTEAD OF UPDATE

AS

UPDATE yourtable

SET SomeString = REPLACE(I.SomeString, ',', ' ')

FROM yourtable Y

INNER JOIN inserted I ON Y.RowNum = I.RowNum

The advantage of this solution over Jeff's is that I think it matches the requirements of replacing the comma with a space when a user enters a string that contains a comma instead of aborting the operation.

---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]

Pete T-366679 SSCrazy Points: 2065 More actions · Answer 12

Ahhhhh, intersting! Thank you, that looks like it might work!