prevent certain characters being inserted into table

  • 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

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • 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?

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • 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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • 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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

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

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

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