Avatar of dgrafx
dgrafxFlag for United States of America asked on

Sql Update & Insert Trigger

I'm looking for best practices advice and also code suggestions for the following:

I'm working on a web application that has SEVERAL insert and update points to the table in question. I don't want to go around and edit each contact point - then test each one - so I'm thinking that I'd like to use a trigger.

The goal is to remove single quotes from being inserted in 2 columns.

I need the trigger to fire on both insert and update.



Avatar of undefined
Last Comment
Scott Pletcher

8/22/2022 - Mon

From the viewpoint of data model design: Constraints prohibiting these are sufficient.

The database should not modify data after it is send from the application that way. When it must be in SQL, then you should use a stored procedure for INSERT and UPDATE on that table instead.

But it is better done in the front-end. Cause otherwise you must read the data after writing it to get possible changes for display. This is an unnecessary round trip.

Yes I know ste5an.
I am saying that there are hundreds of access points to this table for each insert and update.
If it was more feasible then yes I would handle it on the front end.

Thanks for your insight.


That's why you only need a CHECK constraint. Then any application can and must handle the domain correctly.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes

OH I see!! I didn't get what you said!!
Apologies ...

Scott Pletcher

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

I'm sorry Scott! I didn't even see your post!

Thanks Scott !!!
After working with it just a tad I have to say ste5an that when you say >>any application can and must handle the domain correctly<<
It would be nice if all applications were written logically - but they aren't ...
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Scott Pletcher

I posted a bit later, it took me a bit to ready the code, even though it's very easy and straightforward, since I am very busy at work.

You should do as much vetting on the front end / app as you reasonably can, but the db should still have the final controls for what rules the data must meet.  Iow, just because data is (supposedly) "fully" checked by the app, you still need to have a final verification in the db itself.

CHECK constraints are fine if the app code can trap and handle the error properly.  Most app code I've seen does not do that.  Besides, it's a huge amount of effort to write tons of error-handling code for things that come up only 1% (or whatever error rate you have) of the time.

If I would have written this app then there would be one insertion point - ya know one function that handled the inserts and updates - so that any changes or fixes could be handled in that one function. But this was all over the place!

I thank both of you for your time!!
And I agree with your statements Scott!

Yes it can be done in a trigger, some times there is no other way. But not when we talk about web apps.

Most app code I've seen does not do that.
Well, and that's why so many web apps are a hugh security issue. They must validate and sanitze input. This is not optional. See OWASP Input Validation Cheat Sheet.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
Scott Pletcher

Most app code I've seen does not do that.
That was in regard to apps properly handling CHECK constraints.  I have yet to see an app that fully does that.  

The CHECK constraint will lead to a failed insert / update, NOT data being automatically corrected by the app in response.  IF the app code "knew" how to correct that problem, it wouldn't have happened in the first place.  So saying "the app should handle the CHECK constraint" is really a non sequitur.