Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2009
    Location
    India
    Posts
    14

    Unanswered: Restrict Update if Seat greater than Invites

    Hi,

    I prepared a Trigger for restriction in Update, but it's not working.
    I am having 3 tables: Category, Guest
    Code:
    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:
    Code:
    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
    [hr]
    Application has reported a 'Not My Fault' in module KRNL.EXE in line 0200:103F

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Air code, but oughta work:
    Code:
    CREATE TRIGGER	dbo.trgInvited_UpdateInsert
    ON dbo.Guest AFTER UPDATE, INSERT AS BEGIN
    IF EXISTS (SELECT * FROM inserted AS i
    JOIN category AS c ON (c.catID = i.ID)
    JOIN guest AS g ON (g.ID = c.catID AND 'NO' <> g.invited)
    WHERE  'NO' <> i.invited GROUP BY c.ID
    HAVING c.seat < Count(*)) BEGIN
    RAISERROR('Cannot Invite More, Seat Filled', 16, 1)
    ROLLBACK TRAN END END
    GO
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •