CODEWITHSUNDEEP
sqlsql-server
Pandarian Ld
Pandarian Ld

Reputation: 767

Set primary key with the given prefix and auto-incremental to database

I am trying to set the primary key of the table in my database schema like 'A01406V000001'.
'014' means Year (2014)
'06' means Month (June)
'000001' is the auto-incremental number for each AXXXXX prefix.

*More explanation
My SQL Schema Here (SQL Fiddle)
There are two rows in the table.('A01405V00001' and 'A01406V00001')
I want to insert the new element with the AccidentID = 'A01406V000002', 'A01406V000003', 'A01406V000004',... for each time when given the prefix as 'A01406'

I think that I must create a Trigger, but I haven't learn about this.
I am searching about SQL Trigger but it looks complicated. I try something like

CREATE TRIGGER accident_trigger
BEFORE INSERT ON accident
FOR EACH ROW
BEGIN
INSERT INTO accident(Date, 
                     Time, 
                     Location, 
                     City, 
                     AssClaimNo, 
                     LitClaimNo, 
                     AssID, 
                     LitID, 
                     CLicenPlateNumber)
 VALUES ('20 June 2014', 
         '14:00', 
         'Bangkok, Thailand', 
         'Bangkok', 
         NULL, 
         NULL, 
         '20140700b', 
         '201407b00', 
         'กน5019');
SET NEW.AccidentID = CONCAT('A', 
                            Right(Cast(Year(GetDate()) as varchar(10)),2),
                            Right('0' + Cast(Month(GetDate()) as varchar(10)),2),
                            'V', 
                            LPAD(LAST_INSERT_ID(), 6,'0'));
END;

(I also try to get the year and month using the exist function in the SQL)
Of course, I does not work.

Thank you for all solution or suggestion.

Ps. I already looking on
Is there a way to insert an auto-incremental primary id with a prefix in mysql database?
How do I add a autoincrement primary key in sql server with nvarchar?

[EDIT]
I use SQL Server Instead of Apache Derby.

Upvotes: 0

Views: 1996

Answers (1)

Pandarian Ld
Pandarian Ld

Reputation: 767

My solution is:

DECLARE @ROWS INTEGER;
    DECLARE @PREFIX VARCHAR(4) = ?;
    BEGIN
        SET @ROWS = (SELECT COUNT(*)
        FROM LITIGANT
        WHERE LITID LIKE 'L'+@PREFIX+'%');
    END;

    SET @ROWS = @ROWS + 1;
    INSERT INTO LITIGANT (LITID, LNAME, LEMAIL, LTELNUMBER, LDRIVERLICENSE, LCARBRAND, LCARCOLOR, LCARLICENPLATE, LINSNAME, LINSNUMBER)
        VALUES('L' + @PREFIX + RIGHT('000'+CAST(@ROWS AS VARCHAR(4)),4),
            ?, ?, ?, ?, ?, ?, ?, ?, ?);

This statements produce L14070001 when given the prefix = 1407

**This solution working on SQL Server.

Upvotes: 0

Related Questions