December 29, 2011 at 5:15 pm
I'm fairly knowledgable with T-SQL, but I have a question before I attempt my next endeavor. We currently have an application that requires our team to enter the same information over and over again, on the backend it writes to different tables and different columns. For instance:
TableA.CustomerName
TableB.CustomerName
Our team would have to go into both sections through the front end GUI and enter "John Smith". I was thinking of doing a nightly SP with update logic, where we train our team to enter data only in TableA, and have the update copy the data over when TableA.CustomerName is not null.
Is this the right approach, I should I look at a trigger instead. Just looking for some insight from someone much more knowledgable than myself. For the record, never dealt with a trigger.
December 29, 2011 at 5:19 pm
bwagner 90365 (12/29/2011)
I'm fairly knowledgable with T-SQL, but I have a question before I attempt my next endeavor. We currently have an application that requires our team to enter the same information over and over again, on the backend it writes to different tables and different columns.
This sounds like a bad break in standard normalization methods, which is the data is entered once and then referred to from the correct locations. We'd need more information to help inform you if that's the case though, but it sounds like you're fighting bad design right from the gate.
However, a trigger would give you a real time 'copy' of the data, but would slow down every transaction that wrote the original information. That's something you'll have to decide if it's valuable or if you can handle a delay in data-upkeep via your other copying process.
I personally would be looking to overhaul the app from the ground up if I ran into this.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 30, 2011 at 5:38 am
Evil Kraig F (12/29/2011)
...This sounds like a bad break in standard normalization methods, which is the data is entered once and then referred to from the correct locations. We'd need more information to help inform you if that's the case though, but it sounds like you're fighting bad design right from the gate.
+1 for bad design
However, a trigger would give you a real time 'copy' of the data, but would slow down every transaction that wrote the original information. That's something you'll have to decide if it's valuable or if you can handle a delay in data-upkeep via your other copying process.
+1 for marking trigger approach as BAD
If you don't have control on DB design, I would suggest you to go with stored procedures or some ETL procedure (when database workload is less). It won’t be real-time but it won’t affect overall database performance.
December 30, 2011 at 6:24 am
Honestly, unless the relationships between the tables are hectic complex, or there's really convoluted logic, or you're updating customer names several times a second, I'd go for a trigger. Well written they are not performance problems and it prevents any stale data problems where a customer name has changed in tableA and the old value is still in Table B.
If you need help in writing a trigger (and this is a very simple one), please post the actual table definitions and some sample data.
If you go for an automated process, you'll need to account for situations during the day where the data is out of sync and hence incorrect (query 2 tables and get different names), you'll also have to write the overnight process to handle inserts and updates (and how do you tell when someone's broken the rules and corrected a name in B that has to be written to A)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply