Find out the max value of the field and add 1 to generate the new value.
debasisdas
Posting Genius
6,872 posts since Feb 2007
Reputation Points: 666
Solved Threads: 434
This is a bad idea. You should not create the record if you intend to delete or have a "quotes" table for storing data before it becomes a live invoice. If you create invoice #5 and then add a payment to invoice #5, then delete invoice #5 and recreate it, then your payment from the old invoice would automatically be associated to the new one. To answer your question you can reseed the table: DBCC CHECKIDENT('mytable', RESEED, 0) ; or enable IDENTITY_INSERT . This functionality requires dbo rights so it may be a security concern.
sknake
Industrious Poster
4,954 posts since Feb 2009
Reputation Points: 1,764
Solved Threads: 735
That is not the way to work with databases. If you want to avoid gaps then just follow post #2. But that is not going to fulfill your requirement of deleted records.
debasisdas
Posting Genius
6,872 posts since Feb 2007
Reputation Points: 666
Solved Threads: 434
The only way I know to enforce this would be to only permit single records inserts. If you you use an Insert Into .. Select From .. it would break the sequencing logic. You could also have a stored procedure that you fire off before you print invoices that updates the database with any null values to the current number in the sequence. Also what happens if you have invoice #1,2,3 .. then #2 gets deleted but you don't have another invoice by the end of the month so you only have #1,3? Would you have to change #3 to #2? Can you just generate these invoice numbers when you're doing your tax reports and use a different invoice just for this purpose? I have tried a number of different approaches and they all have issues.
Here is the only solid logic I have regarding your design to stop an invoice number from being changed in an update statement:
IF OBJECT_ID('dbo.InvoiceTbl', 'U') IS NOT NULL DROP TABLE InvoiceTbl
GO
Create Table InvoiceTbl
(
PK int identity(1000, 1) PRIMARY KEY,
InvNumber int UNIQUE,
CustName varchar(50)
)
GO
IF OBJECT_ID('tr_InvoiceTbl_Update', 'TR') IS NOT NULL DROP TRIGGER tr_InvoiceTbl_Update
GO
CREATE TRIGGER tr_InvoiceTbl_Update ON InvoiceTbl WITH EXECUTE AS CALLER FOR UPDATE AS
IF UPDATE(InvNumber)
BEGIN
--Stops changing of the invoice number after the record has been created
IF EXISTS
(
Select *
From Deleted FULL Join Inserted On (IsNull(Deleted.PK, -1) = IsNull(Inserted.PK, -2))
Where IsNull(Deleted.InvNumber, -1) <> IsNull(Inserted.InvNumber, -2)
)
BEGIN
RAISERROR ('cannot change invoice number', 16, 1)
ROLLBACK TRAN
RETURN
END
END
However if you need to run an update after the fact to set an invoice number that trigger will cause the update to roll back.
sknake
Industrious Poster
4,954 posts since Feb 2009
Reputation Points: 1,764
Solved Threads: 735
Try to use a procedure to insert the records into the database.
Try this sample oracle code.(convert to sql server code as per your requirement)
create or replace procedure find(eno number, flag out boolean)
is
a number(2);
begin
select count(*) into a from emp where empno=eno;
if a=0 then
flag:=FALSE;
else
flag:=TRUE;
end if;
end;
/
create or replace procedure ins_emp(a number)
is
eno emp.empno%type;
fg boolean;
begin
eno:=a;
<<abc>>
find(eno,fg);
if fg=true then
dbms_output.put_line(eno||' No already exists...');
eno := eno+1;
goto abc;
else
insert into emp(empno) values(eno);
dbms_output.put_line('New empno inserted is : '||eno);
end if;
end;
/
debasisdas
Posting Genius
6,872 posts since Feb 2007
Reputation Points: 666
Solved Threads: 434
