Solved

Check if value exists in master table before insert into details table.

Posted on 2009-07-09
8
486 Views
Last Modified: 2012-05-07
I have a details table and master table. The master table is linked to the details table by a unique key in the master table. The link field cannot be primary keys on both tables otherwise I would have use foreign keys. Now I want a trigger that will be fired on insert of the details table. The trigger should check if the link value exists in the master table and allow the insertion. If it doesn't exist the insertion should fail.
 Any ideas how to do this?
0
Comment
Question by:Atouray
  • 5
  • 3
8 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24814549
why dont you change the insert statement like this

insert into detailsTable
select @Value1 , @Value2  
from MasterTable  WHERE LinkValue =  @Value2
0
 

Author Comment

by:Atouray
ID: 24814663
Oh let me make myself clear. An application is used for data entry.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24815152
you can still use the above query
0
 

Author Comment

by:Atouray
ID: 24816435
Can you please elaborate on how to use this query?

Thanks.
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24817129
you dont have to create a trigger, just modify your exisitng INSERT statement like the one I posted above
0
 

Author Comment

by:Atouray
ID: 24821427
I don't have an existing insert statement. As I told you the application being user for data entry connects directly to the details.
0
 

Author Comment

by:Atouray
ID: 24821673
I've found a way to rollback the transaction but I need to print the message to the user. I've tried using raiserror but it doesn't show anything on the application however when I use test on the sql server it works. How do I print the error to the application connected via odbc to the database.
0
 

Accepted Solution

by:
Atouray earned 0 total points
ID: 24825849
I've solved it myself....
0

Featured Post

Free camera licenses with purchase of My Cloud NAS

Milestone Arcus software is compatible with thousands of industry-leading cameras for added flexibility. Upon installation on your My Cloud NAS, you will receive two (2) camera licenses already enabled in the software. And for a limited time, get additional camera licenses FREE.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
An overview of HIPAA and guidance on this topic that Experts Exchange members can offer.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
A simple description of email encryption using a secure portal service. This is one of the choices offered by The Email Laundry for email encryption. The other choices are pdf encryption which creates an encrypted pdf of your email and any attachmen…

896 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

17 Experts available now in Live!

Get 1:1 Help Now