TSQL: Create A Custom Identity Column (Revision Database)

How would I create a Custom Trigger or Computed Column that works similar to an Identity?

I have two fields that need to be auto-incremented but in a particular order: ID and REV.

DRAWINGS
ID    | REV   | INFO
------+-------+------
1     | 0     | "Draw1"
2     | 0     | "Draw2"
2     | 1     | "Draw2Edit"
2     | 2     | "Draw2MoreEdit"
3     | 0     | "Draw3"
4     | 0     | "Draw4"

If I was to insert a few more records into my table such a:

INSERT INTO DRAWINGS (INFO) VALUES ("Draw5")
INSERT INTO DRAWINGS (ID,INFO) VALUES (3,"Draw3Edit")

My table would like:

DRAWINGS
ID    | REV   | INFO
------+-------+------
1     | 0     | "Draw1"
2     | 0     | "Draw2"
2     | 1     | "Draw2Edit"
2     | 2     | "Draw2MoreEdit"
3     | 0     | "Draw3"
3     | 1     | "Draw3Edit"      --NEW ROW
4     | 0     | "Draw4"
5     | 0     | "Draw5"          --NEW ROW

Pseudo Code

-- IF ID==NULL AND REV==NULL THEN IT IS A NEW DRAWING
IF INSERTED.ID IS NULL AND INSERTED.REV IS NULL 
   ID = SELECT MAX(ID)+1 FROM DRAWINGS
   REV = 0
   --INSERT HERE STATEMENT HERE
ELSE
   -- IF ID!=NULL AND REV==NULL THEN IT IS A NEW REVISION
   IF INSERTED.ID IS NOT NULL AND INSERTED.REV IS NULL
       -- EXTRA CHECK TO ENSURE DRAWING ACTUALLY EXISTS
       IF EXISTS(INSERTED.ID)
           REV = SELECT MAX(REV) + 1 FROM DRAWINGS WHERE ID = INSERTED.ID
           --INSERT HERE STATEMENT HERE
       ELSE
           --DRAWING DOES NOT ACTUALLY EXIST
           GOTO ERROR
   ELSE
       --REV IS NOT NULL (REVISIONS ARE A COMPUTED VALUE ERROR)
       GOTO ERROR

Note:

• I am using SQL Server 2000.
• The table is intended for inserts only.

I think I have it close to what I want:

DROP TABLE DRAW

GO

CREATE TABLE DRAW
(
    ID INT DEFAULT(0), 
    REV INT DEFAULT(-1), 
    INFO VARCHAR(10), 
    PRIMARY KEY(ID, REV)
)

GO

CREATE TRIGGER TRIG_DRAW ON DRAW
FOR INSERT
AS
BEGIN
    DECLARE @newId INT,
            @newRev INT,
            @insId INT,
            @insRev INT

    SET TRANS开发者_如何学PythonACTION ISOLATION LEVEL READ COMMITTED
    BEGIN TRANSACTION

    SELECT @insId = ID FROM inserted
    SELECT @insRev = REV FROM inserted

    PRINT 'BEGIN TRIG'
    PRINT @insId
    PRINT @insRev
    PRINT @newId
    PRINT @newRev


    --IF ID=0 THEN IT IS A NEW ID
    IF @insId <=0
    BEGIN
        --NEW DRAWING ID=MAX+1 AND REV=0
        SELECT @newId = COALESCE(MAX(ID), 0) + 1 FROM DRAW
        SELECT @newRev = 0
    END
    ELSE
    --ELSE IT IS A NEW REV
    BEGIN
        --CHECK TO ENSURE ID EXISTS
        IF EXISTS(SELECT * FROM DRAW WHERE ID=@insId AND REV=0)
        BEGIN
            PRINT 'EXISTS'
            SELECT @newId = @insId
            SELECT @newRev = MAX(REV) + 1 FROM DRAW WHERE ID=@insID
                                                            AND   REV=-1
        END
        ELSE
        --ID DOES NOT EXIST THEREFORE NO REVISION
        BEGIN
            RAISERROR 50000 'ID DOES NOT EXIST.'
            ROLLBACK TRANSACTION
            GOTO END_TRIG
        END
    END

    PRINT 'END TRIG'
    PRINT @insId
    PRINT @insRev
    PRINT @newId
    PRINT @newRev

    SELECT * FROM DRAW

    UPDATE DRAW SET ID=@newId, REV=@newRev WHERE ID=@insId



    COMMIT TRANSACTION
    END_TRIG:
END

GO


INSERT INTO DRAW (INFO) VALUES ('DRAW1')
INSERT INTO DRAW (INFO) VALUES ('DRAW2')
INSERT INTO DRAW (ID,INFO) VALUES (2,'DRAW2EDIT1') --PROBLEM HERE
INSERT INTO DRAW (ID,INFO) VALUES (2,'DRAW2EDIT2')
INSERT INTO DRAW (INFO) VALUES ('DRAW3')
INSERT INTO DRAW (INFO) VALUES ('DRAW4')

GO

--SHOULD THROW
INSERT INTO DRAW (ID,INFO) VALUES (9,'DRAW9')

GO

SELECT * FROM DRAW

GO

Use timestamps to get the inserts into the correct sequential order, and then assign the ordinal value procedurally, either in the SP or in the client program, when the rows are SELECTed.

DROP TABLE DRAW

GO

CREATE TABLE DRAW
(
    ID INT DEFAULT(0), 
    REV INT DEFAULT(-1), 
    INFO VARCHAR(10), 
    PRIMARY KEY(ID, REV)
)

GO

CREATE TRIGGER TRIG_DRAW ON DRAW
FOR INSERT
AS
BEGIN
    DECLARE @newId INT,
            @newRev INT,
            @insId INT,
            @insRev INT

    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    BEGIN TRANSACTION

    SELECT @insId = ID FROM inserted
    SELECT @insRev = REV FROM inserted

    --IF ID=0 THEN IT IS A NEW ID
    IF @insId <=0
    BEGIN
        --NEW DRAWING ID=MAX+1 AND REV=0
        SELECT @newId = COALESCE(MAX(ID), 0) + 1 FROM DRAW
        SELECT @newRev = 0
    END
    ELSE
    --ELSE IT IS A NEW REV
    BEGIN
        --CHECK TO ENSURE ID EXISTS (NOT COUNTING JUST INSERTED)
        IF (SELECT COUNT(*) FROM DRAW WHERE ID=@insId) > 1
        BEGIN
            SELECT @newId = @insId
            SELECT @newRev = MAX(REV) + 1 FROM DRAW WHERE ID=@insID
        END
        ELSE
        --ID DOES NOT EXIST THEREFORE NO REVISION
        BEGIN
            RAISERROR 50000 'ID DOES NOT EXIST.'
            ROLLBACK TRANSACTION
            GOTO END_TRIG
        END
    END

    UPDATE DRAW SET ID=@newId, REV=@newRev WHERE ID=@insId AND REV=-1

    COMMIT TRANSACTION
    END_TRIG:
END

GO


INSERT INTO DRAW (INFO) VALUES ('DRAW1')
INSERT INTO DRAW (INFO) VALUES ('DRAW2')
INSERT INTO DRAW (ID,INFO) VALUES (2,'DRAW2EDIT1') --PROBLEM HERE
INSERT INTO DRAW (ID,INFO) VALUES (2,'DRAW2EDIT2')
INSERT INTO DRAW (INFO) VALUES ('DRAW3')
INSERT INTO DRAW (INFO) VALUES ('DRAW4')

GO

--SHOULD THROW
INSERT INTO DRAW (ID,INFO) VALUES (9,'DRAW9')

GO

SELECT * FROM DRAW

GO