Create SQL Server trigger before insert/update

Question

I have a table with columns (a, b, c, d) and I would like to ensure that the 4-uplet a,b,c,d is unique for each row of the table by using SQL server triggers.

I basically would like to know how I can use triggers to check that the data that is about to be inserted/updated will not break the property I mentionned above.

So in case of an insert attempt, I would like to execute a SELECT on the table to check if the 4-uplet that's about to be inserted already exist and return an error value in this case. I would like to act similarly for updates.

EDIT: Actually my starting point is that I have an oracle SQL index that I want to convert to SQL Server in the context of a database migration. The Oracle SQL index creation structure is as follows:

CREATE UNIQUE INDEX MY_SCHEMA.MY_UNIQUE_INDEX ON MY_SCHEMA.MY_TABLE
(
    CASE "X_ID" WHEN 2 THEN "COL_A" ELSE NULL END ,
    CASE "X_ID" WHEN 2 THEN "COL_B" ELSE NULL END ,
    CASE "X_ID" WHEN 2 THEN "COL_C" ELSE NULL END ,
    CASE "X_ID" WHEN 2 THEN "COL_D" ELSE NULL END 
);

This index seems to impose unicity of (col_a, col_b, col_c, col_d) 4-uplet that's why I chose to translate it by an SQL Server trigger because functional indexes don't exist in SQL Server.

Answer 1

From the discussion in comments, I've restated your requirements:

When X_ID = 2, then A,B,C,D should be unique. When X_ID <> 2, then no uniqueness is necessary.

You can use a filtered index in SQL Server (ie, an index with a WHERE clause).

CREATE TABLE #MyTable (X_ID INT, Col_A VARCHAR(10),Col_B VARCHAR(10),Col_C VARCHAR(10),Col_D VARCHAR(10))

CREATE UNIQUE INDEX MyUniqueIndex ON #MyTable (Col_A,Col_B,Col_C,Col_D) WHERE X_ID = 2;

INSERT INTO #MyTable (X_ID, Col_A,Col_B,Col_C,Col_D)
--non-unique values for x_id = 1
VALUES (1,'A','A','A','A'),
       (1,'A','A','A','A'),
       (1,'A','A','A','A'),
--unique values for x_id = 2
       (2,'A','A','A','A'),
       (2,'A','B','A','A'),
       (2,'A','B','C','A'),
       (2,'A','B','C','D')

--non-unique value for x_id = 2
INSERT INTO #MyTable (X_ID, Col_A,Col_B,Col_C,Col_D)
VALUES (2,'A','A','A','A')

The last insert will give you an error for violating uniqueness: Msg 2601, Level 14, State 1, Line 17 Cannot insert duplicate key row in object 'dbo.#MyTable' with unique index 'MyUniqueIndex'. The duplicate key value is (A, A, A, A).

Depending on whether the columns are NULLable or not, and whether you want NULL to be included in the uniqueness check you may need your CREATE INDEX statement to include checks for NULL. In the above example, SQL Server will treat NULL as a discrete value (which is different than the way Oracle handles NULL). You may instead want to use something like this:

CREATE UNIQUE INDEX MyUniqueIndex ON #MyTable (Col_A,Col_B,Col_C,Col_D) 
    WHERE X_ID = 2
    AND Col_A IS NOT NULL
    AND Col_B IS NOT NULL
    AND Col_C IS NOT NULL
    AND Col_D IS NOT NULL;