I would implement this using a stored procedure instead of a trigger. Use a separate key table to store the last used evidence number for each case.
I mocked up a minimally viable complete example.
Drop the objects from tempdb if they already exist, so we can modify the code as required.
USE tempdb;
IF OBJECT_ID(N'dbo.AddCase', N'P') IS NOT NULL
DROP PROCEDURE dbo.AddCase;
IF OBJECT_ID(N'dbo.AddEvidence', N'P') IS NOT NULL
DROP PROCEDURE dbo.AddEvidence;
IF OBJECT_ID(N'dbo.EvidenceKeys', N'U') IS NOT NULL
DROP TABLE dbo.EvidenceKeys;
IF OBJECT_ID(N'dbo.Evidence', N'U') IS NOT NULL
DROP TABLE dbo.Evidence;
IF OBJECT_ID(N'dbo.Cases', N'U') IS NOT NULL
DROP TABLE dbo.Cases;
GO
Create a Cases and Evidence table, along with an EvidenceKey table to store the incrementing Evidence Number.
CREATE TABLE dbo.Cases
(
CaseID int NOT NULL IDENTITY(1,1)
CONSTRAINT PK_Cases
PRIMARY KEY CLUSTERED
) ON [PRIMARY];
CREATE TABLE dbo.Evidence
(
EvidenceID int NOT NULL IDENTITY(1,1)
CONSTRAINT PK_Evidence
PRIMARY KEY CLUSTERED
, CaseID int NOT NULL
CONSTRAINT FK_Evidence_CaseID
FOREIGN KEY
REFERENCES dbo.Cases(CaseID)
, EvidenceNum int NOT NULL
, CONSTRAINT UQ_EvidenceNum
UNIQUE (CaseID, EvidenceNum)
);
CREATE TABLE dbo.EvidenceKeys
(
CaseID int NOT NULL
CONSTRAINT PK_EvidenceKeys
PRIMARY KEY CLUSTERED
, MaxEvidenceNum int NOT NULL
);
GO
Create a procedure used to add a new Case. You'd need to add parameters to this such as the Case Name, date, etc.
CREATE PROCEDURE dbo.AddCase
(
@CaseID int OUTPUT
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Cases TABLE
(
CaseID int NOT NULL
);
INSERT INTO dbo.Cases
OUTPUT inserted.CaseID
INTO @Cases (CaseID)
DEFAULT VALUES;
SELECT @CaseID = CaseID
FROM @Cases;
END
GO
Create a procedure to add Evidence. Again, this is only a proof-of-concept, so you'd need to add parameters to deal with the actual evidence item details.
CREATE PROCEDURE dbo.AddEvidence
(
@CaseID int
, @EvidenceID int OUTPUT
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @MaxEvidences TABLE
(
MaxEvidenceNum int NOT NULL
);
SET @EvidenceID = NULL;
UPDATE dbo.EvidenceKeys
SET MaxEvidenceNum += 1
OUTPUT inserted.MaxEvidenceNum
INTO @MaxEvidences(MaxEvidenceNum)
WHERE dbo.EvidenceKeys.CaseID = @CaseID;
SELECT @EvidenceID = MaxEvidenceNum
FROM @MaxEvidences;
IF @EvidenceID IS NULL
BEGIN
INSERT INTO dbo.EvidenceKeys (CaseID, MaxEvidenceNum)
VALUES (@CaseID, 1);
SET @EvidenceID = 1;
END
INSERT INTO dbo.Evidence (CaseID, EvidenceNum)
VALUES (@CaseID, @EvidenceID);
END;
GO
Insert some sample data:
DECLARE @CaseID int;
DECLARE @EvidenceID int;
EXEC dbo.AddCase @CaseID OUT;
EXEC dbo.AddEvidence @CaseID, @EvidenceID OUT;
SELECT @EvidenceID;
EXEC dbo.AddEvidence @CaseID, @EvidenceID OUT;
SELECT @EvidenceID;
EXEC dbo.AddEvidence @CaseID, @EvidenceID OUT;
SELECT @EvidenceID;
EXEC dbo.AddCase @CaseID OUT;
EXEC dbo.AddEvidence @CaseID, @EvidenceID OUT;
SELECT @EvidenceID;
EXEC dbo.AddEvidence @CaseID, @EvidenceID OUT;
SELECT @EvidenceID;
EXEC dbo.AddEvidence @CaseID, @EvidenceID OUT;
SELECT @EvidenceID;
Each execution of dbo.AddEvidence
will increment the value in the dbo.EvidenceKeys
table for the given @CaseID
in a single atomic operation, reducing the chance for locking to become a problem.
SELECT *
FROM dbo.Cases c
INNER JOIN dbo.Evidence e ON c.CaseID = e.CaseID
Results from the select
above:
╔════════╦════════════╦════════╦═════════════╗
║ CaseID ║ EvidenceID ║ CaseID ║ EvidenceNum ║
╠════════╬════════════╬════════╬═════════════╣
║ 1 ║ 1 ║ 1 ║ 1 ║
║ 1 ║ 2 ║ 1 ║ 2 ║
║ 1 ║ 3 ║ 1 ║ 3 ║
║ 2 ║ 4 ║ 2 ║ 1 ║
║ 2 ║ 5 ║ 2 ║ 2 ║
║ 2 ║ 6 ║ 2 ║ 3 ║
╚════════╩════════════╩════════╩═════════════╝
Since obtaining the maximum EvidenceKey
value for any given CaseID
, and updating the dbo.EvidenceKeys
table, occurs in a single atomic statement, the opportunity for deadlocks is vastly reduced, without the need for locking hints.
To test this design, I ran the following code. The first piece creates 100 "cases", each with 3 rows of "Evidence". Then, in 3 separate sessions, the 2nd piece of code inserts 100,000 rows into the Evidence
table, randomly assigning each evidence row to a randomly chosen case. No deadlocks occurred, and the process took under 1 minute on my old, slow, dev workstation.
DECLARE @loop int = 0;
DECLARE @CaseID int;
DECLARE @EvidenceID int;
WHILE @loop < 100
BEGIN
EXEC dbo.AddCase @CaseID OUT;
EXEC dbo.AddEvidence @CaseID, @EvidenceID OUT;
EXEC dbo.AddEvidence @CaseID, @EvidenceID OUT;
EXEC dbo.AddEvidence @CaseID, @EvidenceID OUT;
SET @loop += 1;
END
GO
This piece should run in 3 (or more) separate sessions:
DECLARE @loop int = 0;
DECLARE @CaseID int;
DECLARE @EvidenceID int;
WHILE @loop < 100000
BEGIN
SET @CaseID = (SELECT TOP (1) CaseID FROM dbo.Cases ORDER BY CRYPT_GEN_RANDOM(10));
EXEC dbo.AddEvidence @CaseID, @EvidenceID OUT;
SET @loop += 1;
END