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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-05-13 : 01:50:20
|
Derrick writes "I have the followinf scenario...OrderNo LineNo 1 1 2 1 3 1 3 2 3 3 4 1 When I create a new record i need it to check the previous record to see whether the OrderNois the same. If it is the same, the LineNo needs to be incremented by 1, if it is different LineNo needs to revert to 1." |
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-05-13 : 02:10:32
|
| Write a Trigger using this statementselect @lineno= select isnull(count(1),0)+1 from inserted iinner join tablename ton i.orderno=t.ordernoHTH-------------------------------------------------------------- |
 |
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2002-05-13 : 03:16:03
|
| Depending on the application, I might suggest putting this into the business logic layer. If you're doing some kind of a bulk insert, a trigger makes a lot of sense. However, if this is an ongoing thing, shouldn't the front end app know if it's a new line for an existing order?Nazim's solution totally works, and is really pretty clever. However, because it's a trigger on the table, it would preclude you manually entering order/line numbers in case of irregularities.It's tough to know without knowing more about the app, but I'm really not sure that a SQL trigger is the right layer for your logic here.Cheers-b |
|
|
|
|
|
|
Line Numbering