SQL Trigger- Updating count

Question

So I need to create a trigger that creates a new evidence number for each case. For instance, case #1 can have evidence #'s 1,2,3,4 and so on. Case #2 can also have evidence #'s 1,2,3, etc.

So I have a "Case" table (CaseID as PK) and a "Evidence" Table (EvidenceNum as PK and CaseID as FK and associated attributes)

So everytime I search for a specific caseID, I want a new "EvidenceID" column to populate. Such as Evidence item #1, #2, and so on. So these numbers can be repeated for each case. Hence why this is not the primary key. EvidenceNum is primary key but that wont be seen by the end user. Any help with this??

Answer 1

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