CODEWITHSUNDEEP
sql-server
mp1990
mp1990

Reputation: 10239

Database scheme, autoincrement

Database question here. Is it possible to make an autoincrement on a secondary or a thirtiary ID? I need to make something versionbased, so imagine this:

ID  Phrase  PhraseID    PhraseVersion
1   ""      1           1
2   ""      1           2
3   ""      1           3
4   ""      2           1

PhraseID can be the same number, when added to the database. If the PhraseID exists, i want PhraseVersion to autoincrement in number. If the PhraseID doesnt exist, i want PhraseVersion to start over, counting from 1.

I this possible?

Upvotes: 3

Views: 680

Answers (2)

Belladonna
Belladonna

Reputation: 308

This can be accomplished via an insert trigger on the table:

CREATE TABLE Phrases (
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY, 
    PhraseID INT NOT NULL DEFAULT(0), 
    PhraseVersion INT NOT NULL DEFAULT(0))
GO

-- ==========================================================================================
-- Author:         Donna Landy
-- Create Date:    21 Nov 2019
-- Purpose:        To populate the PhraseVersion column (subordinate numeric key) on Insert
-- Note:           Must cater for >1 row being inserted when this trigger is called
-- Strategy:       Construct a temp table with the rows we need to consider, then update one row at a time
-- ==========================================================================================

CREATE TRIGGER Phrases_Insert ON Phrases AFTER INSERT
AS
BEGIN

    DECLARE @ID INT
    DECLARE @PhraseID INT
    DECLARE @PhraseVersion INT

    DECLARE @i INT
    DECLARE @iMax INT

    -- Create and populate temp table
    IF OBJECT_ID('tempdb..#phrases', 'U') IS NOT NULL DROP TABLE #phrases
    CREATE TABLE #phrases (i INT IDENTITY(1,1) PRIMARY KEY, ID INT, PhraseID INT)
    INSERT INTO #phrases (ID, PhraseID) SELECT ID, PhraseID FROM inserted

    -- Scan temp table
    SET @i=1
    SELECT @iMax=MAX(i) FROM #phrases

    WHILE @i <= @iMax BEGIN

        -- Fetch PhraseID & PhraseVersion for the row we are going to update
        SELECT @ID=ID, @PhraseID=PhraseID FROM #phrases WHERE i=@i

        -- Find the highest current Ref
        SELECT @PhraseVersion=ISNULL(MAX(PhraseVersion),0) FROM Phrases WHERE PhraseID=@PhraseID

        -- Update the row 
        UPDATE Phrases SET PhraseVersion=@PhraseVersion+1 WHERE ID=@ID 

        -- Increment loop counter
        SET @i+=1

    END

    -- Remove temp table
    IF OBJECT_ID('tempdb..#phrases', 'U') IS NOT NULL DROP TABLE #phrases

END
GO

Upvotes: 0

Zohar Peled
Zohar Peled

Reputation: 82474

I would go with a computed column for PhraseVersion, that will take the count of rows with the same PhraseID and Id lower or equal to the current row.

To do that, you need to create a UDF to calculate the PhraseVersion:

CREATE FUNCTION dbo.GetPhraseVersion (
    @PhraseId int,
    @id int
)
RETURNS INT
AS
BEGIN
    RETURN (
        SELECT COUNT(*) 
        FROM T 
        WHERE PhraseId = @PhraseId 
        AND Id <= @id
    )
END
GO

Then, Create the table with the computed column:

CREATE TABLE T
(
    id int identity(1,1),
    PhraseId int,
    PhraseVersion as dbo.GetPhraseVersion(PhraseId, id)
)

GO

Now for the test - insert 4 records:

INSERT INTO T (PhraseId) VALUES(1),(1),(1),(2)

Select: 

SELECT *
FROM T

Results:

id  PhraseId    PhraseVersion
1   1           1
2   1           2
3   1           3
4   2           1

You can see a live demo on rextester.

Upvotes: 2

Related Questions