March 9, 2010 at 10:02 pm
Hello,
I had a table name: candidate_details
Columns under this table are: ID(primary key), name, dob, mobile, telephone & email
Now I want to check the duplicate entries, but my duplicate checking should be with name, dob & email.
If either of the two columns found similar with the existing data in table, TRIGGER should RAISERROR.
PLEASE HELP ME.
March 9, 2010 at 11:58 pm
Please provide the DDL (CREATE TABLE statement) for the table, sample data (as a series of INSERT INTO statments) to populate the table and is representative of the problem, expected results based on the sample data, and what you have written so far to solve this problem.
Without all this, not much help we can provide except for shots in the dark that may or may not prove helpful. Plus, if you provide all the requested information, you will get back TESTED CODE.
March 10, 2010 at 12:11 am
TABLE:
CREATE TABLE [candidate_details] (
[candidate_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[name] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dob] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mobile] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_candidate_details] PRIMARY KEY CLUSTERED
(
[candidate_ID]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
TEST DATA:
INSERT INTO [dbo].[candidate_details]([name], [dob], [mobile], )
VALUES('Alex', '03/26/1970', '9876567898', 'alex@example.com')
VALUES('John', '12/24/1987', '9876567896', 'john@gmail.com')
VALUES('Sinha', '06/06/1980', '9877297783', 'sinha@yahoo.com')
VALUES('Alex', '09/21/1980', '9836829829', 'alex@example.com')
Here you will see, there are 2 candidate having same name n same E-Mail ID but different DOB. This can be accepted. But if the same candidate having same name, e-mail & DOB, that should not be accepted. A duplication should be checked with either of the two fields i.e. Name & E-Mail, E-Mail & DOB, Name & DOB or individual Name, E-mail, DOB, BUT IF ALL THE THREE CRITERIA ARE MATCHING, DATA SHOULD NOT BE INSERTED.
Hope this may help you to resolve my problem. Thank You in advance.
March 10, 2010 at 12:14 am
Hi,
You can check these duplicates using queries
select name,dob,count(*) from candidate_details group by name,dob
select name,email,count(*) from candidate_details group by name,email
select dob,email,count(*) from candidate_details group by dob,email
if count is more than 1 means duplicate values..
Similarly you can create insert and update trigger on table to check if any of this pair value already exists in the table.
March 10, 2010 at 12:17 am
I want to know, what can be the combined insert/update trigger for this!!
March 10, 2010 at 5:49 pm
mail2payan (3/10/2010)
I want to know, what can be the combined insert/update trigger for this!!
Ummmm... no... a trigger isn't the place for such code. You should already know if the data is going to cause an error before you try an insert. If you just want an error to pop up like you say, put a unique index on the column combination... it'll raise an error if anyone try's to insert duplicate info into the columns you care about.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 10, 2010 at 9:59 pm
I did this & it works....:-)
, CONSTRAINT [UNQ_name_email] UNIQUE (
[mobile]
,
)
, CONSTRAINT [UNQ_dob_email] UNIQUE (
[dob]
,
)
, CONSTRAINT [UNQ_dob_name] UNIQUE (
[dob]
, [name]
)
, CONSTRAINT [UNQ_dob_name_email] UNIQUE (
[dob]
, [name]
,
)
March 10, 2010 at 10:00 pm
Thank You for your Guidance!!!
March 10, 2010 at 11:18 pm
Very cool. Thank you for the feedback.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply