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

Question

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.

Why do you need a trigger? Why not have your Java program build the next Accident ID itself? Keep a table of accident IDs for each accident ID prefix, then select the current value, add one to it, store that back, and use the value to format the new AccidentID according to your conventions. — Bryan Pendleton, Jul 04 '14 at 14:38
Can I use the following statement in SQL Server to get the result instead of create a new table? `DECLARE @ROWS INTEGER; BEGIN SET @ROWS = (SELECT COUNT(*) FROM accident WHERE ACCIDENTID LIKE 'A01405%'); END; SELECT @ROWS;` Where @ROWS will be converted to the format '000001', '000002', ... And can I replace _'A01405%'_ with _?_ so I can pass a value to the statement. @BryanPendleton — Pandarian Ld, Jul 09 '14 at 06:05

score 0 · Accepted Answer · answered Jul 15 '14 at 09:16

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.

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

1 Answers1