| Author |
Topic |
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
 Posted - 2006-06-28 : 17:35:35
|
| I have a issue where I have a table with a identity value (Col1) and then I also have in that same table a column that needs to default to the identity column + 1 (Col2) How would I do this so when I insert a row that the default value for Col2 would be Col1 +1. Please keep in mind It is very important that this is only for the default (i.e. when no Col2 value is present), if a Col2 value is present then I do not need the column set. Currently the only way I can think of doing this is with a trigger on insert, or add a update statement after my insert to do this using the "Scope_Identity()" function, but it seems like there should be a better way. Please keep in mind that this is going to be in a large db project, so the methodology needs to be foolproof.Thanks |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-06-28 : 19:26:49
|
| Can you explain why you need this?CODO ERGO SUM |
 |
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2006-06-28 : 21:18:41
|
| The real scenerio is much more complex then what I illustrated, I just phrased it in a simplified scenerio to get the point accross of what I want to accomplish. Basically I have 2 tables that holds a users info, one of which is a LIVE table, and the other of which is a Wrk Table. The wrk table generates the IDENTITY for the user which is used to referance that user throughout the application. All updates to a existing user, along with adding a new user are handled in the wrk table, and then upon saving the Wrk table then modifies the live table. If When somone wishes to modify a existing user, the record is then loaded into the wrk table. So please look below for the issue.WrkTable(IDCOLTmp(Identity),IDCOL)LiveTable(IDCOL)When I create a new record in wrktable, I want the default value for IDCOL to be the same as IDCOLTMP, This will solve my issue becuase when I insert from LIVETABLE.IDCOL into WRKTABLE.IDCOL, the IDCOL will be present, therefore the default value will not be used and that users UniqueID(IDCOL) will be loaded into the table. Also this will solve When I insert a new record into the WrkTable the IDCOL will use the default Identity insuring a unique ID. I hope this clears up what I want to do. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-06-28 : 21:30:35
|
| Why not just leave the IDCOL in the WrkTable null?You don't need it for a new row, because when you insert the row into LiveTable you will use WrkTable.IDCOLTmp for LiveTable.IDCOLCODO ERGO SUM |
|
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2006-06-28 : 22:29:16
|
| That will not work, there are other queries that referance that value while it is in the work table, and I don't want to need to re-write all the queries to check both columns. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-06-28 : 23:09:52
|
| If you have a lot of code based on having WrkTable.IDCOL populated, how did you get to this point without having the problem solved already?One other question. When you insert an existing row from LIVETABLE into WrkTable, you are using up the available ID range as the identity value gets incremented. Will this cause you problems down the road if you use up the available range of the ID datatype?CODO ERGO SUM |
|
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2006-06-28 : 23:38:03
|
| I appriciate the assistance, but please from this point just assume that I am competant enough to know what I need to accomplish in order to resolve the issue on hand, and that I am at this point due to many reasons where it would take a extremly long time to write, and serve little benefit. To answer your question in as condensed as I can, I originally had both the Live table and the Wrk table setup where each would create there own identity, for the purpose of explaining we'll call(TMPID,LIVEID). Please keep in mind each of these tables have many sub tables both live and wrk that depend on the unique key from that table. Only data that had been saved had a liveID, otherwise the value was null (Similiar to what you stated). Yesterday I decided I did not like that methodology due to many grey areas in coding where it was very easy to pull a duplicate record when joining the tables where not only the Main wrk table, but also all of the many subtables that used both live and tmp keys. I did accomplish this using both UNION Queries and some instances where I was able to get away with the use a "OR" operator on my table joins(Neither of which I liked). I changed all my code to ensure that when I was using a single query that I could referance a hard value regardless of whether record was just inserted, or previously inserted. For programming purposes this option looked much better.So I made all these changes late yesterday and early this morning, and due to mental fatigue, I overlooked a extremly important issue, that I could not insert into a identity field (I do not want to use the Identity_Insert ON, because with multiple tables and multiple users using various tables at any given time, it could cause many future headaches). So I decided to change the identity field to the IDCOLTMP and then create a second column called IDCOL that would somehow get that IDENTITY Value from IDCOLTMP as it's default value. Hense were I am now. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-06-28 : 23:52:55
|
| I am reposting this question, since you answered as I was changing my post.When you insert an existing row from LIVETABLE into WrkTable, you are using up the available ID range as the identity value gets incremented. Will this cause you problems down the road if you use up the available range of the ID datatype? I am guessing you will have many more updates than inserts.Getting back to your original question, if you have to populate that column, you are stuck using one of the methods you already mentioned. There may be some scalability issues with that, since will have to hold the transaction open longer for each insert. Also, I don't see a scalable method to make the column NOT NULL, somthing that will be required if there are foreign key references to this table. That is why I was trying to see if there was a way to not need it.CODO ERGO SUM |
|
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2006-06-29 : 00:02:02
|
| You bring up a good point, but the identity by default can increment to the max value of that field correct? If not I will need to change it to do so. I havn't tried this and I'm assuming that this will not work, but what if I changed the default value of the column to Scope_Identity(), the same as I would use getdate() as the default if I wanted to datestamp the column? I am pretty certain that will not work in that context, but it's worth throwing out there.Thanks for your assistance. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-06-29 : 00:12:35
|
| My point was that at some point you may use up the identity values available, if you have many more updates than inserts, because you would be using up an identity value each time you do an edit. Then you would really have problems.As for Scope_Identity() as a default, I've never tried it, and I doubt it will work. One test is worth a thousand words, so I'll leave that to you.CODO ERGO SUM |
|
|
|
|
Binding a column to another columns value