How to generate unique code manually based on previous value using triggers in spring boot

user_16265684 0
Viewed 59

I want to generate a unique code manually, using a trigger in spring boot, so every time the new recorded is inserted into that table, I want to generate this unique code based on the last value inserted

for example consider: column_name Subject_code value A001

so next time when any new values are inserted I should set this Subject code manually as A002 next time A003..... so on

how can I achieve this in spring boot...

spring-boot triggers sql
1 Answers

I have generated 10 digits unique code, If your table size is too big 4 digits might repeat data. You can obviously tweak the size according to your needs.

--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
user_101174330
Related