Trigger problems

  • I can't seem to get the syntax correct for these...

    I have to date fields StartDate and EndDate. I want to set the CourseLength field to be the difference ebtween these two fields. I also have some logic to remove weekends.

    I don't actually know how I can update the table in the end. Some help would be great.

    CREATE TRIGGER trg_CourseLen ON Courses FOR INSERT, UPDATE

    AS

    DECLARE @days AS INT

    IF EXISTS(SELECT * FROM inserted)

    BEGIN

    SET @days = DATEDIFF(d,StartDate,EndDate)

    IF @days > 5

    BEGIN

    DECLARE @weekends AS INT

    SET @days = @days - DATEDIFF(ww,StartDate, EndDate) * 2 + 1

    END

    ELSE

    BEGIN

    SET @days = @days

    END

    UPDATE Courses

    SET CourseLength = @days

    WHERE CourseId = inserted.CourseId

    END

  • What leaps out right away are two things:

    1. DECLARE @weekends INT (but this is never used, and doesn't appear to be required)

    2. Is the equation giving the correct results?

    Assume DATEDIFF(day, StartDate, EndDate) is 40 days over five weeks. Your equation will give 31 days as formatted (40 - 5 * 2 + 1), or do you expect 29 days (40 - (5 * 2 + 1))?

  • I forgot to remove the @weekends variable. You can ignore that.

    So far my equation works for what I need and I can change it later, the most important part is getting the update or insert to work.

  • Your trigger logic is thinking you are dealing with one record at a time. Remember that the inserted table can have multiple records so you need to do a set-based operation to be most efficient.

    Make the body of your trigger (note that you don't have to use the exists this way):

    UPDATE

    C

    SET

    C.CourseLength = DATEDIFF(ww,StartDate, EndDate) * 2 + 1

    FROM

    Courses C

    INNER JOIN Inserted I ON C.CourseID = I.CourseID

    WHERE

    DATEDIFF(d,StartDate,EndDate) > 5

    Now, you can do the same thing with a calculated field - that may be a better option.

    Your removal of weekends logic is not that great - there are postings on this site that can help you improve this.

  • I'll take a look at my weekend logic but I have tested it and compared it to just counting by hand and it works fine.

    If it's 40 days...

    (40 - (6*2)) + 1

    (40 - 12) + 1

    (28 + 1)

    29

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply