Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2010
    Posts
    5

    Unanswered: Problem with Trigger to get Datediff

    HELLO

    I created a table with 3 columns.

    2 columns for date. (date type) Name: ProbableDate and RealDate
    1 column for the difference of these two dates in days. (int type)

    I insert the probable date.
    The value for RealDate becaomes NULL.

    Until then everything is fine.

    Then I want to create a Trigger AFTER UPDATE, that wheen the RealDate is updated to a date value automatically gets the difference from the ProbableDate and RealDate and save it to the difference column.

    Here is my code (Note: in my code the columns are not actually called like that since I'm latin and my variables are in spanish but I guess you can still understand the code)

    CREATE TRIGGER dbo.DIFERENCIA
    ON dbo.MaterialColocado
    AFTER UPDATE
    AS
    BEGIN
    DECLARE @CUENTA AS INT
    SET @CUENTA = 1
    DECLARE @LIMITE AS INT
    SET @LIMITE = (SELECT COUNT(*) FROM MaterialColocado)
    DECLARE @ID AS INT
    DECLARE @FECHAP AS DATE
    DECLARE @FECHAC AS DATE
    WHILE (@CUENTA <= @LIMITE)
    BEGIN
    WITH NUEVO AS (
    SELECT id, fechapromesa, fechacumplida, ROW_NUMBER() OVER (ORDER BY ID) AS LINEA

    FROM MaterialColocado
    )
    SELECT @ID = id, @FECHAP = fechapromesa, @FECHAC = fechacumplida FROM NUEVO WHERE LINEA = @CUENTA
    UPDATE MaterialColocado SET diferenciafechas = DATEDIFF(day, @FECHAP, @FECHAC) WHERE ID = @ID
    SET @CUENTA = @CUENTA + 1
    END
    END

    I think theres the problem because if I go line by line there it thorws me a strange error.

    Any ideas? or How can I do the trigger

    Thanks in advance.

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You know you can also replace your difference column with a calculated field, which will eliminate the need for a trigger.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Jul 2010
    Posts
    5
    Hi,

    Yeah, can you give me some more info on that?, but it will automatically work when I update a row in my table?

    Thanks

  4. #4
    Join Date
    Jul 2010
    Posts
    5
    Hey,

    I already researched the computed columns, I can't believe I was complicating myself with the trigger, this is so easy.

    Thank you very much for your answer.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •