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.

• Lamak: Why a trigger if you want to return an error?, you should just create a unique index
• anon: Is there a reason you aren't just using a unique index? CREATE UNIQUE INDEX uniqueIndexesAreEasy ON dbo.SomeTable (a,b,c,d);
• Platus: @AMtwo Please check my last edit, if a unique index can do want I describe in the EDIT why not
• anon: @kikus I assume that in your Oracle function-based index those four lines should be A,B,C,D? Not A,B,B,C?
• Platus: Yes, It was just a typo

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;

• ypercubeᵀᴹ: Right, this is correct when the 4 columns (A, B, C, D) are not nullable. If any of them (or all) are nullable, then you need to modify the index filter: WHERE (x_id = 2 AND col_a IS NOT NULL ... AND col_d IS NOT NULL) for 100% equivalence with Oracle's functional index. (because SQL Server behaves differently regarding Nulls and unique indexes/constraints).
• ypercubeᵀᴹ: Oracle would allow two rows with: (2, 'A',' B', 'C', NULL). SQL Server won't.
• anon: Ah, right. I forget Oracle treats NULLS differently. I'll add this to my answer.
• Platus: Thanks for this answer, and does this work also if my table has other columns beside col_a,col_b etc and x_id? @AMtwo
• anon: Correct. Only the columns included in the index need to be unique. The table can have whatever other non-unique columns.