Solved

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

Posted on 2009-07-09
8
489 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
Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

 

Author Comment

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

Thanks.
0
 
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

Surfing Is Meant To Be Done Outdoors

Featuring its rugged IP67 compliant exterior and delivering broad, fast, and reliable Wi-Fi coverage, the AP322 is the ideal solution for the outdoors. Manage this AP with either a Firebox as a gateway controller, or with the Wi-Fi Cloud for an expanded set of management features

Question has a verified solution.

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

If you are looking at this article, you have most likely been hit by some version of ransomware and are trying to find out if there is anything you can do, or what way you should react - READ ON!
Do you know what to look for when considering cloud computing? Should you hire someone or try to do it yourself? I'll be covering these questions and looking at the best options for you and your business.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

726 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