Database triggers - SQL Server - Fields only allowed if listed in another field in another table

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?

11/2/2006 5:50:30 AM 29906 articles. 0 followers. Follow

3 Replies

Similar Articles

[PageSpeed] 57

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 

Welcome to my SQL/ASPNET forum for Chinese
11/2/2006 7:58:42 AM

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?


11/3/2006 12:37:01 AM
A quick sample:

CREATE TABLE testConstraint (ID INT, NAME sysname, YESNO VARCHAR(3))

ALTER TABLE testConstraint

--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 

Welcome to my SQL/ASPNET forum for Chinese
11/15/2006 8:03:28 AM

Similar Artilces:

Error accessing database in App_Data that has linked tables from a database on another server
I have a database that lives on a Windows Server 2003 server (Server A). On another Windows 2003 server, I have my ASP.NET intranet application. In the App_Data folder of my ASP.NET application, I have a .mdb file that is basically a shell to hold links to the tables in the database file located on Server A. The reason why the master tables live in a database on Server A is because we access the data in those tables from projects outside ASP.NET, so I put the database on the server that made the most logical sense for universal access. Because I also want to access the data in those tables f...

Copy table from 1 database server to another
Hello is there a command to copy a SQL table from 1 server to another Many thanks GrantGrant Krieger I know you can do this using DTS, inside SQL Server - - are you asking, merely how to do it, or how to do it inside ASP.Net ?David WierMCP/ - where to look first!Control Grouper - easily control properties for multiple controls with one control!Calendar Express - The Best HTML Calendar Generator on the web!(Please 'Mark as Answer' when it applies)...

How to copy table definitions from Server Explorer to another database?
I created a development database via SQL Server 2005 Express that is part of VS 2008 on my work station.  Then I created a production SQL Server 2005 database on a GoDaddy server.  I would like to copy the table definitions from the development machine to the production machine.  I created a connection to the GoDaddy production database.  I can get to it using Server Explorer on VS 2008.  Now I want to create the table definitions -- the easy way if possible.  I did a right-click copy on the source table.  But when I right-clicked on the destination table t...

Copying Database table to another Database
I am using Visual Studio 2005 to connect to a database on a remote SQL2000 server.  I need to copy a table in a database on my local machine up to the server.  How can I do that? Thank you, Do you have enterprise manager or sql management studio?If so do use the Import/Export wizard to copy the table from A to B. -- Sam Critchley"Wise man say 'forgiveness is divine, but never pay full price for late pizza." - no - I have Access 2007 and it is nothing like Access 2000 which made this act easy   Part of my problem is that I am using V...

copy of table from another database into your database
We copy a table from a progress database into our sql server database that we use as a read-only table.  We perform this action through a job. Is there a way to have a read only table from another database that is put into your database that will always be insinc with the original copy to use as a read only copy, that as the original table is updated, it will proprogate to your copied table? Would setting up a package to import the data from the other database on a schedule work?  In SQL Server, you can do an import and you have the option to save the import as a package and sch...

how to copy one table from one database to another on different servers?
 Hello. I need to copy all of the rows in a table from a database on one server, to another existing table of the same name in a different database on a different server.  I'm trying to use a SELECT INTO statement.  Any idea how to do this?I've tried SELECT          *   INTO                  DestinationServer.dbo.DestinationDB.DestinationTableFROM             &nb...

How to copy table from one database to another database ?
Hello to you, I'm uisng pb8 How to copy some rows from customer table in (ABC database) to customer table in (XYZ databases) the selection row is : select * from customer where startdate = 2002-06-15 Please show me the code, step by step steven On Sun, 16 Jun 2002 22:30:37 -0400, steven wrote: >Hello to you, >I'm uisng pb8 > >How to copy some rows from customer table in (ABC database) to customer >table in (XYZ databases) > >the selection row is : >select * from customer where startdate = 2002-06-15 > >Please show me the code...

copying tables from one database to another database
i am using visual web developer 2005 and SQL Express 2005 with VB  as the code behindi have two databases . i want to copy all the tables with all the contents from one database to another database programaticallyhow to achieve this ?please help me  easiest way is to use the detach - attach method or backup and restoreKH...

How to copy a table from one database to another database ?
Hello to you, I'm newbie using pb8.02 I'm uisng ms-access 2000 I'd database abc & xyz that contains customer table I want to copy some record on abc.customer to xyz.customer How ? What is the easy way ? should I using datawindow ? (i meant should I always had dw to got this purpose ? ) please advice steven DataWindow purists will flinch, but I think an embedded SQL INSERT INTO xyz.customer (SELECT ... FROM abc.customer) is the clearest way to do that. Two datawindows/datastores, retrieve the first, RowsCopy, Update on the second will also work. Pi...

Table transfer from one Database to another Database
Hi,I want to transfer one table to transfer another table using dataset and writing ASP.NET code, tables are in different databases, and databases are in different servers. I wrote the following code, the second table is empty initially; public DataSet DS1;public DataSet DS2;public SqlDataAdapter DA2; private void Page_Load(object sender, System.EventArgs e){ // get dataset from database1 and display in gridSqlConnection conn1 = new SqlConnection("server=(local);database=;Uid=;password="); String strSQL1 = "select * from myTable1"; SqlDataAdapter DA1 = new SqlDataAdapter(strSQL1,conn1...

Can you Alter a Table from another tables Trigger??
I'm in the process of trying to take a specific value from one of our tables, and based on when a row is updated, get that updated value, and set it within the default attribute of a specific column in another table. Is anybody know if there are any problems I might run into trying to do this?? TIA Jeff Gibson Intercept Solutions Nashville, TN I think ALTER TABLE causes an implicit COMMIT, which might not be welcome within a trigger. Rather than using a DEFAULT value, you could write a BEFORE INSERT trigger on the second table. It would detect if the incoming row h...

getting user name from another table (id included in another table)
Hi,  I am using a datagrid and I have 2 tables (I will try to explain as simple as I can) ... First table contains SENDER_ID and TOPIC fields . The sender id's are the users Id's. Second table contains member records and Lets say it has it has MEMBER_ID , MEMBER_NAME columns. I get the values from first table to datagrid and I want the SENDER_ID 's to be converted to MEMBER_NAME 's  while displaying the data with datagrid . I am able to do this but I have to connect to db for every MEMBER_ID record to transform them to names .Here it is <ItemTemplate><%# ...

Copying information from One table to Another from 2 different databases on one server
Can I copy accross databases e.g.: In MS SQL I can: Insert into Database2..customer select * from Database1..customer Thanks in Advance Yes you can. The syntax in Sybase is same. The syntax is the same if you are on ASE. ASA does not support that syntax. You need to use a Proxy table instead. -- Jim Egan [TeamSybase] Houston, TX Sybase Developers Network ...

All Listed Datagrid Results from one database used in a datasource query for another Datagrid.
I have two separate databases with separate connection strings.   DataGrid (1) list a series of names from database (1).   How can the second DataGrid (2) datasource (2) use the DataGrid (1) LISTed values in it's query?  The query input requires the entire list.  Consequently, gridview1.selectedvalue does not appear to be an option. Thanks for your time,  Sincerely, Unhistoric   I'm not sure if I follow your statement above, but if you want to use the first DataSource as your Grid 2 DataSource then y...

can I compare one record from one table to another record in another table?
can I compare one record from one table to another record in another table? I need to test if this condition is met. My scenario is: i need to check one table for a value and compare it to a list of records. for example, table 1           columnrecord 1- applesrecord 2- orangesrecord 3- grapes table 2            col1   col2   col3    col4record 1- Null , Null, Null, grapes I need to compare table 2 records with a list of records from table 1. the fru...

Web resources about - Database triggers - SQL Server - Fields only allowed if listed in another field in another table -

Database - Wikipedia, the free encyclopedia
A database is an organized collection of data . The data are typically organized to model aspects of reality in a way that supports processes ...

Database - Wikipedia, the free encyclopedia
... requiring information. For example, modelling the availability of rooms in hotels in a way that supports finding a hotel with vacancies. Database ...

Five million customers affected by Vtech database hack
Toy and educational material seller Vtech has revealed five million customers were affected by a hack of its app database and has also suspended ...

Ben Carson calls for database of all foreigners in US
Boston Herald Ben Carson calls for database of all foreigners in US Boston Herald Republican presidential candidate, Dr, Ben Carson, center, ...

Donald Trump wants "surveillance of certain mosques," database of refugees - Videos - CBS News
... rally in Birmingham, Alabama, Republican presidential candidate Donald Trump called for increased scrutiny over mosques, along with a database ...

Trump Wants A Database For All Syrian Refugees Who Enter America
Trump Wants A Database For All Syrian Refugees Who Enter America

Change your passwords: NexusMods suffers database breach
... security breach by posts on reddit. Cybersecurity firm REN-ISAC had apparently contacted several university IT departments about a database ...

US Republican rivals blast Donald Trump for Muslim database comments
US Republican rivals blast Donald Trump for Muslim database comments - Donald Trump said he would implement a database to keep track of Muslims ...

VTech Admits Lack of Database Security Opened Door to Hack
A SQL injection, a common software flaw, was found to be the root cause in the VTech breach.

Animal Venom Database Could Be Boon To Drug Development
A new public database that catalogs animal toxins could spur the development of new drugs made with compounds found in animal venom.

Resources last updated: 12/9/2015 8:59:47 AM