SQL Server 2005 Insert Trigger or Instead of Insert Trigger

I have a table, Email, that holds email addresses, and another table, emailmessage, that holds email messages.  The tables do not have any relationship keys.  

For any new record inserted into emailmessage, first check if the values being inserted into fields "toemailaddress" OR "fromemailaddress" exist in table Email.  If yes, then do the insert.  If no, then don't.

I would like to use an Instead of Insert trigger and only commit if the condition is met.  Or, if not possible, do the check after insert and delete the row if the condition is not met.
worldfearAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
worldfearConnect With a Mentor Author Commented:
CREATE TRIGGER tri_EmailMessage on dbo.EmailMessage
INSTEAD OF INSERT
AS
DECLARE @no_of_rows int
DECLARE @email varchar(100)
SELECT EMAILID INTO #t1
FROM DBO.EMAIL
WHERE EMAIL IN (SELECT TOEMAIL FROM INSERTED)
SELECT @no_of_rows = @@ROWCOUNT
SELECT @email = (select toemail from inserted)

IF (@no_of_rows > 0)
INSERT INTO EmailMessage (toemail, emailmessage, hasattachment,senderemailaddress,cc,receivedtime,senton,subject)
VALUES (@email, null, null, null, null, null, null, null)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.