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.
CREATE UNIQUE INDEX uniqueIndexesAreEasy ON dbo.SomeTable (a,b,c,d);