Solved

Lookup Values

Posted on 1998-10-07
1
189 Views
Last Modified: 2010-03-19
Does anyone know of a third-party-product that will allow a MS SQL 7 field to check another table to determine valid values?  This can't be done with a foreign key in my scenario and I don't believe it can be done in SQL.
0
Comment
Question by:scarlett
1 Comment
 
LVL 2

Accepted Solution

by:
formula earned 50 total points
ID: 1090447
Hi Scarlett!

To do what you want to do, you need to write a trigger.
A trigger will valid values in an insert or update from a lookup table.  It will be something like this:

create trigger validate_field
on data_table
for insert,update
as

if (select count(*) from data_table, inserted
       where lookup_table.field!=inserted.field) > 0
 begin
   rollback trans
   raiserror('Error inserting records, Value not valid,16,101)
 end
return
GO

This is not exact, but the general idea.  Let me know if you need any further assistance.

0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server 2012 r2 - Make Temp Table Query Faster 5 40
Sql Stored Procedure field variable 17 27
SSRS 2013 - Overlapping reports 2 19
SQL Server 2012 r2 - Varible Table 3 20
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now