Sql restrict insert records for the same client based on 2 columns

Question

I have a simple table

CREATE TABLE Records
(
    Id INT,
    Client INT,
    IsActive BIT,
    HasSession BIT
)

I tried using OR statement but it only considered the 2nd argument.

CREATE NONCLUSTERED INDEX F 
    ON ClientsDB.Records (Client)  
    WHERE IsActive = 1;  

CREATE NONCLUSTERED INDEX F 
    ON ClientsDB.Records (Client)  
    WHERE HasSession = 1;  

I tried doing some research and seems you cannot use OR statement on these

As per trigger suggestion referred, one of my requirements is to avoid them at all so i cannot use anything related to triggers.

Answer 1

It appears to me that this was asked with a similar context in a StackOverflow Question.

Using that as a foundation I think a Create Trigger will give you what you are looking for.

CREATE TRIGGER dbo.UniqueClient ON dbo.Records
AFTER INSERT
AS
IF EXISTS 
(
    SELECT *
    FROM dbo.Records r 
        INNER JOIN inserted AS i 
            ON r.Client = i.Client 
    WHERE r.IsActive = 1 
    OR r.HasSession = 1
)
BEGIN
RAISERROR ('This Client Already Exists with an Active account or Session', 16, 1);
ROLLBACK TRANSACTION;
RETURN 
END;

The Key to this process is if the incoming row (represented in the inserted table) matches an existing client and that existing client has IsActive = 1 OR HasSession = 1, SQL will roll back the transaction and stop the insert. An error message is sent back to the application prior to the rollback.

Does this give you what you are looking for?

Answer 2

Here is the way i managed to solve this problem

CREATE UNIQUE NONCLUSTERED INDEX [XI_hRegistos_IsActivo]
    ON [dbo].[Records]([Client] ASC, [IsActive] ASC) WHERE ([IsActive]=(1));


GO
CREATE UNIQUE NONCLUSTERED INDEX [XI_hRegistos_IsArranqueActivo]
    ON [dbo].[Records]([Client] ASC, [HasSession] ASC) WHERE ([HasSession]=(1));