Updating tables with Triggers
-
I am relativley new to DB development, particularly MSSQL. So, at the risk of sounding like a total idiot, my question is, can I update a field in a table, based upon the actions performed on another table in the same database? Should I consider a Stored Proc for something like this?
It is a huge and complicated application and now the client is requesting an additional field in the DB. The information goes to anopther table in the db, but for thier reports, they are trying to extract that data from the table in question. They would prefer to avoid alot of "join". I would prefer not to navigate through this entire application changing "Insert" and "Update" commands if it is not necessary.
-
Why should they tell you how to code the app?
How long does it take to run the report with a join?
You should not denormalize the db because someone in management thinks it's best.. you must know it's best.
-
Yea, I know, or at least everything I have researched states that. I have been tosed to the wolves here and I am trying to fend them off.
I'll just have to evaluate and bite the bit on this one.
-
Who are the wolves?
-
Aah Remi - you speak like someone who's always been given a free hand....just consider yourself lucky!
Danny - why don't you post the DDL of the "table in question" & the "other table" and an example of what will be updated when ?!
There's bound to be (almost always) someone who'll come up with a simple solution and help you keep those wolves at bay...
**ASCII stupid question, get a stupid ANSI !!!** -
Sorry but if the management (assuming untrained management here) forces you to do stuff like that, you're probabely better off starting to find a new job, or we'll be seeing a lot of you here (denormalized design are a pain in the @$$ to manage)
. If they are trained, then just prove them wrong. If they still refuse the see your point of view >> A quote from rudy komacsar
time for:
exec sp_update_resume
possibly time for:
exec sp_distribute_resume
-
I haven't always had a free hand, but when I proved everyone wrong around me (to the last argument), they gave it to me and never asked it back... best move of my career here.
-
> Why should they tell you how to code the app?
Why? WHY!!?????!!?????
<cool jets><calm down><breathe normally><check pulse>
It's because of rule 1 about databases: everybody thinks he's an expert. It would never occur to someone who is not a tax accountant to correct someone who is. It would never occur to someone who is not a criminal lawyer to question the courtroom tactics of someone who is. It would never occur to someone who is not a surgeon to coach a surgeon on the snazziest way to remove a hemorrhoid. But it occurs to everyone who outranks the DBA in the corporate hierarchy that they know how to design a table better than a DBA.
My favorite is when the head of some other department insists immediately on the most denormalized solution possible, "because I don't want the design to become too complicated." I actually haven't had this problem where I currently work -- well, not too much, anyway 🙂 -- but I had a former boss who always insisted on the "simplest" solution. Finally I hit on the correct incantation: "Look -- your proposed design does not alleviate complexity, it simply transplants complexity to the maintaining code and allows data consistency errors to creep in when that code itself is improperly maintained."
-
"It's because of rule 1 about databases: everybody thinks he's an expert."
You can blame MS Access for this one... It's nice to have wizards but it doesn't mean you can desgin squat.
"It would never occur to someone who is not a tax accountant to correct someone who is. It would never occur to someone who is not a criminal lawyer to question the courtroom tactics of someone who is. It would never occur to someone who is not a surgeon to coach a surgeon on the snazziest way to remove a hemorrhoid. But it occurs to everyone who outranks the DBA in the corporate hierarchy that they know how to design a table better than a DBA."
Been there... shoved it right in there face
."My favorite is when the head of some other department insists immediately on the most denormalized solution possible, "because I don't want the design to become too complicated." I actually haven't had this problem where I currently work -- well, not too much, anyway 🙂 -- but I had a former boss who always insisted on the "simplest" solution."
See previous answer
."Finally I hit on the correct incantation: "Look -- your proposed design does not alleviate complexity, it simply transplants complexity to the maintaining code and allows data consistency errors to creep in when that code itself is improperly maintained.""
Nice quote... can I print it and use it next time I get this "problem"
? -
> Nice quote... can I print it and use it next time I get this "problem"
Absolutely. My copyright has expired. 🙂
Usually, I also add: "The real complexity is in the relationships between these objects in the real world. We're not trying to add complexity, just to capture it. That complexity, in fact, is going to persist whether it's embedded in the table design, or in the code that executes against it. By modeling that complexity in the database design, you save programmers the trouble of modeling it for themselves everytime they try to fulfull one of your requests."
Sometimes that sinks in. Sometimes not. In over twenty years of this profession, I've experienced some Dilbert moments you wouldn't believe.
In reality, I usually mentally assess the added costs of doing things the way the customer wants, and will often silently assent to their requests/suggestions/demands if I judge that it's no big deal. It's not my job to lecture them -- they don't really want to understand what we do -- but it is my job to make sure that the job gets done right, or at least right enough. Look: usually, these are very smart people who know an awful lot about what *they* do for a living. I'm just here to help them; it's only that sometimes they're able to make helping them harder than it ought to be.
Architects, homebuilders, and lots of other professions actually do face similar circumstances with their customers -- again, things that the average person probably imagines he knows something about but really doesn't. A homebuilder in Omaha once explained that occasionally he gets a request from someone that requires that he do things the wrong way. Maybe it's the way a basement is poured or laid, maybe the way roofing or siding is put on, but the request goes against best practices. He said he will argue his point, but ultimately, if he doesn't convince them, he will just go ahead and do what he knows is right and take the flak for it.
Problem for us is it's probably easier to fire one of us than a homebuilder.
-
Lets hear about those : "Sometimes that sinks in. Sometimes not. In over twenty years of this profession, I've experienced some Dilbert moments you wouldn't believe."
-
Well, okay, you asked for it.
There was, e.g., the time my boss asked me into his office to discuss a new database we were designing for our customer. My boss, looking off in the distance, said, "We need two things from this database. Firstly, it must be absolutely flexible -- whatever the users decide to put in as data, goes."
So at this point, I'm taking notes. "Hmmm," I thought, "That means lots of VARCHAR columns, few if any constraints... okay."
The boss continued, "Secondly, you can't allow them to input anything that is wrong."
I blinked. I rubbed my eyes. I shook out my ears. I must have heard that wrong. So I asked, "How can I create a database that allows any input, but refuses to accept anything that is 'wrong'?"
The boss just waved his hand dismissively, "You're the engineer, you figure it out."
So I figured it out. When given an impossible task, do what you think you ought to do, not what they asked for. I pretty much allowed them to put in anything they wanted, but created lists of errors for them to review whenever they tried putting square pegs in round holes. It was a lot harder than just forcing the inputs to fit the mold to begin with, but it seemed to make the boss happy. And I got paid anyway.
-
What the hell kind of application was that??? I have one like that here and it's not pretty
. -
It was defense-related. Speaking from personal experience, defense contracting firms tend to have a corporate culture that enshrines cravenness, and this attitude trickles down to the techie level. Sometimes, I'm not sure "trickle down" is the correct characterization -- sometimes, it seems more like "aim and spray". For whatever reason, they seem to me to be more afraid than most firms not to do everything in the precise way the customer wants it, and are more willing to do things in a less optimum manner if doing so will put a smile on the federal government's face.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply