Post reply

Date calculation trigger problem

  • March 28, 2005 at 11:12 am

    #163085

    I am trying to code an after insert, update trigger that will calculate a date but I am having a problem with my date variables being an invalid data type. I rightly or wrongly chose to try and perform this operation using a trigger because of a condition has to be tested and a few of the fields are calculated in a UDF and referencing these fields in another UDF is not allowed.

    Here is my code and the variables in question are @deficit_year and @yearapp giving the error message "Column or parameter #2 Cannot find data type date Column or parameter #5 Cannot find data type date @deficit_year has an invalid data type @yearapp has an invalid data type "

    CREATE TRIGGER [deficit_year_calc] ON [dbo].[DISPOSITION_TABLE]

    AFTER INSERT, UPDATE

    AS

    DECLARE @dispnum varchar(50)

    DECLARE @deficit_year date

    DECLARE @calc1 numeric(8,3)

    DECLARE @pendsub int

    DECLARE @yearapp date

    DECLARE @appcred numeric(8,3)

    DECLARE @annreq numeric(8,3)

    SELECT @dispnum = (SELECT disposition FROM inserted)

    SELECT @calc1 = (SELECT (approved_credits - annual_requirement) AS diff1 FROM inserted)

    SELECT @appcred = (SELECT approved_credits FROM inserted)

    SELECT @annreq = (SELECT annual_requirement FROM inserted)

    SELECT @pendsub = (SELECT pending_submission FROM inserted)

    SELECT @yearapp = (SELECT year_applied FROM inserted)

    IF @calc1 < 0 AND @pendsub = 0

       BEGIN

          SET @deficit_year = DATEADD(yyyy,1,@yearapp)

          UPDATE DISPOSITION_TABLE SET deficit_year = @deficit_year WHERE disposition = @dispnum

       END

    ELSE

       BEGIN

          SET @deficit_year = DATEADD(yyyy,FLOOR((@appcred + @pendsub)/(@annreq + 1)),@yearapp)

          UPDATE DISPOSITION_TABLE SET deficit_year = @deficit_year WHERE disposition = @dispnum

       END

     

    I do have the columns I am trying to define these variables with as date columns. Any help with what I am not doing right here would be appreciated.

    Thanks.

     

  • March 28, 2005 at 12:21 pm

    #549176

    Your datatype should be datetime instead of date.

    Sample:

     DECLARE @deficit_year datetime

     

Viewing 2 posts - 1 through 1 (of 1 total)

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