Solved

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

Posted on 2009-07-09
8
485 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

Ransomware continues to be a growing problem for both personal and business users alike and Antivirus companies are still struggling to find a reliable way to protect you from this dangerous threat.
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…
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…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

760 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

20 Experts available now in Live!

Get 1:1 Help Now