[ACFUG Discuss] Database incremental Counter
I'm just looking for ideas when creating a counter in a SQL server database. I have a need for a counter that stores an integer value and increments the counter each time it is used. My inclination is to create a table with one record with one integer field that is just retrieved, incremented, and updated each time I need a new number. It seems sort of kludgey, and I would love to hear any other implementations anyone may have used, or can dream up. Also, if anyone can anticipate any issues with this method, I would love to hear them. I have no need for keeping any records of the numbers used. Paul Morton - Annual Sponsor FigLeaf Software - http://www.figleaf.com To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com -
Re: [ACFUG Discuss] Database incremental Counter
Barring some other requirement that dictates otherwise, you'd be better off to keep the incrementing function on the database. Use an insert trigger on the table to update the row or make the field autoincrementing. Howard On Wed, Mar 12, 2008 at 12:45 PM, Paul Morton [EMAIL PROTECTED] wrote: I'm just looking for ideas when creating a counter in a SQL server database. I have a need for a counter that stores an integer value and increments the counter each time it is used. My inclination is to create a table with one record with one integer field that is just retrieved, incremented, and updated each time I need a new number. It seems sort of kludgey, and I would love to hear any other implementations anyone may have used, or can dream up. Also, if anyone can anticipate any issues with this method, I would love to hear them. I have no need for keeping any records of the numbers used. *Paul Morton* - Annual Sponsor - Figleaf Software http://www.figleaf.com To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink http://www.fusionlink.com - -- Howard Fore, [EMAIL PROTECTED] The universe tends toward maximum irony. Don't push it. - Jeff Atwood - Annual Sponsor FigLeaf Software - http://www.figleaf.com To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com -
RE: [ACFUG Discuss] Database incremental Counter
Howard, Thanks. I like the idea of doing it with a trigger. Paul _ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Howard Fore Sent: Wednesday, March 12, 2008 11:02 AM To: discussion@acfug.org Subject: Re: [ACFUG Discuss] Database incremental Counter Barring some other requirement that dictates otherwise, you'd be better off to keep the incrementing function on the database. Use an insert trigger on the table to update the row or make the field autoincrementing. Howard On Wed, Mar 12, 2008 at 12:45 PM, Paul Morton [EMAIL PROTECTED] wrote: I'm just looking for ideas when creating a counter in a SQL server database. I have a need for a counter that stores an integer value and increments the counter each time it is used. My inclination is to create a table with one record with one integer field that is just retrieved, incremented, and updated each time I need a new number. It seems sort of kludgey, and I would love to hear any other implementations anyone may have used, or can dream up. Also, if anyone can anticipate any issues with this method, I would love to hear them. I have no need for keeping any records of the numbers used. Paul Morton - Annual Sponsor - Figleaf http://www.figleaf.com Software To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink http://www.fusionlink.com - -- Howard Fore, [EMAIL PROTECTED] The universe tends toward maximum irony. Don't push it. - Jeff Atwood - Annual Sponsor - Figleaf Software http://www.figleaf.com To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink http://www.fusionlink.com - - Annual Sponsor FigLeaf Software - http://www.figleaf.com To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com -
RE: [ACFUG Discuss] Database incremental Counter
Hi Paul: There are a few functional ways you could do this; however, for the sake of simplicity and quick implementation, I would consider using an IDENTITY column with an INT or NUMERIC data type. That will function just like the auto num column for primary keys in an Access database. You could also implement a trigger that will increment the number in the specified column in the event of an insert and decrement it in the event of a deletion. From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Paul Morton Sent: Wednesday, March 12, 2008 12:46 PM To: discussion@acfug.org Subject: [ACFUG Discuss] Database incremental Counter I'm just looking for ideas when creating a counter in a SQL server database. I have a need for a counter that stores an integer value and increments the counter each time it is used. My inclination is to create a table with one record with one integer field that is just retrieved, incremented, and updated each time I need a new number. It seems sort of kludgey, and I would love to hear any other implementations anyone may have used, or can dream up. Also, if anyone can anticipate any issues with this method, I would love to hear them. I have no need for keeping any records of the numbers used. Paul Morton - Annual Sponsor - Figleaf Software http://www.figleaf.com To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink http://www.fusionlink.com - - Annual Sponsor FigLeaf Software - http://www.figleaf.com To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com -
RE: [ACFUG Discuss] Database incremental Counter
Thanks Greg, I'm not looking to create a table full of records, which is what is required to use the identity column. I just want a number that is incremented when used, keeping the table limited to one record. I currently have it working, but I run a select to retrieve the value, then an update to increment the value. I wish I could write a trigger on a select. I would like to do it in one step. It's just an exercise to eliminate a database action. Hope this makes sense. Paul Paul Morton Morton International Consulting, Inc. p: 406 459-5634 f: 406 449-3151 w: www.mortonint.com _ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of McTure, Greg Sent: Wednesday, March 12, 2008 4:12 PM To: discussion@acfug.org Subject: RE: [ACFUG Discuss] Database incremental Counter Hi Paul: There are a few functional ways you could do this; however, for the sake of simplicity and quick implementation, I would consider using an IDENTITY column with an INT or NUMERIC data type. That will function just like the auto num column for primary keys in an Access database. You could also implement a trigger that will increment the number in the specified column in the event of an insert and decrement it in the event of a deletion. _ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Paul Morton Sent: Wednesday, March 12, 2008 12:46 PM To: discussion@acfug.org Subject: [ACFUG Discuss] Database incremental Counter I'm just looking for ideas when creating a counter in a SQL server database. I have a need for a counter that stores an integer value and increments the counter each time it is used. My inclination is to create a table with one record with one integer field that is just retrieved, incremented, and updated each time I need a new number. It seems sort of kludgey, and I would love to hear any other implementations anyone may have used, or can dream up. Also, if anyone can anticipate any issues with this method, I would love to hear them. I have no need for keeping any records of the numbers used. Paul Morton - Annual Sponsor - Figleaf Software http://www.figleaf.com To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink http://www.fusionlink.com - - Annual Sponsor - Figleaf Software http://www.figleaf.com To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink http://www.fusionlink.com - - Annual Sponsor FigLeaf Software - http://www.figleaf.com To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com -
RE: [ACFUG Discuss] Database incremental Counter
Couldn't you create a small stored procedure that will actually run the select and subsequently increment the number? Therefore you simply can the stored procedure which in turn will return the record(s) you want and also increment the value. From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Paul Morton Sent: Wednesday, March 12, 2008 6:38 PM To: discussion@acfug.org Subject: RE: [ACFUG Discuss] Database incremental Counter Thanks Greg, I'm not looking to create a table full of records, which is what is required to use the identity column. I just want a number that is incremented when used, keeping the table limited to one record. I currently have it working, but I run a select to retrieve the value, then an update to increment the value. I wish I could write a trigger on a select. I would like to do it in one step. It's just an exercise to eliminate a database action. Hope this makes sense. Paul Paul Morton Morton International Consulting, Inc. p: 406 459-5634 f: 406 449-3151 w: www.mortonint.com From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of McTure, Greg Sent: Wednesday, March 12, 2008 4:12 PM To: discussion@acfug.org Subject: RE: [ACFUG Discuss] Database incremental Counter Hi Paul: There are a few functional ways you could do this; however, for the sake of simplicity and quick implementation, I would consider using an IDENTITY column with an INT or NUMERIC data type. That will function just like the auto num column for primary keys in an Access database. You could also implement a trigger that will increment the number in the specified column in the event of an insert and decrement it in the event of a deletion. From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Paul Morton Sent: Wednesday, March 12, 2008 12:46 PM To: discussion@acfug.org Subject: [ACFUG Discuss] Database incremental Counter I'm just looking for ideas when creating a counter in a SQL server database. I have a need for a counter that stores an integer value and increments the counter each time it is used. My inclination is to create a table with one record with one integer field that is just retrieved, incremented, and updated each time I need a new number. It seems sort of kludgey, and I would love to hear any other implementations anyone may have used, or can dream up. Also, if anyone can anticipate any issues with this method, I would love to hear them. I have no need for keeping any records of the numbers used. Paul Morton - Annual Sponsor - Figleaf Software http://www.figleaf.com To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink http://www.fusionlink.com - - Annual Sponsor - Figleaf Software http://www.figleaf.com To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink http://www.fusionlink.com - - Annual Sponsor - Figleaf Software http://www.figleaf.com To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink http://www.fusionlink.com - - Annual Sponsor FigLeaf Software - http://www.figleaf.com To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com -
RE: [ACFUG Discuss] Database incremental Counter
That should work. I'll try that. Thanks. Paul Paul Morton Morton International Consulting, Inc. p: 406 459-5634 f: 406 449-3151 w: www.mortonint.com _ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of McTure, Greg Sent: Wednesday, March 12, 2008 4:42 PM To: discussion@acfug.org Subject: RE: [ACFUG Discuss] Database incremental Counter Couldn't you create a small stored procedure that will actually run the select and subsequently increment the number? Therefore you simply can the stored procedure which in turn will return the record(s) you want and also increment the value. _ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Paul Morton Sent: Wednesday, March 12, 2008 6:38 PM To: discussion@acfug.org Subject: RE: [ACFUG Discuss] Database incremental Counter Thanks Greg, I'm not looking to create a table full of records, which is what is required to use the identity column. I just want a number that is incremented when used, keeping the table limited to one record. I currently have it working, but I run a select to retrieve the value, then an update to increment the value. I wish I could write a trigger on a select. I would like to do it in one step. It's just an exercise to eliminate a database action. Hope this makes sense. Paul Paul Morton Morton International Consulting, Inc. p: 406 459-5634 f: 406 449-3151 w: www.mortonint.com _ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of McTure, Greg Sent: Wednesday, March 12, 2008 4:12 PM To: discussion@acfug.org Subject: RE: [ACFUG Discuss] Database incremental Counter Hi Paul: There are a few functional ways you could do this; however, for the sake of simplicity and quick implementation, I would consider using an IDENTITY column with an INT or NUMERIC data type. That will function just like the auto num column for primary keys in an Access database. You could also implement a trigger that will increment the number in the specified column in the event of an insert and decrement it in the event of a deletion. _ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Paul Morton Sent: Wednesday, March 12, 2008 12:46 PM To: discussion@acfug.org Subject: [ACFUG Discuss] Database incremental Counter I'm just looking for ideas when creating a counter in a SQL server database. I have a need for a counter that stores an integer value and increments the counter each time it is used. My inclination is to create a table with one record with one integer field that is just retrieved, incremented, and updated each time I need a new number. It seems sort of kludgey, and I would love to hear any other implementations anyone may have used, or can dream up. Also, if anyone can anticipate any issues with this method, I would love to hear them. I have no need for keeping any records of the numbers used. Paul Morton - Annual Sponsor - Figleaf Software http://www.figleaf.com To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink http://www.fusionlink.com - - Annual Sponsor - Figleaf Software http://www.figleaf.com To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink http://www.fusionlink.com - - Annual Sponsor - Figleaf Software http://www.figleaf.com To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink http://www.fusionlink.com - - Annual Sponsor - Figleaf Software http://www.figleaf.com To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink http://www.fusionlink.com - - Annual Sponsor FigLeaf Software - http://www.figleaf.com To unsubscribe from this list, manage your profile @ http
