Triggers
-
Hi,
I have a table simple nothing to complicated
CREATE TABLE [dbo].[RHH] (
[UNIQUE_ID] varchar(50) NULL,
[First_Name] varchar(50) NULL,
[Last_Name] nvarchar(50) NULL,
nvarchar(50) NULL)
ON [PRIMARY]
with this table all the data gets inserted via an application except the UNIQUE_ID. This I am looking to increment as the data is inserted. Also it has to be in the format of say RHH100100 +1.
Im thinking an on insert trigger to update the column but not sure how to get the last value and increment that. ?
Hope this helps...
Ford Fairlane
Rock and Roll Detective
-
I first thought on this is that you are about to create a serious bottleneck in your system - at best you will experience a little bit of blocking. At worst (and I have seen it this in action), you will end up with a system that has extremely poor performance and constant deadlocks.
Also, the trigger should have situations where multiple rows are inserted at the same time (e.g. INSERT INTO dbo.RHH SELECT * from anothertable) which will add complexity.
How important is the numeric portion of your unique_id ? Is it enough for it to be generation using a sql server IDENTITY column ? If it is then, you could easily do what you need using and IDENTITY column and, if you need to show the value with the "RHH" prefix, you could also have a computed column.
The following would achieve this
CREATE TABLE [dbo].[RHH] (
[UNIQUE_ID] AS 'RHH' + RIGHT ('00000000' + CONVERT (VARCHAR, MyIdentity), 10),
[First_Name] varchar(50) NULL,
[Last_Name] nvarchar(50) NULL,
nvarchar(50) NULL,
MyIdentity INT IDENTITY (1, 1)
)
ON [PRIMARY]
-
Certainly makes sense I will try it out and let you know.
Appreciate the idea.
Hope this helps...
Ford Fairlane
Rock and Roll Detective -
happycat59 (10/14/2013)
I first thought on this is that you are about to create a serious bottleneck in your system - at best you will experience a little bit of blocking. At worst (and I have seen it this in action), you will end up with a system that has extremely poor performance and constant deadlocks.Also, the trigger should have situations where multiple rows are inserted at the same time (e.g. INSERT INTO dbo.RHH SELECT * from anothertable) which will add complexity.
How important is the numeric portion of your unique_id ? Is it enough for it to be generation using a sql server IDENTITY column ? If it is then, you could easily do what you need using and IDENTITY column and, if you need to show the value with the "RHH" prefix, you could also have a computed column.
The following would achieve this
CREATE TABLE [dbo].[RHH] (
[UNIQUE_ID] AS 'RHH' + RIGHT ('00000000' + CONVERT (VARCHAR, MyIdentity), 10),
[First_Name] varchar(50) NULL,
[Last_Name] nvarchar(50) NULL,
nvarchar(50) NULL,
MyIdentity INT IDENTITY (1, 1)
)
ON [PRIMARY]
happycat - with a tiny modification to suit my needs this worked a real charm thanks for you assistance !!!!
Hope this helps...
Ford Fairlane
Rock and Roll Detective -
No probs, glad to help out (and to have you avoid a pretty serious problem)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply