How to create a trigger to validate existing item? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server Development (2000)

How to create a trigger to validate existing item?

Author

Topic

Andrew_S
Starting Member

2 Posts

Posted - 2008-09-30 : 11:43:32

Hi everyone,

I have table tblProducts with two fields ProductID (identity column with auto increment) and ProductName.
I have created an INSERT-trigger that verifies, when a new product is added, whether the product with the same name exists in the table already.

The following is the snippet:


ALTER  TRIGGER trigValidateProductName ON dbo.tblProducts 
FOR INSERT
AS

If (COUNT (*)
	FROM Inserted INNER JOIN tblProducts 
	ON Inserted.ProductName = tblProducts.ProductName
	WHERE Inserted.ProductName = tblProducts.ProductName)>0

BEGIN
	ROLLBACK TRAN
	RAISERROR ('The product exists already in database',16,1)
	RETURN
END

However, the COUNT function returns 1 even when I add a non-existing product and expect to receive zero instead.

Does anybody have an idea of this behavior?

Any help appreciated.

Andrew_S

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-30 : 11:51:14

the WHERE condition is redundant as you're already joining on ProductName.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-30 : 11:52:56

Also, rather than explicitly rollbacking, you can use an instead of insert trigger.

ALTER  TRIGGER trigValidateProductName ON dbo.tblProducts 
INSTEAD OF INSERT
AS

If (SELECT COUNT (*)
	FROM Inserted INNER JOIN tblProducts 
	ON Inserted.ProductName = tblProducts.ProductName)=0

BEGIN
	INSERT INTO tblProducts
        SELECT fields FROM inserted
END

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-09-30 : 12:12:59

I would be easier and more efficient to create a unique constraint on the ProductName column.

CODO ERGO SUM

Andrew_S
Starting Member

2 Posts

Posted - 2008-10-01 : 10:00:04

Visakh16,
thank you for your recommendation. Your INSTEAD OF TRIGGER works!!!

Michael Valentine Jones,
I reviewed my approach and I ended up using yours. It is indeed easier and more efficient!!!

Thank both of you!

Andrew_S

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources