Click here to Skip to main content
14,981,861 members
Please Sign up or sign in to vote.
3.40/5 (2 votes)
Suppose i have a table called customer which is as follows
SQL
create table customer(
customerid int identity(1,1) primary key,
customername varchar(50),
emailid varchar(30) unique,
address varchar(max),
stateid int foreign key references state(stateid)
)

now i have a table called deletecustom which is like

SQL
create table deletecustom(
deletionid int identity(1,1) primary key,
customerid int 
)

i need to delete the entry in the customer table for the value inserted in the deletecustom table
so i use a trigger like
SQL
create trigger deletecustomer on deletecustom
for insert
as
declare @cid int;
select @cid=customerid from inserted
delete from customer where customer.customerid=@customerid

This deletes the records from the customer table. Is there a way wherein i can re use the deleted customerid field for creation of one more record? What i mean is if i delete a customer whose customerid is 1, can i use customer id=1 for some other customer just for proper utilization?
Posted
Updated 9-Oct-13 0:43am
v2

No, you cannot directly reuse an id that has just been removed. Identity field just acts as an incremental counter on which you do not have any control (except to reset it with the command TRUNCATE TABLE.
   
Comments
Rakshith Kumar 9-Oct-13 6:56am
   
even If u use truncate table u will not be able to reset . I tried it. It dint work.
phil.o 9-Oct-13 7:03am
   
TRUNCATE is to empty the table, not to reuse the ID.
My answer is clear on that : YOU CANNOT. Moreover, you cannot truncate a table if there are some foreign keys related to this table.
Rakshith Kumar 9-Oct-13 7:08am
   
Thank you Phil
phil.o 9-Oct-13 7:08am
   
You're welcome.
That's why you should not use Identity columns for Display values.

But you reset the Identity value(Reset Table Identity[^]) but you can't during when table has massive data. During this situation you could use the deleted Identity value(I mean you create a new record using the deleted Identity value). INSERT INTO SQL Server table with IDENTITY column[^]
But I won't recommend this way. Read the first line in my answer
   
Comments
Rakshith Kumar 9-Oct-13 23:41pm
   
thanxk you thatraja

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900