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***
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.
|||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
Wheredbo.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
WhereExists (select 1 from inserted as I Where i.EnrollCourseNumber = tblClassSchedule.CourseNumber)
End
|||
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.
|||
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.
|||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
|||
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).
|||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])
|||
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.
No comments:
Post a Comment