Trigger AutoNumber di SQL Server

Trigger AutoNumber di SQL Server

Berikut ini trigger buatan saya untuk membuat autonumber, mungkin belum sempurna 100%, tapi paling tidak sudah berjalan dalam 3 tahun terakhir

=== begin trigger ===
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[ti_Quotation]
ON [dbo].[Quotation]
after insert
AS
declare @xcounter nvarchar(100), @tahun nvarchar(2), @bulan nvarchar(2), @nomor int;
declare @curr_tahun nvarchar(2), @curr_bulan nvarchar(2);

declare xcount cursor for select counter,substring(counter,4,2), substring(counter,6,2),CONVERT(INT,substring(counter,8,5)) from AutoGen where table_name = ‘Quotation’;
declare curr_monthyear cursor for select replicate(‘0’,2-len(convert(varchar,MONTH(GETDATE()))))+convert(varchar,MONTH(GETDATE())) , substring(convert(varchar,YEAR(getdate())) ,3,2);

open xcount;
open curr_monthyear;

fetch next from xcount into @xcounter,@tahun,@bulan,@nomor;
fetch next from curr_monthyear into @curr_bulan, @curr_tahun;

/*formatnya: INV 10 10 00001*/
/* 123 45 67 89012*/
if @curr_bulan = @bulan
set @nomor = @nomor+1;
else if @curr_tahun = @tahun
set @nomor = 1;

if @curr_bulan=1 and @curr_tahun>@tahun
set @nomor=1;

set @xcounter = ‘QUO’[email protected][email protected]_bulan+replicate(‘0’,5-LEN(CONVERT(nvarchar,@nomor))) +CONVERT(nvarchar,@nomor);
update Quotation set NoQuotation = @xcounter from Quotation, inserted where Quotation.oid = inserted.oid;

/* update table autogen */
update AutoGen set counter = @xcounter where table_name = ‘Quotation’;
close xcount;
close curr_monthyear;

deallocate xcount;
deallocate curr_monthyear;
=== end tigger ===

Untuk struktur table Autonum, kurang lebih seperti ini

Disesuaikan sendiri ya..

selamat belajar

 

Leave a Reply

Your email address will not be published. Required fields are marked *