Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pbaldy
Starting Member
7 Posts |
 Posted - 2008-11-10 : 17:17:49
|
| I have an unusual business problem, trying to figure out the best way to solve it. This is for a vehicle maintenance database, whose tables include one for vehicles, one for service transactions (fields include vehicle, service code, date, mileage) and regular service intervals (like 4000 miles for oil changes) with fields for vehicle, service code and interval. My problem is that the boss now has a particular type of vehicle for which the interval will change; the first couple at 2,500 mile intervals, then one at a 5,000 mile interval, then 15,000. Basically we're involved in a test with the manufacturer.My initial thinking was to create a trigger that would look for inserts to the service transactions table with the appropriate service code, test if the vehicle was in the test, check where that vehicle was in the process, and then set the interval in the intervals table appropriately.Does that sound like an appropriate use of a trigger, or am I better off doing it in the front end (which is Access if relevant). I'm pretty strong in Access, so-so in SQL Server. One problem I had was that inserts to the service transactions table can be done in multiples, so I would need to pick out the relevant one. In other words, the front end will insert with SQL:INSERT INTO ...SELECT...which could return several records. For instance, if they did an oil change, transmission service and a brake job, 3 records would be inserted. I'm only concerned with the transmission service. I know enough to know that the trigger would need to be written to handle multiple inserts, not enough to know how.Paul |
|
|
hanbingl
Aged Yak Warrior
652 Posts |
Posted - 2008-11-10 : 17:41:08
|
Modify your service Intervals table to include limits.Vehicle ServiceCode Interval Limit-------- ------------- --------- --------V1 Oil 4000 0V1 Break 10000 0V1 Xmission 5000 0V2 Oil 4000 0V2 Break 10000 0V2 Xmission 2500 5000V2 Xmission 5000 15000V2 Xmission 15000 0 Now you can use your existing code and add some additional logic to your trigger. |
 |
|
|
pbaldy
Starting Member
7 Posts |
Posted - 2008-11-10 : 18:01:30
|
| Thanks; that's an interesting idea; I'll have to ponder it. It means I don't need this trigger or anything, but complicates my existing queries that tell the users what vehicles are due for service based on their current odometer reading and the interval in the interval table.Paul |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-11-10 : 20:42:19
|
| I'd agree with hanbing.This sort of logic should not be hard-coded. It should be in a table.If it is not practically useful, then it is practically useless. |
|
|
|
|
|
|
|
|
Trigger or ?