CODEWITHSUNDEEP
sqlsql-server-2005triggers
jstim
jstim

Reputation: 2432

SQL trigger fires but original value is not inserted

I am transitioning my database from storing datetimes in Pacific time into UTC, but will not be changing all of the code at the same time.

I am trying to write a trigger that will update times entered in either column and convert them and insert them into the other column.

Ex. If I insert a Pacific time in the OriginalTime column, it should convert the time to UTC and update the NewTime column with a time in UTC. Conversely, if I insert/update the value in NewTime, it should convert the time back to Pacific and put it in OriginalTime.

Am I looping things incorrectly or something?

ALTER TRIGGER tr_Calls_CreateDate ON Calls
AFTER INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON

DECLARE
    @CallId INT,
    @CreateDate DATETIME,
    @CreateDateUTC DATETIME

--Create cursor
DECLARE CreateDate_Cursor CURSOR LOCAL FOR
    SELECT CallId, CreateDate, CreateDateUTC FROM Inserted

OPEN CreateDate_Cursor;

-- Get First Row
FETCH NEXT FROM CreateDate_Cursor INTO @CallId, @CreateDate, @CreateDateUTC;

WHILE @@FETCH_STATUS = 0
BEGIN
    IF UPDATE ([CreateDate])
    BEGIN
        UPDATE Calls
        SET CreateDateUTC = dbo.ConvertPacificTimeToUTC(@CreateDate)
        WHERE CallId = @CallId
    END

    IF UPDATE ([CreateDateUTC])
    BEGIN
        UPDATE Calls
        SET CreateDate = dbo.ConvertUTCTimeToPacific(@CreateDateUTC)
        WHERE CallId = @CallId
    END

    FETCH NEXT FROM CreateDate_Cursor INTO @CallId, @CreateDate, @CreateDateUTC;
END

-- Free Up Cursor
CLOSE CreateDate_Cursor;
DEALLOCATE CreateDate_Cursor;
END

For some reason, my current trigger is entering the converted value correctly, but the insert that caused the trigger to fire seems to be failing.

If I insert a value:

INSERT INTO Calls (CreateDate)
VALUES (GETDATE()) -- getdate currently outputs in pacific time

I get the trigger result but not the inserted value:

+-----------------+--------------------------+
|   CreateDate    |      CreateDateUTC       |
+-----------------+--------------------------+
|       NULL      | 2013-01-24 20:27:13.510  |
+-----------------+--------------------------+

Upvotes: 0

Views: 219

Answers (1)

Sebastian Meine
Sebastian Meine

Reputation: 11813

On an insert UPDATE(column) is true for all columns. That is wh your second update writes a null into the CreateDate column.

So you need to create two triggers, One that deals with updates as in your code and one that deals with inserts. If only CreateDate is valued, CreateDateUTC is NULL and you can check for that.

Also, you should really not use a cursor for this. Use something like this instead:

UPDATE c SET
  CreateDate = ISNULL(CreateDate, dbo.ConvertUTCTimeToPacific(i.CreateDateUTC)),
  CreateDateUTC = ISNULL(CreateDateUTC, dbo.ConvertPacificTimeToUTC(i.CreateDate))
FROM dbo.Calls AS c
JOIN INSERTED AS i
ON i.CallId = c.CallId;

That is, for the insert trigger. For the UPDATE you can use something like this:

UPDATE c SET
  CreateDate = CASE WHEN UPDATE ([CreateDateUTC]) THEN CreateDate
                  ELSE dbo.ConvertUTCTimeToPacific(i.CreateDateUTC)
               END,
  CreateDateUTC = CASE WHEN UPDATE ([CreateDate]) THEN CreateDateUTC
                    ELSE dbo.ConvertPacificTimeToUTC(i.CreateDate)
                  END
FROM dbo.Calls AS c
JOIN INSERTED AS i
ON i.CallId = c.CallId;

This is all untested code, but it should work.

EDIT: See my longer comment below for why you really should wrap this UPDATE inside of an IF statement.

Upvotes: 5

Related Questions