October 27, 2010 at 2:38 pm
Well, you can either do it in a trigger, or in the proc that inserts into that table.
You should be able to simply select the count from the table based on the column you want. If it goes over twelve, use raiserror and rollback to handle it.
What have you got so far? Might be able to help you fix it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 27, 2010 at 2:46 pm
Yeah I thought the same. Here's what I tried:
CREATE TRIGGER Trg_CartwallTitlesIns ON dbo.CartwallTitles
AFTER INSERT AS
DECLARE @rowcount tinyint
SELECT @rowcount = (SELECT COUNT(*) FROM dbo.CartwallTitles, inserted
WHERE CartwallTitles.CartwallID = inserted.CartwallID)
IF @rowcount > 12
BEGIN
RAISERROR ('The maximum number of carts for the cartwall has been reached', 16, 1)
ROLLBACK TRANSACTION
END;
GO
October 27, 2010 at 2:52 pm
First, that will only work if you're only inserting one type of CartwallID at a time. It also won't work if you insert multiple rows in one transaction, because your join will be semi-cartesian and will multiple the results.
If, for example, there are four rows already in there, and you insert four more, your join will come back with 16 rows, because it will join all combinations where the FK column is equal.
Why not simply have the trigger check:
if exists (select 1 from dbo.CartwallTitles group by CartwallID having count(*) > 12) begin ...
Then do your error and rollback.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 27, 2010 at 3:10 pm
Brilliant! These sort of things demonstrate my lack of depth with SQL Server. Thanks very much for your help - it worked a treat.
Thankfully I don't have anything else nearly this complicated to achieve with the rest of my database.
Ady
October 28, 2010 at 7:11 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 28, 2010 at 8:51 am
ady.foot7970 (10/27/2010)
I want to limit the number of Cartwall titles to 12 and this need a trigger to enforce this in the database.
You can enforce this without triggers by using DDL. Something like:
CREATE TABLE dbo.Cartwalls
(
CartwallID int NOT NULL
CONSTRAINT PK_Cartwalls PRIMARY KEY
,Cartwall varchar(20) NOT NULL
-- etc...
)
GO
CREATE TABLE dbo.CartwallTitles
(
CartwallID int NOT NULL
CONSTRAINT FK_CartwallTitles_CartwallID REFERENCES dbo.Cartwalls(CartwallID)
,TitlesNo tinyint NOT NULL
CONSTRAINT CK_CartwallTitles_TitlesNo CHECK (TitlesNo BETWEEN 1 AND 12)
-- etc...
,CONSTRAINT PK_CartwallTitles PRIMARY KEY (CartwallID, TitlesNo)
)
GO
October 28, 2010 at 1:48 pm
Not a good plan. What if you end up with concurrent inserts both picking 12 as the next value? Then both succeed.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 28, 2010 at 3:49 pm
Thanks for your efforts Ken but GSquared had already provided me with the answer and it's working perfectly well.
Regards,
Ady
October 29, 2010 at 3:31 am
GSquared (10/28/2010)
Not a good plan. What if you end up with concurrent inserts both picking 12 as the next value? Then both succeed.
It is a very good plan - the primary key, or an unique index if you prefer, will stop duplicates being added.
If you do use a trigger you should at least check that CartwallID is in inserted to stop future performance problems.
IF EXISTS
(
SELECT C.CartwallID
FROM dbo.CartwallTitles C
WHERE EXISTS
(
SELECT *
FROM inserted I
WHERE I.CartwallID = C.CartwallID
)
GROUP C.CartwallID
HAVING COUNT(*) > 12
)
BEGIN
...
Viewing 9 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply