cvservices
asked on
Deleting value on duplicate with trigger (or something else?)
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!
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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...
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...
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
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
ASKER
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!
Thanks for the heads up though, and for the samples. I will take a look.
The points are yours! :)
Cheers!
Glad to help you out.
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.