'Pete',             '3',           'Audi',     'Showroom_London'
'John',             '5',           'Ford',     'Showroom_Scotland'
'Richard',          '2',           'Audi',     'Showroom_Ireland'

quote:

---

Originally posted by drewsalem

Consider the following:

ShowRoom_Manager ShowRoom_ID CarModel ShowRoomLocation

'Pete',             '3',           'Audi',     'Showroom_London'
'John',             '5',           'Ford',     'Showroom_Scotland'
'Richard',          '2',           'Audi',     'Showroom_Ireland'

I would like it that for every show room, there is at least 1 Audi. The best way I can think of doing this is by putting a check in place:

add Check ((select count (*) from Showrooms where CarModel = 'Audi' ) > 1);

However my check does not take into account "for every showroom"

How do I achieve the required?

Thanks

The Drewster

---

quote:

---

SELECT ShowRoom_Manager,
ShowRoom_ID,
CarModel,
ShowRoomLocation
FROM MyTable
WHERE ShowRoom_ID IN (SELECT ShowRoomID FROM MyTable WHERE CarModel = 'Audi')

---

quote:

---

Originally posted by drewsalem

1. I would like it that for every show room, there is at least 1 Audi.
2. add Check ((select count (*) from Showrooms where CarModel = 'Audi' ) > 1)

---

quote:

---

why would you write a contraint that checks for at least one Audi in a showroom?...the only reason I can think of, is when you delete cars from showroom and it is vital that at least one Audi is still there. This might be a better job for a trigger.

---

quote:

---

Originally posted by drewsalem

Yeh, that's pretty much the reason.

What's the trigger trigerring. I just want it to prevent me from deleting the last audi for a show room.

---

CREATE TRIGGER trgLastAudi ON MyTable
FOR DELETE
AS

IF (SELECT COUNT(*) FROM MyTable WHERE CarModel = 'Audi') < 1
    BEGIN
        ROLLBACK TRAN
        RAISERROR(16, 1, 'No more Audis left in table.')
    END

CREATE TRIGGER trgLastAudi ON MyTable
FOR DELETE
AS

DECLARE	@Name VARCHAR(100),
        @ID INT

SELECT		TOP 1 @ID = Showroom_ID,
                @Name = Showroom_Name
FROM		ShowroomTable
WHERE		Showroom_ID NOT IN (SELECT Showroom_ID FROM InventoryTable WHERE CarModel = 'Audi')

IF @ID IS NOT NULL
        ROLLBACK TRAN
        RAISERROR(16, 1, 'No more Audis left for showroom %d in %s.', @ID, @Name)
    END