Solved

Deleting value on duplicate with trigger (or something else?)

Posted on 2009-07-12
6
233 Views
Last Modified: 2012-05-07
I have a table which contains 4 fields:
PermID, CN, inAD, and DTS.

I cannot set any of the fields to be unique individually. They do have to be unique by combining PermID and CN.
Meaning:
PermID    CN
1016       5997
1016       5998
1017       5997
1017       5998

All the above are acceptable entries, however, I want to make sure that no additional duplicate value combination is inserted: i.e:
another 1016    5997

I'm not sure about the best way to go about doing this, though I figured that a FOR trigger may do the trick, to check upon inserting, and I think should also be INSERT/UPDATE, though I wasn't sure how to write that,  and exiting that insert/update statement if it already exists. I've attempted to write it, and I'm pretty sure I did it wrong, but maybe someone out there, can help me with getting what I need. Here's what I have so far: (Please excuse some of the syntax, I know it's wrong).

Also, if there's a better way to do this other than with a trigger, I'm definitely open to suggestions.

TiA!


CREATE TRIGGER [dbo].[Enrollment_Stop_On_Duplicate] ON [dbo].[Enrollment] 
 

FOR INSERT 
 

AS

BEGIN

SET NOCOUNT ON;
 

IF EXISTS(SELECT Enrollment.PermID, Enrollment.CN FROM INSERTED WHERE INSERTED.PermID=Enrollment.PermId and INSERTED.CN=Enrollment.CN);
 

END

Open in new window

0
Comment
Question by:cvservices
  • 4
  • 2
6 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 200 total points
ID: 24836770
Why not create an unique index on two columns like this:

create unique index IX_TEST on ur_table_name (PermID, CN)

Hope this helps
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24836775
>> Also, if there's a better way to do this other than with a trigger, I'm definitely open to suggestions.

An unique index validates far more better than your trigger making unique index more efficient for your scenario. If you missed mentioning any scenarios, kindly mention those things so that we can figure out whether unique index or trigger is efficient.

Anyhow to check for unique values in two columns, the unique index above will help you much better.
0
 
LVL 1

Author Comment

by:cvservices
ID: 24836803
Ah ... perfect! I knew there was something simple like this! This worked like a charm. Thank you very much for your help on this.

At this point, I'm not really seeing any side effects from having this, I have to look again at my database design, but solution should do it just fine.

Since I was asking about a trigger though, and since I'm in the process of learning.

If I were to write a TRIGGER that would check for a field existence, and "exit" , or skip that entry if the field exists.. how would I write that? if it's not too much trouble...
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24836829
One disadvantage of Unique index here is that if you try to insert duplicate values, it will throw out an error and might cause the application to crash if not properly handled.

In that case, a trigger would help by just raising an error message and a code sample as you requested below:

http://www.sqlmag.com/Article/ArticleID/25913/sql_server_25913.html
http://www.sqlmag.com/Files/23/25913/Listing_09.txt

Hope this helps
0
 
LVL 1

Author Comment

by:cvservices
ID: 24836836
Yes. I noticed that, though, I wrote my application to handle that error if and when it happens. also, this unique index is merely a safeguard at this point, as my application should never really get to the point where it tries to insert a duplicate.
Thanks for the heads up though, and for the samples. I will take a look.

The points are yours! :)

Cheers!
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24836843
Glad to help you out.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Temporarily disable SQL Replication 7 23
Sql server lock cursor 13 41
Help with SQL Query 23 39
SQL Maintenance Plan 3 17
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 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

10 Experts available now in Live!

Get 1:1 Help Now