Triggers for checking complex uniqueness for table with soft deletes

Question

I have a table that contains information about permits for various regions.

It has unique identity primary key, and a two column unique key which is the application's permit number and the region ID.

It also supports soft deletes; when a row is deleted it just sets a bit field.

I'm struggling to enforce uniqueness with the DB.

Can't use a UNIQUE constraint as it fails if a deleted item uses the permit number. Can't see a way to use a check constraint because I need to run a query to check for duplicates.

I have got this far with a trigger:

        create table Test (
            ID int identity primary key,
            PN nvarchar(25),
            RID int,
            Deleted bit default(0)
        )
        go
        create trigger T_PN on Test instead of insert 
        as
        begin
            declare @pn nvarchar(25), @rid int
            select @pn = PN, @rid = RID from inserted 

            if exists(select ID from Test where PN = @pn and RID = @rid and Deleted = 0)
                RAISERROR('PN in use', 11, 1);
            else
                insert into Test select * from inserted
        end

However this won't work because of the identity column; An explicit value for the identity column in table 'Test' can only be specified when a column list is used and IDENTITY_INSERT is ON.

This project is an ongoing rapid development project; the table will change regularly so hardcoding the column names is not an option.

I don't know the ramifications of using IDENTITY_INSERT within a trigger and it feels hacky and dangerous?

I'm leaning towards an app-layer solution, but am I missing anything?

Answer 1

Use a filtered unique index instead (UNIQUE CONSTRAINTs don't support a WHERE clause in their definition):

CREATE TABLE dbo.Test (
   TestID int IDENTITY 
      CONSTRAINT PK_test PRIMARY KEY, --ALWAYS name your constraints
   PN nvarchar(25) NOT NULL,
   RID int NOT NULL,
   Deleted bit NOT NULL CONSTRAINT DF_Test_Deleted DEFAULT(0)
); ---ALWAYS name your constraints

GO

CREATE UNIQUE INDEX IX_Test_PN_RID_Active
   ON dbo.Test (PN,RID)
   WHERE Deleted = 0;

Then you can't INSERT (or UPDATE) a row to be the same as an existing "undeleted" row:

--All work
INSERT INTO dbo.Test (PN,RID)
VALUES(N'abc',2),
      (N'abc',3),
      (N'def',3),
      (N'xyz',99);
GO
--Fails
INSERT INTO dbo.Test (PN,RID)
VALUES(N'abc',2);
GO
SELECT *
FROM dbo.Test;
GO

UPDATE dbo.Test
SET Deleted = 1
WHERE TestID = 1;
GO
--Now succeeds
INSERT INTO dbo.Test (PN,RID)
VALUES(N'abc',2);
GO
--Fails, it's the same as the row we just inserted
UPDATE dbo.Test
SET RID = 2
WHERE TestID = 2;
GO

SELECT *
FROM dbo.Test;
GO
--Clean up
DROP TABLE dbo.Test;