I would like to ensure data integrity in a column (actually multiple columns will need a trigger) in my table(s) by setting up a trigger which allows an update of my database field only if the value which is being written to the field in the database exists in another column (in another "check" table).
eg. I only want values "Yes", "No" or "" in many of my fields, which I store in a column named "YesNoBlank" in another table.
Does anyone know the easy way to do this? / Syntax for the trigger?
Why not use PK/FK instead of trigger? I mean you can define the YesNoBlank column as Primary Key in some table, and other columns whose values must exist in the YesNoBlank column as Foreign Key referenceing the YesNoBlank column. For more information about PK/FK, you can take a look at:
Creating and Modifying FOREIGN KEY Constraints
|||I think Check constraints are my preferance, as I have all of my fields which would potentially be Primary keys in your example in a single reference table (ie multiple columns)
My statement in('Yes','No','') does not work for my check constraint.
Does anyone know what my syntax would be?
|||A quick sample:
CREATE TABLE testConstraint (ID INT, NAME sysname, YESNO VARCHAR(3))
ALTER TABLE testConstraint
ADD CONSTRAINT yesno_check CHECK (UPPER(YESNO) in ('YES','NO'))
--This will succeed
INSERT INTO testConstraint VALUES(1,'Iori','Yes')
--This will fail
INSERT INTO testConstraint VALUES(2,'Kyo','noo')
For mor information, you can refer to:?ALTER?TABLE?
