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
精彩评论