locked
After Insert Trigger Issue RRS feed

  • Question

  • Trying to create a trigger that will based on the recorded inserted into an enrollment table will update a schedule table.

     

    My syntax is as listed:

     

    Create Trigger increaseEnrollments

    on dbo.tblEnrollRegistrations

    After Insert As

    Begin

    set nocount on;

     

    Update dbo.tblClassSchedule

    set EnrollNumber = EnrollNumber + 1

    Where  dbo.tblClassSchedule.CourseNumber = inserted.EnrollCourseNumber

    End

     

    I keep getting msg 4101 'the multi-part identifier could not be bound'

     

    Not really sure how to fix this, I have attempted also referencing the table that trigger is fired from, along with referencing an exists statement ; and of course when I didn't place a where statement the trigger will create itself, but will increase the enrollment count on every record

     

    *** I went and applied all three indepently & found none of them would actually do the update that I was attempting to accomplish ... any other suggestions***

    Monday, July 23, 2007 3:12 AM

Answers

  • Well, first off you didn't include the the table in question, but in this table, the key of the table you are relating too is ClassInstId, not CourseNumber.  CourseNumber is nullable.  So it would be better if the

    dbo.tblEnrollRegistrations table had the ClastInstId, rather than the CourseNumber.

     

    Second, I would suggest you don't do this in a trigger and just count them as needed, unless there are few inserts.  This way ends up locking way more things than needed, usually.  You will have to implement UPDATE and DELETE triggers also

     

    But consider this example, with code modified from Umachandar's post:

     

    CREATE TABLE [dbo].[tblClassSchedule](
     [ClassInstID] [int] IDENTITY(1,1) NOT NULL,
     [CourseNumber] [int] NOT NULL UNIQUE,
     EnrollNumber int default 0
    )
    go
    Create table dbo.tblEnrollRegistrations
    (
        enrollRegistrationId int identity primary key,
        CourseNumber INT NOT NULL
    )
    go
    create Trigger increaseEnrollments
    on dbo.tblEnrollRegistrations
    After Insert As
    Begin
    set nocount on;

        update dbo.tblClassSchedule
        set EnrollNumber = EnrollNumber +
                               (select count(*)
                                from inserted as i
                                where i.CourseNumber =
                                     dbo.tblClassSchedule.CourseNumber);

    End
    go

    insert into [tblClassSchedule](CourseNumber)
    select 100
    union
    select 101
    go
    insert into dbo.tblEnrollRegistrations (CourseNumber)
    values (100)

    select *
    from  [dbo].[tblClassSchedule]
    go

     

    ClassInstID CourseNumber EnrollNumber
    ----------- ------------ ------------
    1           100          1
    2           101          0

     

    insert into dbo.tblEnrollRegistrations (CourseNumber)
    select 100
    union all
    select 100
    union all
    select 100
    union all
    select 101
    union all
    select 101

    select *
    from  [dbo].[tblClassSchedule]
    go

     

    ClassInstID CourseNumber EnrollNumber
    ----------- ------------ ------------
    1           100          4
    2           101          2

     

    Works as expected.

    Friday, July 27, 2007 11:16 PM

All replies

  • Try something like this:

     

    Code Snippet

    CREATE TRIGGER IncreaseEnrollments

       ON dbo.tblEnrollRegistrations

       AFTER INSERT

    AS

       IF @@ROWCOUNT = 0

          RETURN

     

       BEGIN

     

        SET NOCOUNT ON;

     

        UPDATE dbo.tblClassSchedule

           SET EnrollNumber = ( EnrollNumber + 1 )

           FROM dbo.tblClassSchedule c

              JOIN inserted i

                 ON c.CourseNumber = i.EnrollCourseNumber

     

       END

     

    As a side note, using 'tbl' as a table name prefix is quite out of 'style'. You will usually know from context if it is a table, as the wasted three keystrokes every time you type is serves no purpose.

     

    I hope you are all creating a trigger to decrease the EnrollNumber upon a registration cancellation.

    Monday, July 23, 2007 4:32 AM
  • The following queries also work,

     

    Code Snippet

     

    Create Trigger increaseEnrollments

    on dbo.tblEnrollRegistrations

    After Insert As

    Begin

    set nocount on;

     

     

    Update dbo.tblClassSchedule

    set EnrollNumber = EnrollNumber + 1

    Where  dbo.tblClassSchedule.CourseNumber in (select EnrollCourseNumber from inserted)

     

    End

     

     

    --or

     

    Code Snippet

    Create Trigger increaseEnrollments

    on dbo.tblEnrollRegistrations

    After Insert As

    Begin

    set nocount on;

     

     

    Update dbo.tblClassSchedule

    set EnrollNumber = EnrollNumber + 1

    Where  Exists (select 1 from inserted as I Where i.EnrollCourseNumber = tblClassSchedule.CourseNumber)

     

    End

     

     

    Monday, July 23, 2007 6:31 AM
  • Can you have multiple course numbers in the registrations table? If so, none of the posted solutions will produce the correct results. You need to do the following:
     
     
    Code Snippet
    update dbo.tblClassSchedule
    set EnrollNumber = EnrollNumber + (select count(*) from inserted as i
    where i.EnrollCourseNumber = dbo.tblClassSchedule.EnrollCourseNumber);

     

     

    The solutions with joins or exists (IN approach is same as EXISTS) will not count duplicate course numbers - for example, if there are more than one registrations per course which is likely.
    Thursday, July 26, 2007 3:53 PM
  •  

    Nathan,

     

    From your previous message

    My scenario is listed below, with some sample code suggested.  I went and applied all three indepently & found none of them would actually do the update that I was attempting to accomplish ... any other suggestions

    , please let us know exactly what you are "attempting to accomplish".

     

    If you don't give any feedback, we can't tune our suggestions to help you find a solution.

     

    Thursday, July 26, 2007 4:12 PM
  • nothing is happening ... thats the problem ... I am attempting to increase an enrollment head count with the triggers listed ... i know i can do it with Visual Basic, but the code that I know it will take seems to be more complicated than what a trigger would allow ...

     

    my first issue with my  original trigger was that it wasn't recognizing the column referenced, which basically the 3 suggestions should have fixed.  the 'enrollment number' does increase when i don't restrict the reference to a specific record, of course the problem with that is that it increases (and when i add the decrement trigger, decrease) the value of all records, which of course is not a result I want

     

    thx

    Thursday, July 26, 2007 6:37 PM
  •  

    For none of the suggestions to be working properly, there must be something about the tables and/or data that hasn't been disclosed and that would be helpful to find a working solution.

     

    Please post the table DDL for ClassSchedule and EnrollRegistrations, and a few rows of sample data in the form of INSERT statements (see this link for ideas).

    Thursday, July 26, 2007 7:09 PM
  • Here Are The Table Structures.  Pretty Much I am Working With Little Data Right Now, Because It Hasn't Been Rolled out yet ...

     

    tblClassSchedule

    ---------------------------------

    USE [NHSMS]
    GO
    /****** Object:  Table [dbo].[tblClassSchedule]    Script Date: 07/26/2007 15:56:31 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[tblClassSchedule](
     [ClassInstID] [int] IDENTITY(1,1) NOT NULL,
     [CourseNumber] [int] NULL,
     [CStartDate] [smalldatetime] NULL,
     [CEndDate] [smalldatetime] NULL,
     [MaxEnroll] [smallint] NULL CONSTRAINT [DF_tblClassSchedule_MaxEnroll]  DEFAULT ((0)),
     [EnrollNum] [int] NULL CONSTRAINT [DF_tblClassSchedule_EnrollNum]  DEFAULT ((0)),
     [InstName] [int] NULL,
     [ClassType] [int] NULL CONSTRAINT [DF_tblClassSchedule_ClassType]  DEFAULT ((1)),
     [DaySchedule] [bit] NULL CONSTRAINT [DF_tblClassSchedule_Active1]  DEFAULT ((1)),
     [ClassStatus] [int] NULL CONSTRAINT [DF_tblClassSchedule_ClassStatus]  DEFAULT ((1)),
     [ClassRoom] [int] NULL,
     [Notes] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
     [Active] [bit] NULL CONSTRAINT [DF_tblClassSchedule_Active]  DEFAULT ((1)),
     [EntryDate] [smalldatetime] NULL,
     [WebSch] [bit] NULL CONSTRAINT [DF_tblClassSchedule_WebSch]  DEFAULT ((1)),
     [CLength] [int] NULL CONSTRAINT [DF_tblClassSchedule_CLength]  DEFAULT ((0)),
     [CLocation] [int] NULL,
     [CScheduleType] [int] NULL,
     [Day1] [smalldatetime] NULL,
     [Day2] [smalldatetime] NULL,
     [Day3] [smalldatetime] NULL,
     [Day4] [smalldatetime] NULL,
     [Day5] [smalldatetime] NULL,
     [Day6] [smalldatetime] NULL,
     [Day7] [smalldatetime] NULL,
     [Day8] [smalldatetime] NULL,
     [Day9] [smalldatetime] NULL,
     [Day10] [smalldatetime] NULL,
     [Day11] [smalldatetime] NULL,
     [Day12] [smalldatetime] NULL,
     [CStartTime] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
     [CEndTime] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
     CONSTRAINT [PK_tblClassSchedule] PRIMARY KEY CLUSTERED
    (
     [ClassInstID] ASC
    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]

    GO
    USE [NHSMS]
    GO
    ALTER TABLE [dbo].[tblClassSchedule]  WITH CHECK ADD  CONSTRAINT [FK_tblClassSchedule_tblClassRooms] FOREIGN KEY([ClassRoom])
    REFERENCES [dbo].[tblClassRooms] ([ClassRmID])
    GO
    ALTER TABLE [dbo].[tblClassSchedule]  WITH CHECK ADD  CONSTRAINT [FK_tblClassSchedule_tblClassStatus] FOREIGN KEY([ClassStatus])
    REFERENCES [dbo].[tblClassStatus] ([CStatusID])
    GO
    ALTER TABLE [dbo].[tblClassSchedule]  WITH CHECK ADD  CONSTRAINT [FK_tblClassSchedule_tblCourses] FOREIGN KEY([CourseNumber])
    REFERENCES [dbo].[tblCourses] ([CourseID])
    GO
    ALTER TABLE [dbo].[tblClassSchedule]  WITH CHECK ADD  CONSTRAINT [FK_tblClassSchedule_tblCourseSchType] FOREIGN KEY([CScheduleType])
    REFERENCES [dbo].[tblCourseSchType] ([CSchTypeID])
    GO
    ALTER TABLE [dbo].[tblClassSchedule]  WITH CHECK ADD  CONSTRAINT [FK_tblClassSchedule_tblCourseType] FOREIGN KEY([ClassType])
    REFERENCES [dbo].[tblCourseType] ([SchItemTypeID])
    GO
    ALTER TABLE [dbo].[tblClassSchedule]  WITH CHECK ADD  CONSTRAINT [FK_tblClassSchedule_tblLocation] FOREIGN KEY([CLocation])
    REFERENCES [dbo].[tblLocation] ([LocID])
    GO
    ALTER TABLE [dbo].[tblClassSchedule]  WITH CHECK ADD  CONSTRAINT [FK_tblClassSchedule_tblTimeValue] FOREIGN KEY([CStartTime])
    REFERENCES [dbo].[tblTimeValue] ([TimeValue])
    GO
    ALTER TABLE [dbo].[tblClassSchedule]  WITH CHECK ADD  CONSTRAINT [FK_tblClassSchedule_tblTimeValue1] FOREIGN KEY([CEndTime])
    REFERENCES [dbo].[tblTimeValue] ([TimeValue])
    GO
    ALTER TABLE [dbo].[tblClassSchedule]  WITH CHECK ADD  CONSTRAINT [FK_tblClassSchedule_tblUsers] FOREIGN KEY([InstName])
    REFERENCES [dbo].[tblUsers] ([UserID])

     

    --------------------------------------

    tblEnrollReg

    -------------------------------------

    USE [NHSMS]
    GO
    /****** Object:  Table [dbo].[tblEnrollReg]    Script Date: 07/26/2007 15:56:00 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[tblEnrollReg](
     [EnrollID] [int] IDENTITY(1,1) NOT NULL,
     [EStudentNum] [int] NULL,
     [ECourseNumber] [int] NULL,
     [PayMethod] [int] NULL,
     [ClassPrice] [money] NULL,
     [EnrollDate] [smalldatetime] NULL,
     [EnrollBy] [int] NULL,
     [EActive] [bit] NULL CONSTRAINT [DF_tblEnrollReg_EActive]  DEFAULT ((1)),
     [CancelDate] [smalldatetime] NULL,
     [CancelBy] [int] NULL,
     [ClassStartDate] [smalldatetime] NULL,
     [ClassEndDate] [smalldatetime] NULL,
     [CourseLocation] [int] NULL,
     [EnrollNotes] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
     [Approved] [bit] NULL,
     [ApprovedBy] [int] NULL,
     [ApprovalDate] [smalldatetime] NULL,
     CONSTRAINT [PK_tblEnrollReg] PRIMARY KEY CLUSTERED
    (
     [EnrollID] ASC
    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]

    GO
    USE [NHSMS]
    GO
    ALTER TABLE [dbo].[tblEnrollReg]  WITH CHECK ADD  CONSTRAINT [FK_tblEnrollReg_tblClassSchedule] FOREIGN KEY([ECourseNumber])
    REFERENCES [dbo].[tblClassSchedule] ([ClassInstID])
    GO
    ALTER TABLE [dbo].[tblEnrollReg]  WITH CHECK ADD  CONSTRAINT [FK_tblEnrollReg_tblStudent] FOREIGN KEY([EStudentNum])
    REFERENCES [dbo].[tblStudent] ([StuRecID])

     

    Friday, July 27, 2007 8:42 PM
  • Well, first off you didn't include the the table in question, but in this table, the key of the table you are relating too is ClassInstId, not CourseNumber.  CourseNumber is nullable.  So it would be better if the

    dbo.tblEnrollRegistrations table had the ClastInstId, rather than the CourseNumber.

     

    Second, I would suggest you don't do this in a trigger and just count them as needed, unless there are few inserts.  This way ends up locking way more things than needed, usually.  You will have to implement UPDATE and DELETE triggers also

     

    But consider this example, with code modified from Umachandar's post:

     

    CREATE TABLE [dbo].[tblClassSchedule](
     [ClassInstID] [int] IDENTITY(1,1) NOT NULL,
     [CourseNumber] [int] NOT NULL UNIQUE,
     EnrollNumber int default 0
    )
    go
    Create table dbo.tblEnrollRegistrations
    (
        enrollRegistrationId int identity primary key,
        CourseNumber INT NOT NULL
    )
    go
    create Trigger increaseEnrollments
    on dbo.tblEnrollRegistrations
    After Insert As
    Begin
    set nocount on;

        update dbo.tblClassSchedule
        set EnrollNumber = EnrollNumber +
                               (select count(*)
                                from inserted as i
                                where i.CourseNumber =
                                     dbo.tblClassSchedule.CourseNumber);

    End
    go

    insert into [tblClassSchedule](CourseNumber)
    select 100
    union
    select 101
    go
    insert into dbo.tblEnrollRegistrations (CourseNumber)
    values (100)

    select *
    from  [dbo].[tblClassSchedule]
    go

     

    ClassInstID CourseNumber EnrollNumber
    ----------- ------------ ------------
    1           100          1
    2           101          0

     

    insert into dbo.tblEnrollRegistrations (CourseNumber)
    select 100
    union all
    select 100
    union all
    select 100
    union all
    select 101
    union all
    select 101

    select *
    from  [dbo].[tblClassSchedule]
    go

     

    ClassInstID CourseNumber EnrollNumber
    ----------- ------------ ------------
    1           100          4
    2           101          2

     

    Works as expected.

    Friday, July 27, 2007 11:16 PM