Error with code? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Error with code?

Author

Topic

PatDeV
Posting Yak Master

197 Posts

Posted - 2006-06-29 : 13:56:06

ALTER trigger [dbo].[testinsert] on [dbo].[customer]
FOR INSERT
AS
if not EXISTS (SELECT email FROM contactinfo)
-- tying to check the new value is exist in the contactinfo if not then insert
begin
insert contactinfo(email, description)
select email,[desc] from inserted
end

nathans
Aged Yak Warrior

938 Posts

Posted - 2006-06-29 : 15:27:03

Ok, not sure what youre asking here... but at first glance your existence check is not checking whether the inserted email exists, but rather if the ContactInfo table is empty.

Is this what you want? You may want to check if the email being inserted exists at the destination, if not then insert it?

Also, if you think this logic can be put into the stored procedure code that is doing the Customer table insert then it should be.

Search on the Sqlteam site for info on triggers.

Tara makes some important points on Trigger Behavior here:
[url]http://weblogs.sqlteam.com/tarad/archive/2004/09/14/2077.aspx[/url]

Nathan Skerl

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-29 : 18:02:38

[code]ALTER trigger [dbo].[testinsert] on [dbo].[customer]
FOR INSERT
AS
~~if not EXISTS (SELECT email FROM contactinfo)~~
-- tying to check the new value is exist in the contactinfo if not then insert
begin
insert contactinfo(email, description)
select email,[desc]
from inserted i
where not exists (select * from contactinfo x where x.email = i.email)
end[/code]

nathans
Aged Yak Warrior

938 Posts

Posted - 2006-06-29 : 18:09:45

The only reason I didnt rewrite the trigger is because I wanted PatDev to examine his logic.

KH's syntax is correct, but should you be joining on Email? What if two Customers use the same Email Address? I think the join criteria should include the relationship between Customer and ContactInfo tables. I hope that is not Email.

Perhaps we should be joining on CustomerID?

What about when a Customer changes their Email? Should we INSERT another record or perform an UPDATE?

Nathan Skerl

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources