sql server - Trigger to auto-increment ID based on certain criteria in TSQL -
i write trigger when new record comes in, if columns of contains same content previous record, 1 particular id column existing entries increase 1 , new entry 0, , if there no existing record, new record automatically assigned have id 0 in column.
for example, existing data this:
firstname lastname invoiceid amt ----------------------------------- john doe 1 $5 bill jane 0 $6 john doe 0 $3 now want insert john doe $3.5 of invoice, record automatically becomes:
firstname lastname invoiceid amt ----------------------------------- john doe 1 $5 bill jane 0 $6 john doe 2 $3 john doe 0 $3.5 now if need insert new, jane smith $2, become:
firstname lastname invoiceid amt -------------------------------------- john doe 1 $5 bill jane 0 $6 john doe 2 $3 john doe 0 $3.5 jane smith 0 $2 may know how should write such trigger in t-sql?
thanks!
here's do, rather having trigger:
create table t (tid int identity(1,1) not null,firstname varchar(10) not null,lastname varchar(10) not null,amt decimal(18,4) not null) go create view v select firstname, lastname, row_number() on (partition firstname,lastname order tid desc) - 1 invoiceid, amt t go and set initial data:
insert t(firstname,lastname,amt) values ('john','doe',5), ('bill','jane',6) go insert t(firstname,lastname,amt) values ('john','doe',3) and new john doe row:
insert t(firstname,lastname,amt) values ('john','doe',3.5) and jane smith row:
insert t(firstname,lastname,amt) values ('jane','smith',2) and select view:
firstname lastname invoiceid amt ---------- ---------- -------------------- --------------------------------------- bill jane 0 6.0000 jane smith 0 2.0000 john doe 0 3.5000 john doe 1 3.0000 john doe 2 5.0000 and have no concern invoiceid might incorrect because never store it.
select * v
Comments
Post a Comment