October 18, 2010 at 4:29 am
Hello Room,
I have a automated time table generation trigger when I insert into this table the time table should get generated automatically in the timetable table, to my knowledge and beleif this trigger is getting compiled properly without errors and record is also getting inserted into the table on which this trigger is created, but it is not inserting any records into the timetable table as given in this insert trigger.
Please can you guide me what is wrong with this trigger ?
ALTER trigger [dbo].[timetableautomatedins] on [dbo].[college_timetableautomated] after insert AS
DECLARE @SWV_NEW_CO VARCHAR(255)
DECLARE @SWV_NEW_COLLEGECD VARCHAR(255)
DECLARE @SWV_NEW_COURSECD VARCHAR(255)
DECLARE @SWV_NEW_CLASSCD VARCHAR(255)
DECLARE @SWV_NEW_DIVISION VARCHAR(255)
DECLARE @SWV_NEW_YEARCD VARCHAR(255)
DECLARE @SWV_NEW_STARTDATE VARCHAR(255)
DECLARE @SWV_NEW_ENDDATE VARCHAR(255)
DECLARE @SWV_NEW_DAY VARCHAR(255)
DECLARE @SWV_NEW_BUILDINGCD VARCHAR(255)
DECLARE @SWV_NEW_FLOORNO VARCHAR(255)
DECLARE @SWV_NEW_ROOMNO VARCHAR(255)
DECLARE @SWV_NEW_WING VARCHAR(255)
DECLARE @SWV_NEW_FROMTIME VARCHAR(255)
DECLARE @SWV_NEW_TOTIME VARCHAR(255)
DECLARE @SWV_NEW_SUBJECTID VARCHAR(255)
DECLARE @SWV_NEW_TEACHERCD VARCHAR(255)
DECLARE @SWV_Cursor_For_NEW CURSOR
SET @SWV_Cursor_For_NEW = CURSOR FOR SELECT co, collegecd, coursecd, classcd, division, yearcd, startdate, enddate, day, buildingcd, floorno, roomno, wing, fromtime, totime, subjectid, teachercd FROM inserted
OPEN @SWV_Cursor_For_NEW
FETCH NEXT FROM @SWV_Cursor_For_NEW INTO @SWV_NEW_CO,@SWV_NEW_COLLEGECD,@SWV_NEW_COURSECD,@SWV_NEW_CLASSCD,@SWV_NEW_DIVISION,
@SWV_NEW_YEARCD,@SWV_NEW_STARTDATE,@SWV_NEW_ENDDATE,@SWV_NEW_DAY,
@SWV_NEW_BUILDINGCD,@SWV_NEW_FLOORNO,@SWV_NEW_ROOMNO,@SWV_NEW_WING,
@SWV_NEW_FROMTIME,@SWV_NEW_TOTIME,@SWV_NEW_SUBJECTID,@SWV_NEW_TEACHERCD
WHILE @@FETCH_STATUS = 0
begin
DECLARE @vholidaydate DATETIME
DECLARE @vstartdate DATETIME
DECLARE @venddate DATETIME
DECLARE @vtimetablestartfrom DATETIME
DECLARE @vtimetableendfrom DATETIME
DECLARE @vtimetableday VARCHAR(50)
DECLARE @c1 CURSOR
DECLARE @c2 CURSOR
SET @c1 = CURSOR FOR select startdate,enddate from college_classmaster where co = @SWV_NEW_CO and
collegecd = @SWV_NEW_COLLEGECD and coursecd = @SWV_NEW_COURSECD and classcd = @SWV_NEW_CLASSCD
and division = @SWV_NEW_DIVISION
open @c1
fetch @c1 into @vstartdate,@venddate
SET @vtimetablestartfrom = @SWV_NEW_STARTDATE
SET @vtimetableendfrom = @SWV_NEW_ENDDATE
SET @vtimetableday = @SWV_NEW_DAY
while 1 = 1
begin
SET @vtimetablestartfrom = CONVERT(DATETIME,@vtimetablestartfrom)+1
while 1 = 1
begin
SET @c2 = CURSOR FOR select docdate from college_holidays where co = @SWV_NEW_CO and collegecd = @SWV_NEW_COLLEGECD
and yearcd = @SWV_NEW_YEARCD and docdate = @vtimetablestartfrom
open @c2
fetch @c2 into @vholidaydate
if @@FETCH_STATUS = 0
SET @vtimetablestartfrom = CONVERT(DATETIME,@vtimetablestartfrom)+1
else
BREAK
close @c2
end
if SUBSTRING(CONVERT(VARCHAR(30),CONVERT(DATETIME,@vtimetablestartfrom),110),1,3) = SUBSTRING(@SWV_NEW_DAY,1,3) and @vtimetablestartfrom between @vstartdate and @venddate
insert into college_timetable(co,collegecd,coursecd,classcd,division,buildingcd,floorno,
roomno,wing,dateofclass,fromtime,totime,yearcd,subjectid,teachercd)
values(@SWV_NEW_CO,@SWV_NEW_COLLEGECD,@SWV_NEW_COURSECD,@SWV_NEW_CLASSCD,@SWV_NEW_DIVISION,@SWV_NEW_BUILDINGCD,@SWV_NEW_FLOORNO,
@SWV_NEW_ROOMNO,@SWV_NEW_WING,@vtimetablestartfrom,@SWV_NEW_FROMTIME,@SWV_NEW_TOTIME,@SWV_NEW_YEARCD,@SWV_NEW_SUBJECTID,@SWV_NEW_TEACHERCD)
if @vtimetablestartfrom > @vtimetableendfrom
BREAK
end
close @c1
FETCH NEXT FROM @SWV_Cursor_For_NEW INTO @SWV_NEW_CO,@SWV_NEW_COLLEGECD,@SWV_NEW_COURSECD,@SWV_NEW_CLASSCD,@SWV_NEW_DIVISION,
@SWV_NEW_YEARCD,@SWV_NEW_STARTDATE,@SWV_NEW_ENDDATE,@SWV_NEW_DAY,
@SWV_NEW_BUILDINGCD,@SWV_NEW_FLOORNO,@SWV_NEW_ROOMNO,@SWV_NEW_WING,
@SWV_NEW_FROMTIME,@SWV_NEW_TOTIME,@SWV_NEW_SUBJECTID,@SWV_NEW_TEACHERCD
end
CLOSE @SWV_Cursor_For_NEW
Any help is highly appreciated. Thank you in advance.
October 18, 2010 at 8:47 am
Anybody Please help ! Hello ! I hear echo ! :w00t:
October 18, 2010 at 8:58 am
I think I would start by removing the cursor from the trigger and instead insert the data as one set into the timetable table. You'll get performance problems when your tables grow large, otherwise.
John
October 18, 2010 at 9:40 am
Ouch!
One cursor in a trigger is bad enough, but three!
Also, two of your cursors are not being closed correctly and I doubt
if SUBSTRING(CONVERT(VARCHAR(30),CONVERT(DATETIME,@vtimetablestartfrom),110),1,3) = SUBSTRING(@SWV_NEW_DAY,1,3)
will work as it looks as it is comparing the first 3 chars of the date in mm/dd/yyyy format to the first three characters
of the day of the week.
You should try using a numbers/tally table, or a calendar table, (google these) to make the trigger set based.
Something like the following will get you started, although you should problably materialize the Numbers table.
SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER dbo.timetableautomatedins
ON dbo.college_timetableautomated
AFTER INSERT
AS
SET NOCOUNT ON
;WITH Numbers5(N) AS ( SELECT 1 UNION ALL SELECT 0 )
,Numbers4(N) AS (SELECT 1 FROM Numbers5 N1 CROSS JOIN Numbers5 N2)
,Numbers3(N) AS (SELECT 1 FROM Numbers4 N1 CROSS JOIN Numbers4 N2)
,Numbers2(N) AS (SELECT 1 FROM Numbers3 N1 CROSS JOIN Numbers3 N2)
,Numbers1(N) AS (SELECT 1 FROM Numbers2 N1 CROSS JOIN Numbers2 N2)
,Numbers(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM Numbers1)
,AllRows
AS
(
SELECT
I.co, I.collegecd, I.coursecd, I.classcd, I.division, I.buildingcd
,I.floorno, I.roomno, I.wing ,I.fromtime, I.totime
,I.yearcd, I.subjectid, I.teachercd
,I.[day]
,I.startdate + N.N - 1 AS dateofclass
FROM inserted I
JOIN Numbers N
ON I.startdate + N.N - 1 BETWEEN I.startdate AND I.enddate
)
INSERT INTO college_timetable
(
co, collegecd, coursecd, classcd, division, buildingcd
,floorno, roomno, wing ,dateofclass ,fromtime, totime
,yearcd, subjectid, teachercd
)
SELECT
co, collegecd, coursecd, classcd, division, buildingcd
,floorno, roomno, wing ,dateofclass ,fromtime, totime
,yearcd, subjectid, teachercd
FROM AllRows A
WHERE DATEPART(dw, A.dateofclass) =
CASE LEFT(A.[day], 3)
WHEN 'Sun' THEN 1
WHEN 'Mon' THEN 2
WHEN 'Tue' THEN 3
WHEN 'Wed' THEN 4
WHEN 'Thu' THEN 5
WHEN 'Fri' THEN 6
WHEN 'Sat' THEN 7
END
AND EXISTS
(
SELECT *
FROM college_classmaster C
WHERE C.co = A.co
AND C.collegecd = A.collegecd
AND C.coursecd = A.coursecd
AND C.classcd = A.classcd
AND C.division = A.division
AND A.dateofclass BETWEEN C.startdate AND C.enddate
)
AND NOT EXISTS
(
SELECT *
FROM college_holidays H
WHERE H.co = A.co
AND H.collegecd = A.collegecd
AND H.docdate = A.dateofclass
)
GO
October 18, 2010 at 9:41 am
Thanks Ken,
you have correctly pointed out, the logic is that each class is having a start and end date (divisionwise). If I enter any period in this table then first the class start and end date are scanned, then it is compared with holidays (whether the date generated falls in holiday) and finally it checks whether it has arrived on correct day. i.e whatever day is inserted. Monday,Tuesday.... etc.. now when i arrive at a date in loop, i compare it with the day it has arrived and compare it with inserted day. And finally if the day has arrived i need to insert in time table.
I will have a look at it and let you know.
thank you for your help, If I face any problems I will post here immediately.
Kind Regards,
October 18, 2010 at 9:53 am
Please refer my next post.
October 18, 2010 at 10:06 am
Hi Ken,
I am sorry a mistake I made was that I deleted my trigger (fine) now when i copied your trigger and executed, it was alter trigger, so i converted "Alter" to "Create" and it got compiled successfully, but the other side is that It is still not getting the timetable table filled !
What may be wrong now, your trigger is getting compiled properly.
the timetable table structure is replica of the timetable automated table except the additional day column which i have put to compare and additionally remains the class table, there only start and end date is being referred and with holidays table only the date of holidays are referred. all are date fields, i have checked it well well.
:unsure:
October 18, 2010 at 9:38 pm
Hi Ken,
You rock man ! I got your trigger to work finally without any changes ! There was a problem in my database inserts. It was not picking up the proper combination of class and division that is the reason your trigger was not generating records.
Secondly, I have seen many triggers, but the one of your kind is totally unknown and completely new to me in terms of clauses. Can you please explain what logic was used ?
Thank you once again ! 🙂 Great Work ! Great Efforts Extended !
October 18, 2010 at 10:24 pm
aspardeshi (10/18/2010)
Secondly, I have seen many triggers, but the one of your kind is totally unknown and completely new to me in terms of clauses. Can you please explain what logic was used ?Thank you once again ! 🙂 Great Work ! Great Efforts Extended !
Can you identify what parts of the trigger that you don't understand? This will make it easier to explain to you what is going on.
Part of the trigger is creating a dynamic "tally" or "numbers" table. You can read all about them, and how they can replace a loop, here[/url].
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 18, 2010 at 11:41 pm
Hi Wayne,
initially it seems to me like dual table from oracle. But it is something more than that I guess. turning my heads on !
It is a good facility as i have gone through the article. I need to do more study.
There is one thing I do not understand, In Oracle I have used tremendous cursors in triggers without any performance issues, rest part I use to handle by query optimization in oracle expert and there goes your CPU time to low ! Oracle suggest indexes to minimize the query time, indexing resolves 95% of the query problems well. I think I have visited several sql server 2005 forums, I don't understand why people are shocked about cursors in triggers ! Everyone Awe for cursor in trigger ! If that was so terrible then sql server 2005 woudn't have designed cursor in trigger ! It is quiet normal though. How does it consume CPU usage, what relation cursor has got to triggers to make it a reason to avoid ? If you take only CPU usage then it has to shoot up on any query, then why cursors in triggers are specifically avoided for CPU usage ? SQL server 2005 should take cursor query in a trigger unlike any other query optimization by caching the query optimization technique in memory in first run, is my knowledge. Please throw some light on the same.
Thanks for your update, a new research for me !. 🙂
December 28, 2010 at 2:34 am
Hello Ken / Wayne,
Wish you all Happy New Year !
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply