January 6, 2015 at 4:02 am
Hi Friends,
I need a help from you.
THe Scenario is We have Tables like parent and Child Table.
Like we have Child Table as Name AcademyContacts,In that we have Columns like
Guid(PK)Not Null,
AcademyId(FK), Not Null,
Name,Null
WorkPhone,Null
CellPhone,Null
Email Id,Null
Other.Null
Since we have given Null to ''Workphone'',''Cellphone '', ''Email ID''.when inserting the data into these table
if the particular columns are empty while inserting also the data will get populate into the table.And
I need is if these columns are ''Workphone'',''Cellphone'' , ''Email ID'' they cant insert the data into table.
Like it must trigger like ''Please enter atleast one of these ''Workphone'',''Cellphone'' , ''Email ID'' columns.
How to do this in triggers please suggests me friends !!!!
I am waiting for your response !!!
January 6, 2015 at 4:07 am
Have you considered setting the columns to NOT NULL?
(After fixing existing NULLs, of course)
January 6, 2015 at 4:11 am
HI,
Thank you for your reply !
No it must be NULL only .
I know very well we can use it in Stored Procedure as well as when we can create this using UI . But my Manager told to create a Triggers for this . That's Y I have posted.
January 6, 2015 at 4:34 am
Well, you're the expert - that's why your manager hired you. I don't think setting the columns NOT NULL will work, since your requirement is only to have at least one of three columns not null. Why don't you create a check constraint - something like this:
ALTER TABLE AcademyContacts
ADD CONSTRAINT CK_WorkPhone_Cellphone_EmailID
CHECK (Workphone IS NOT NULL OR CellPhone IS NOT NULL OR EmailID IS NOT NULL)
You wouldn't get the customised error message that you mentioned, but you would get an error saying that the check constraint had been violated, which your application could handle for you.
John
January 6, 2015 at 4:35 am
Tell your manager that a trigger is not the appropriate solution to this. This should be checked in the front end or at the latest in the procedure which does the insert. Plus a table constraint to ensure that at least one is populated. But a trigger is not the appropriate solution here.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 6, 2015 at 5:04 am
Oh no, this is absolutely not the place for a trigger. The right answer is to make those columns not null. That will raise an error and that error can be dealt with on the front-end. That's the right answer.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply