I have a table named tblTransaction

ID int (auto increment)
TransactionID nvarchar(50)
TransactionDate datetime
TransactionType nvarchar(50)

now, I want my TransactionID to be in the format of <mmddyy99999999 > or 'TransactionDat e + ID'. But the problem is that I need to reset my seed every 12AM so that my TransactionID the next day will be 0709110 then 0709111, and so...