Restrict Update if Seat greater than Invites

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

Transact-SQL (2000)

Restrict Update if Seat greater than Invites

Author

Topic

daipayan
Posting Yak Master

181 Posts

Posted - 2010-12-04 : 02:59:32

Hi,

I prepared a Trigger for restriction in Update, but it's not working.
I am having 3 tables: Category, Guest

Category:
---------------------------
ID | Name  | Seat_Allocated
---------------------------
 1 | RED   |      3
 2 | BLUE  |      2
---------------------------
Guest:
---------------------------------
ID | Category_ID | Name | Invite
---------------------------------
 1 |      1      | John |  YES
 2 |      2      | Rose |  YES
 3 |      2      | Alex |  YES
 4 |      2      | Jim  |  NO
---------------------------------

You can see for Category 2 (BLUE), I invited 2 Guests (Rose, Alex). Now, if I try to update Invite column of Jim from NO to YES, it should RAISERROR as "Cannot Invite More, Seat Filled"
For this I written a trigger, but not working:

CREATE TRIGGER	dbo.trgInvited_UpdateInsert
ON		dbo.Guest
AFTER		UPDATE, INSERT
AS
IF EXISTS
(
SELECT     TOP 1 c.ID, COUNT(i.invited) AS TOTAL, c.seat
FROM         dbo.Category as c INNER JOIN
                      INSERTED as i ON c.ID = i.catID
WHERE     (i.invited = 'YES')
GROUP BY c.ID, c.seat
HAVING      (COUNT(i.invited) > c.seat)
)
BEGIN
RAISERROR('Cannot Invite More, Seat Filled', 16, 1)
ROLLBACK TRAN
END

Please Help me on this.

Regards,
Daipayan
Software Programmer

Application has reported a 'Not My Fault' in module KRNL.EXE in line 0200:103F

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-04 : 03:17:50

I think it should be "for trigger" rather than "After Trigger" as per your requirement.

Vaibhav T

If I cant go back, I want to go fast...

daipayan
Posting Yak Master

181 Posts

Posted - 2010-12-04 : 04:04:34

No Sir,
I tried, still it's not working..

quote:
Originally posted by vaibhavktiwari83

I think it should be "for trigger" rather than "After Trigger" as per your requirement.

Vaibhav T

If I cant go back, I want to go fast...

Regards,
Daipayan
Software Programmer

Application has reported a 'Not My Fault' in module KRNL.EXE in line 0200:103F

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-04 : 04:52:06

Change your trigger script Like below -
See the red part.


CREATE TRIGGER	dbo.trgInvited_UpdateInsert
ON		dbo.Guest
AFTER		UPDATE, INSERT
AS
IF EXISTS
(
SELECT     TOP 1 c.ID, COUNT(i.invited) AS TOTAL, c.seat
FROM         dbo.Category as c INNER JOIN
                      Guest  as i ON c.ID = i.catID
WHERE     (i.invited = 'YES')
GROUP BY c.ID, c.seat
HAVING      (COUNT(i.invited) > c.seat)
)
BEGIN
RAISERROR('Cannot Invite More, Seat Filled', 16, 1)
ROLLBACK TRAN
END

Vaibhav T

If I cant go back, I want to go fast...

daipayan
Posting Yak Master

181 Posts

Posted - 2010-12-04 : 05:14:32

Thank You Sir..it's working..

But I didn't understand why it did not took the INSERTED??

quote:

Originally posted by vaibhavktiwari83

Change your trigger script Like below -
See the red part.


CREATE TRIGGER	dbo.trgInvited_UpdateInsert
ON		dbo.Guest
AFTER		UPDATE, INSERT
AS
IF EXISTS
(
SELECT     TOP 1 c.ID, COUNT(i.invited) AS TOTAL, c.seat
FROM         dbo.Category as c INNER JOIN
                      Guest  as i ON c.ID = i.catID
WHERE     (i.invited = 'YES')
GROUP BY c.ID, c.seat
HAVING      (COUNT(i.invited) > c.seat)
)
BEGIN
RAISERROR('Cannot Invite More, Seat Filled', 16, 1)
ROLLBACK TRAN
END

Vaibhav T

If I cant go back, I want to go fast...

Regards,
Daipayan
Software Programmer

Application has reported a 'Not My Fault' in module KRNL.EXE in line 0200:103F

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-04 : 05:30:22

Because Inserted is a virtual table which keeps records which newly inserted or updated rather than keeping whole table records.
And join with one record will not give status of whole table Guest.
I hope I am much clear to explain.

Vaibhav T

If I cant go back, I want to go fast...

daipayan
Posting Yak Master

181 Posts

Posted - 2010-12-04 : 05:45:48

Yes Sir, understand!!
Thank YOU

quote:
Originally posted by vaibhavktiwari83

Because Inserted is a virtual table which keeps records which newly inserted or updated rather than keeping whole table records.
And join with one record will not give status of whole table Guest.
I hope I am much clear to explain.

Vaibhav T

If I cant go back, I want to go fast...

Regards,
Daipayan
Software Programmer

Application has reported a 'Not My Fault' in module KRNL.EXE in line 0200:103F

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-04 : 05:51:09

Welcome

Vaibhav T

If I cant go back, I want to go fast...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources