Associative Constraints - 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

SQL Server Development (2000)

Associative Constraints

Author

Topic

Jman0082
Starting Member

8 Posts

Posted - 2006-07-03 : 14:59:00

I have two tables with a many to many relationship established

TblProducts
ProdID (PK)
ProductCode

TblCategory
CatID (PK)
Category

tblAssocProdCat
AssocID (PK)
ProdID (FK)
CatID (FK)

What I am trying to do is maintain the number of categories. I don’t want any categories with out an associated Product.

First: If a product is deleted then all the associations of that product to its associated Categories should be deleted. And Any category should be deleted if it no longer has an entry in AssocProdCat

Second: If a user selects a Product and wants to delete a specific category for that product (deleting the association) if that category only belonged to that product then it should be removed from the Category table

Example:
TblProducts
ProdID | ProductCode
1 | ABC
2 | DEF
3 | GHI

tblAssocProdCat
AssocID | ProdID | CatID
1 | 1 | 1
2 | 2 | 1
3 | 3 | 2
4 | 1 | 2
5 | 2 | 3

tblCategory
CatID | Category
1 | XYZ
2 | UVX
3 | RST

The following are independent:

So Deleting Product 3 should only delete GHI from tbl Products and Assoc 3 from tblAssocProdCat (simple cascading Delete)

Deleteing Product 2 should delete DEF from tbl products and AssocID 2 & 5 from tblAssocProdCat And Delete RST from tblCategory

Deleting the association of ABC to XYZ should just delete tblAssocProdCat - AssocID 1 but not tblCategory - CatID 1
Deleteing the association of DEF to RST should delete both the association entry and the category RST.

I thought about Using Triggers but I have never used them before and cant seem to figure them out and wanted to see if that was the right aproach or is there a different way?

Thanks

P.S. For posts It says HTML is Off how do I turn it on?

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-07-03 : 15:15:59

Jman0082
Starting Member

8 Posts

Posted - 2006-07-03 : 15:39:07

Are you saying drop both tblCategory and tblProducts and only have one table the Association table?

Sorry, I exluded this about the original post but tbl products and tbCategory contain more than just the 2 columns they each have infromation regarding the product and the category ie:Description, ProductionStatus ....

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-07-03 : 15:47:52

You will need a trigger then.
A delete trigger on the associations table.

But it seems a strange requirement to delete from the product & category tables when there is no longer an association.
Specially since a deletion of association information can result in a cascading delete of other information (product+category)!

rockmoose

Jman0082
Starting Member

8 Posts

Posted - 2006-07-03 : 17:43:43

Ok so, I was looking at how the trigger works and it looks like it only runs once even if there are three records deleted.
So The Deleted table must contain three rows, so what i need to do is loop through the deleted table pulling out the category and checking to see if it still exists in tblAssociations. If it does not exist then delete it from tblCategories. Is this what I need to do?

Im not really sure how to do the looping but would this be correct way to do this. It seems to be correct syntax, Just wondering if this is how


CREATE TRIGGER DeleteCategory
ON dbo.AssocProdFW 
FOR DELETE
AS
	PRINT 'Trigger DeleteFirmware'
	DECLARE getCatID CURSOR FOR SELECT catID FROM Deleted
	OPEN getCatID
	FETCH NEXT FROM getCatID
	WHILE @@FETCH_STATUS = 0
		IF NOT EXISTS(SELECT catID FROM tblCategoires WHERE CatID= getCatID.catID) BEGIN
			DELETE FROM tblCategories WHERE CatID = getCatID.CatID
		END
	FETCH NEXT FROM getCatID
	CLOSE getCatID
	DEALLOCATE getCatID

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2006-07-03 : 17:47:53

Don't use a cursor for the delete inside of your trigger. Join your deleted table to the tblCategoires table.

DELETE a
FROM tblCategoires a INNER JOIN Deleted ON (a.CatID = Deleted.CatID)
WHERE NOT EXISTS(SELECT * FROM tblAssocProdCat b WHERE a.CatID = b.CatID)

Jman0082
Starting Member

8 Posts

Posted - 2006-07-03 : 18:07:55

Works Great, I didnt want to open a cursor but I never would have thought to join the tables.

Last Question for this thread and i will show how much of a newbie i am.

is the a and b following the "FROM [someTableName]" just assigning the table to a different name?

Thanks

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2006-07-03 : 18:43:38

Ya "a" and "b" are called table aliases.

You need to use a table alias when you are joining tables in an update or delete command. However, the "b" table alias isn't required though just the table alias on the tblCategoires is required.

Jman0082
Starting Member

8 Posts

Posted - 2006-07-03 : 18:57:27

Thanks for the quick replies, much help

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-04 : 07:32:28

quote:
Originally posted by DustinMichaels

Ya "a" and "b" are called table aliases.

You need to use a table alias when you are joining tables in an update or delete command. However, the "b" table alias isn't required though just the table alias on the tblCategoires is required.

DustinMichaels - I'm going to disagree with this

.

I think an alias on the tblCategoires is not required, and this is valid syntax:

DELETE tblCategoires
FROM tblCategoires INNER JOIN Deleted ON (tblCategoires.CatID = Deleted.CatID)
WHERE NOT EXISTS(SELECT * FROM tblAssocProdCat WHERE tblCategoires.CatID = tblAssocProdCat.CatID)

This is not to fault your help, though - that was very good and very useful.

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources