tags:

views:

67

answers:

3

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.

A: 

But why create a trgigger? Instead write a stored procedure, in that

if(@secondcolumn==1)
  set @secondcolumn=0
srinivas reddy thatiparthy
How can I call this procedure on event of insertion? I would like this to be done automatically, not by manual calling from my code.
Alex
then trigger would be ur choice..
srinivas reddy thatiparthy
Good, so the question remains the same - how to write such trigger:)
Alex
A: 

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.

slugster
Thank you for the answer, but the subtle thing is that due to the task specific I need to allow writing such record even if status column is invalid so foreign keys will not work for me.
Alex
+2  A: 

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; 
onedaywhen
Thanks, onedaywhen!
Alex