Bit of advice with my Triggers

[tom_hotspur]

Hiya

I've created a Trigger on a Customers Table within my SQL Server 2k db that checks for an Entry with the same name. If one si found thenit rolls back the transaction to my app

Alter TRIGGER chk_for_duplicate_cust ON [dbo].[tbl_cust]
FOR INSERT
AS
DECLARE @name varchar(100), @count int
SELECT @name = i.CustomerName
FROM Inserted i
SELECT @count = COUNT(c.[CustomerName])
FROM tbl_cust c
WHERE c.CustomerName = @name
IF (@count > 1)
BEGIN
RAISERROR ('The Customer %s already Exists, 16, 1, @name)
ROLLBACK TRAN
END

This works fine but it is pretty hacked together (i.,e. the count has to be >1)
Is there a better way of writing the Trigger SQL perhaps using the EXISTS statement?
Any ideas would be great

cheers

Tom

[Bonker Gudd]

IF EXISTS (SELECT *FROM Inserted i, tbl_cust c
WHERE c.CustomerName = i.CustomerName)
BEGIN
RAISERROR ('The Customer %s already Exists, 16, 1, @name)
ROLLBACK TRAN
END

[tom_hotspur]

everytime i try to add a customer now it rolls back the transaction!

I've altered my Trigger so it now reads:

Alter TRIGGER chk_for_duplicate_cust ON [dbo].[tbl_cust]
FOR INSERT
AS
IF EXISTS (SELECT * FROM Inserted i, tbl_cust c
WHERE c.CustomerName = i.CustomerName)
BEGIN
RAISERROR ('The Customer already Exists', 16, 1)
ROLLBACK TRAN
END

Sorry to ask for help again but i'm stumped! I tried using a Trigger along the same lines to begin with and couldn't get it to work. No doubt ive made a stoopid mistake somewhere. Any Ideas?

Cheers

t

[Bonker Gudd]

Do you have an Id on your table you could check?

IF EXISTS (SELECT * FROM Inserted i, tbl_cust c
WHERE c.Id <> i.Id AND c.CustomerName = i.CustomerName)
BEGIN
RAISERROR ('The Customer already Exists', 16, 1) 
ROLLBACK TRAN
END

[tom_hotspur]

That works a treat kid!
Thanks for the help it was legendary!
By the way your rabbit piture is quality!
Nice 1

tom

[Bonker Gudd]