CODEWITHSUNDEEP
sqlsql-server
Alex
Alex

Reputation: 1367

How not to insert specific value into database

I have MS SQL Server database and insert some values to one of the tables.

Let's say that the table contains columns ID, int Status and text Text.

If possible, I would like to create a trigger which prevents from writing specific incorrect status (say 1) to the table. Instead, 0 should be written. However, other columns should be preserved when inserting new values:

If the new row written is (1, 4, "some text"), it is written as is.
If the new row written is (1, 1, "another text"), it is written as (1, 0, "another text")

Is it possible to create such trigger? How?

EDIT: I need to allow writing such record even if status column is invalid, so foreign keys will not work for me.

Upvotes: 0

Views: 185

Answers (2)

onedaywhen
onedaywhen

Reputation: 57093

I think you would need a foreign key to ensure data integrity even if you choose to use a trigger (though I would myself prefer a 'helper' stored proc -- triggers can cause debugging hell) e.g. 

CREATE TABLE MyStuff 
(
 ID INTEGER NOT NULL UNIQUE,
 Status INTEGER NOT NULL 
    CHECK (Status IN (0, 1)), 
 UNIQUE (Status, ID) 
);

CREATE TABLE MyZeroStuff 
(
 ID INTEGER NOT NULL, 
 Status INTEGER NOT NULL 
    CHECK (Status = 0), 
 FOREIGN KEY (Status, ID)
    REFERENCES MyStuff (Status, ID), 
 my_text VARCHAR(20) NOT NULL
);

CREATE TRIGGER tr__MyZeroStuff
ON MyZeroStuff
INSTEAD OF INSERT, UPDATE
AS
BEGIN; 
INSERT INTO MyZeroStuff (ID, Status, my_text)
SELECT i.ID, 0, i.my_text
  FROM inserted AS i;
END;

Upvotes: 2

slugster
slugster

Reputation: 49965

An insert trigger has been mentioned, but another way to achieve this is to have a foriegn key on your Status column which points back to a Status table - this will not allow the write and change the value, instead it will simply disallow the write if the foriegn key is not valid.

Check out referential integrity for more info on this option.

Upvotes: 0

Related Questions