Personal documentation during development phase and still learning new technology
Trigger AutoNumber di SQL Server
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 *

This site uses Akismet to reduce spam. Learn how your comment data is processed.