July 28, 2004 at 11:08 pm
How can write a script for Email address will be maintained as a key (using a trigger), however, data with no email address will also be allowed.
July 29, 2004 at 1:26 am
Hmmm - not quite sure what you're after. Do you have a 'person' table containing an e-mail field and want to maintain a separate table which contains just e-mail address (and, presumably, a foreign key back to the main 'person' table)?
So do you therefore want to migrate your e-mail addresses away from your 'person' table? Or do you want to store them in two places for some reason?
Please provide more detail on what you are trying to do.
Regards
Phil
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 29, 2004 at 3:29 am
I'm not 100% sure what you are looking for either. Does the below work for you ?? If so, make sure you create an update trigger also with the below code present to prevent dups being created that way .....
DROP TABLE dbo.Email
GO
CREATE TABLE dbo.Email ( EmailID int identity(1,1), EmailAddress varchar(200))
GO
CREATE TRIGGER dbo.EmailINS ON Email FOR INSERT
AS
BEGIN
IF @@ROWCOUNT = 0 RETURN -- stop trigger code executing when no rows effected
DECLARE @error int
DECLARE @errormsg varchar(200)
IF EXISTS ( SELECT *
FROM inserted ins
JOIN dbo.Email mail
ON mail.EmailID != ins.EmailID
AND mail.EmailAddress = ins.EmailAddress
AND NULLIF(ins.EmailAddress,'') IS NOT NULL)
BEGIN
SELECT @error = 1000001
SELECT @errormsg = 'Duplicate email address found !!'
GOTO ERROR
END
ERROR:
/* if found a problem, rollback changes to Email */
IF @error != 0
BEGIN
ROLLBACK TRANSACTION
RAISERROR @error @errormsg
RETURN
END
END
GO
INSERT Email SELECT 'xxx@xxx'
INSERT Email SELECT 'xxx@xxx' -- errors
INSERT Email SELECT '' -- allows multiples
July 29, 2004 at 6:02 pm
"... If so, make sure you create an update trigger ..." - no need. Just change the create trigger line as follows:
CREATE TRIGGER dbo.EmailINS ON Email FOR INSERT, UPDATE
Phil
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply