prevent certain characters being inserted into table

Question

prevent certain characters being inserted into table

Siten0308

SSCarpal Tunnel

Points: 4404
More actions
May 14, 2021 at 11:02 pm
Go to Answer
#3883626

Hello,
So i am assigned a task, that if a user inserts a certain character from a list or is not in the proper naming convention, then don't insert or raise error, is the best way to go about doing this is a trigger, or is there something else I can use in SQL server 2014? this table will have a lot of reads and writes, but not sure if the trigger is the best way, or if there is something else I can use that any of you did and worked out? yes I wish I can have the app devs do it on the development side, but this is a vendor app and its not possible on there end :\

thanks in advance
Jeff Moden

SSC Guru

Points: 1004318
More actions
May 15, 2021 at 11:06 pm
Answer
#3883899

My apologies... I flat out forgot that this is a VENDOR supplied table. I absolutely agree with the others but will take it one step further... unless you get written permission from the VENDOR to make ANY changes to the table or anything that impacts the table or the process that populates the table, you're probably in violation of the support agreement whether written or implied (and it IS probably written).
Talk with the VENDOR about what you want to do and then get it in writing or have them make the change. Period.
--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)

Viewing 8 posts - 1 through 7 (of 7 total)

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

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 1

What are you saying you want done if an attempted entry contains one of the taboo characters? Do you want to silently reject the entire entry or just remove the taboo characters from the entry and then use the cleaned entry?

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

Siten0308 SSCarpal Tunnel Points: 4404 More actions · Answer 2

ohh sorry, yes if one of the taboo characters is in the string then reject the entire entry. should this or is it best to be done as a trigger or do you recommend something else?

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 3

The "best" way would be to have an "INSTEAD OF" trigger that checks the entry. You could also do it after the fact with a normal "after" trigger that would undo the insert if it were bad. The Instead Of trigger would be better because it would be programmed not to do the insert at all if such conditions existed.

I do wish MS had a true "Before" trigger (like Oracle does).

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

ScottPletcher SSC Guru Points: 101093 More actions · Answer 4

Best would be a CHECK constraint if that is possible. You wouldn't have to write the code and SQL would guarantee that it gets run and works without error (given, of course, that you've coded the CHECK correctly). If the values don't pass the CHECK, SQL itself will kill the INSERT (and UPDATE too, if you want; if you use triggers, you'd have to write separate trigger for UPDATE along with the one for INSTEAD OF INSERT).

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 5

I would be very careful about adding check constraints or triggers to a vendor supplied and supported table. If the vendor has signed off on you making this change, then great - but if not and you make a change that causes the application to fail in a way that is not expected by their code, it could cause a lot more problems than it resolves.

If the vendor is using a stored procedure to file the data - then that would be where I would look at fixing the problem. And in the stored procedure you can then validate the data, and also include a check constraint on the table - and know that it will be handled correctly.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 6

If the vendor doesn't want to allow changes. I'd build a process to "scrub" entries every minute. Index the column if you can and search for problem strings. Then decide what to do.