Validation using triggers - SQL Server Forums

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

Transact-SQL (2000)

Validation using triggers

Author

Topic

anolis
Starting Member

14 Posts

Posted - 2004-05-16 : 13:26:42

Hi,

Imagine you've got a table with three fields: id, zippcode and place.

The zippcode can be anything, but if it contains 4 numbers + a space + 2 characters, I want the space automaticaly removed.
The code for this isn't difficult (a simple REPLACE), but where should I put the code?
If I use a Trigger with:
IF UPDATE(zippcode) BEGIN
-- the code here
END
It won't work, for obvious reasons.

Any suggestions?

Anolis,
Developer,
MCAD for .NET

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-05-16 : 14:25:06

why dont you do it directly in the insert statement?

and it probably wouldn't be a bad idea to have 2 separate columns [4 numbers], [2 chars].
it's easier on sorting in the user end application.

Go with the flow & have fun! Else fight the flow :)

anolis
Starting Member

14 Posts

Posted - 2004-05-16 : 14:48:53

... the database and applicationcode(asp) has been developped for 5 years by many different companies. The code and database are one big bunch of spaghetti! There's no way I can change the insert statements, nor can I split the field. I just need to remove that space afterwards.

Anolis,
Developer,
MCAD for .NET

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-16 : 15:34:50

Are you looking for something like this???

CREATE TABLE djl_test_update_trigger(
table_id INT IDENTITY(1,1) PRIMARY KEY,
zippcode VARCHAR(55))
GO

CREATE TRIGGER upd_ins_djl_test_update_trigger
ON djl_test_update_trigger
FOR UPDATE, INSERT

AS

UPDATE dtut
SET dtut.zippcode = REPLACE(dtut.zippcode,' ','')
FROM
djl_test_update_trigger dtut
INNER JOIN inserted i ON dtut.zippcode = i.zippcode
WHERE
i.zippcode LIKE '% %'

GO

INSERT djl_test_update_trigger(zippcode)
SELECT '6450776'
UNION ALL
SELECT '645077 882'

SELECT * FROM djl_test_update_trigger

UPDATE djl_test_update_trigger
SET zippcode = '6450 775'
WHERE zippcode = '6450776'

SELECT * FROM djl_test_update_trigger

DROP TABLE djl_test_update_trigger

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

anolis
Starting Member

14 Posts

Posted - 2004-05-16 : 16:51:20

Well, this only works when nested triggers are not allowed... which unfortunately is not the case.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-16 : 16:54:21

No, it works all the time actually. There will nothing to update after the initial trigger execution. That's why you have the inner join and WHERE i.zippcode LIKE '% %'.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

anolis
Starting Member

14 Posts

Posted - 2004-05-16 : 17:00:42

Hmm, I misjudged that!
Thanks, I will try this tomorrow ... and think about it (I've been busy too long now) ... it's bedtime overhere.

Anolis,
Developer,
MCAD for .NET

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources