如何在spring引导中使用触发器根据先前的值手动生成唯一的代码

Question

我想手动生成一个唯一的代码，使用触发器在春季启动，每次新记录插入到表中，我想根据最后插入的值生成这个唯一的代码

的例子考虑:column_name Subject_code价值A001

所以下次插入任何新值时我应该手动设置这个主题代码为A002下次A003……所以在

我如何在春季启动中实现这一点…

Answer 1

我已经生成了10位数字的唯一代码，如果您的表大小太大，4位数字可能重复数据。显然，你可以根据自己的需要调整大小。

--Table Structure
CREATE TABLE triggerTable(
ID int NOT NULL,
uniqueCode varchar(50) NULL,
ColumnName varchar(50) NULL,
SubjectCode varchar(50) NULL,
)

--Trigger
ALTER TRIGGER UniqueNumberGenerator
ON triggerTable --table name
AFTER insert --work only when inserting data
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @maxId VARCHAR(max),@autoIncrement bigint,@hexa VARCHAR(50),@result VARCHAR(50),@prefix NVARCHAR(50), @currentId int, @prevUniqueCode varchar(50); 
SET @prefix = ''
--Dropping temp table
IF OBJECT_ID(N'tempdb..#tempTriggerTable') IS NOT NULL
DROP TABLE #tempTriggerTable
select @currentId = (SELECT ISNULL(MAX (ID),0) AS ID FROM triggerTable ) --Taking the current ID for updating only currently inserted data
print @currentId
--Finding the max ID for the purpose of finding the max unique code value from previous data
select @maxId =(SELECT ISNULL(MAX (ID),'0') AS ID FROM triggerTable where ID <> @currentId ) 
print  @maxId
SET @prevUniqueCode = (SELECT ISNULL(MAX (uniqueCode),'0') AS Code FROM triggerTable where ID = @maxId )
SET @autoIncrement=(CASE @prevUniqueCode WHEN '0' THEN 0 ELSE CONVERT(BIGINT,SUBSTRING(@prevUniqueCode,7,LEN(@prevUniqueCode)-5)) END)+1; --auto incrementing data
print  @autoIncrement
--Inserting to temptable for getting column name and subject code
CREATE TABLE #tempTriggerTable(
ColumnName nvarchar(50) NULL,
SubjectCode nvarchar(50) NULL
)

--Copy data into the temporary table
INSERT  INTO #tempTriggerTable
SELECT t.ColumnName, t.SubjectCode 
From triggerTable t where t.Id = @currentId

SET @prefix = (select ColumnName + SubjectCode from #tempTriggerTable);
print @autoIncrement
SET @hexa=CONVERT(VARCHAR(14),RIGHT('0000' + RTRIM(@autoIncrement), 4));  
print  @hexa
SET @result=@prefix+@hexa;      
SELECT @result;
--Finally updating the unique code column
UPDATE triggerTable 
SET uniqueCode = @result 
From triggerTable t where t.Id = @currentId
END