Defining a default based on an Identity column
-
Hi everybody,
I have a question which after two days of intensive research is still not solved so with your help... I hope it will be.
My problem is getting an identity column duplicated by default. Something like...
CREATE Table dbo.TestIdentity
(id int identity(1,1)
,id2 int not null default [DBO].[GetIdentity]('TestIdentity')
,sval varchar(50)
) on [primary]
CREATE FUNCTION [DBO].[GetIdentity] (@TABLENAME NVARCHAR(255))
RETURNS INT AS
BEGIN
RETURN IDENT_CURRENT(@TABLENAME)
END
This seems to work but not... Launching 12 connections and after 300000 insert I have about 4 [id]<>[id2] Which doesn't meet my need
Here is the script for those 12 processes...
declare @Counter int
Set @Counter=0
While @Counter< 50000
BEGIN
Insert into TestIdentity(sval) values (CAST(@Counter as varchar(50)))
Set @Counter=@Counter+1
END
Go
Although you may think that putting a transaction would do the trick it doesn't!?
declare @Counter int
Set @Counter=0
While @Counter< 50000
BEGIN
begin transaction
Insert into TestIdentity(sval) values (CAST(@Counter as varchar(50)))
commit transaction
Set @Counter=@Counter+1
END
Go
My problem is that I need to have Id2 Not Null, as a foreign key on [id] as it happens and updatable [No computed column therefore]
I also tried the use of trigger with no much chance as locks appear everywhere...
CREATE TRIGGER [tryone] ON [dbo].[TestIdentity]
AFTER INSERT
AS
Update [dbo].[TestIdentity] SET Id3=(Select id From Inserted)
GO
(Id3 int default(0) [that is without the foreign key initially] )
I also tried
CREATE TRIGGER [trytwo] ON [dbo].[TestIdentity]
FOR INSERT
AS
Update [dbo].[TestIdentity] SET Id4=(Select id From Inserted)
GO
None of it worked....
How can I resume my question better than HELP
Thanks to all in advance,
Sincerelly,
Sylvain
-
I would think a trigger is your best bet regardless of the locks. I do this in one of my databases. You have a logic problem in your trigger, try this
CREATE TRIGGER [tryone] ON [dbo].[TestIdentity]
AFTER INSERT
AS
UPDATE a
SET a.ID2 = a.[ID]
FROM [dbo].[TestIdentity] a
INNER JOIN Inserted i
ON i.[ID] = a.[ID]
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply