Sql-server – Create SQL Server trigger before insert/update

oraclesql servertrigger

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.

Best Answer

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;

Related Solutions

Thesql trigger to insert/update date column from datetime column

It's unclear why you need this but assuming that the table looks something like

CREATE TABLE Table1
    (`id` int not null auto_increment primary key, 
     `dt1` datetime, 
     `dt2` datetime, 
     `d1`  date, 
     `d2`  date);

your triggers might look the following way

CREATE TRIGGER tg_bi_table1
BEFORE INSERT ON table1
FOR EACH ROW
  SET NEW.d1 = DATE(NEW.dt1), 
      NEW.d2 = DATE(NEW.dt2);

CREATE TRIGGER tg_bu_table1
BEFORE UPDATE ON table1
FOR EACH ROW
  SET NEW.d1 = DATE(NEW.dt1), 
      NEW.d2 = DATE(NEW.dt2);

Here is SQLFiddle demo

Oracle Text CTXCAT Domain Index, Transactional, and Before Insert/Update Trigger

Aha! I've found the answer. Talk about an edge case.

First, I found this post from 2007, where someone says:

...the AFTER trigger for synchronizing the CTXCAT index on [column] is not firing (since my update statement does not include the indexed column).

...

Oracle, would it not be better to generate the CTXCAT trigger to examine the :old and :new values in the indexed column, rather than using a check on (if updating([column]))?

Over a year later, someone replied:

I fixed this issue by adding additional UPDATING (first_name and last_name) conditions in the DR$table_name trigger.

Here is part of the modified trigger.

if (inserting or updating('LAST_NAME_FIRST') or updating('FIRST_NAME') or updating('LAST_NAME')) then reindex := TRUE;

Hm, so the CTXCAT index uses a trigger to know when it needs to update the index for a particular entry. All I need to do is tweak the trigger and recompile it so it does what I want.

To get the content of the trigger:

SELECT text
  FROM   user_source
  WHERE  name = 'DR$NAMES_IDXTC'
  AND    type = 'TRIGGER'
  ORDER  BY line;

I copied this into Sublime, prettified it, and found this (simplified):

TRIGGER "TEST"."DR$NAMES_IDXTC" AFTER
INSERT
OR
UPDATE ON "TEST"."NAMES"
FOR EACH ROW DECLARE REINDEX boolean := FALSE;

BEGIN 

IF (inserting
    OR updating('COMPOUND_NAME')
    OR :new."COMPOUND_NAME" <> :old."COMPOUND_NAME") THEN REINDEX := TRUE;
END IF;

...

END;

You can see that the 12c Oracle Text version's AFTER trigger does actually compare the :new and :old values of the indexed column to see if it needs to update--not the case back in 2008.

So...if I'm updating the :new value in my BEFORE trigger, that should be reflected in the AFTER trigger, and the comparison would kick off an update to the index. What gives?

Well, here are the two SQL statements I was using:

update test.names set 
  first_name = 'Skye', 
  last_name = 'Fillingim'
  where ... ;

update test.names set
  first_name = null,
  last_name = null
  where ... ;

(Slightly different than what I said in my question, I apologize.)

The effect here is that, each time I used one of these statements, either the :old.compound_name or :new.compound_name would be null. So when we get to this condition:

OR :new."COMPOUND_NAME" <> :old."COMPOUND_NAME"

We are doing an equality comparison against a null, which evaluates to UNKNOWN. Hence, no index update.

This is actually an extreme edge case, because you have to be indirectly modifying :new.column via a trigger, and either :new or :old must be null. I would never have discovered it it I hadn't used those exact SQL statements.

So, we have a slight update to the trigger:

IF (inserting
    OR updating('COMPOUND_NAME')
    OR :new."COMPOUND_NAME" <> :old."COMPOUND_NAME"
    OR (:new."COMPOUND_NAME" IS NULL AND :old."COMPOUND_NAME" IS NOT NULL)
    OR (:new."COMPOUND_NAME" IS NOT NULL AND :old."COMPOUND_NAME" IS NULL)
    ) THEN REINDEX := TRUE;

And then just stick CREATE OR REPLACE in front of the old trigger, recompile it, and everything works perfectly.

Best Answer

Related Solutions

Thesql trigger to insert/update date column from datetime column

Oracle Text CTXCAT Domain Index, Transactional, and Before Insert/Update Trigger

Related Question