0

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.

  • 2
    If one of those is set to 1, should an insert be allowed where both columns are 0? – George.Palacios Jun 27 at 17:15
  • no, both are needed to be set to 0 to be allowed a new record for same client, so if either one of em is true, they can't make a new record – Jackal Jun 27 at 17:16
  • CHECK CONSTRAINT? – Vérace Jun 27 at 17:32
3

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?

  • It does look good but one of my requirements is to avoid triggers at all cost, i should have added this. ill update main post – Jackal Jun 27 at 18:48
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));
  • 1
    Glad to see this solved your problem. I think this may have an unforeseen consequence of impacting UPDATE statements as well. For example if one person with Client = ABC gets a session (HasSession is set to 1). A different record with the same Client value now can't acquire a session for itself either. The attempt to set that 2nd record with Client = ABC (set HasSession = 1) would fail the unique index of [XI_hRegistos_IsArranqueActivo]. I would either test that situation to see if it will cause an issue for you on the App Side or not. – Kirk Saunders Jun 27 at 19:54
  • Thanks but i tested this and it does exactly as intended, as per requirement 'HasSession = 1' or 'IsActive = 1' is allowed only per client, they can make a new record once both those booleans are 0 again. There's only one problem is that if Has Session is set to 1 and IsActive is set to 0, the client can still make a new record, – Jackal Jun 27 at 20:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.