Update Statement fails
Posted on 2008-06-26
This is a bit difficult to explain. Basically, I have a 3rd-party product that acts as a "data-pump" between our manufacturing plant and my instance of SQL Server 2005. Activities in the plant cause INSERTs to be executed in SQL. Works great.
I have a trigger on one such table, which contains an UPDATE statement. In a nutshell, that UPDATE looks like this:
StopEventID = @EventID,
StopTime = @Time,
StopEventValue = @EventValue,
StopEventCode = @EventCode,
StopStatusCode = @StatusCode,
DurationSeconds = DATEDIFF(ss, lss.StartTime, @Time)
FROM dbo.LineSpeedSessions lss
(lss.StopEventID IS NULL OR lss.StopEventID = 0)
lss.LineID = @LineID
AND -- where it's the most recent event on this line, of this type, that's previous to this event
SessionID = (SELECT MAX(SessionID) FROM dbo.LineSpeedSessions lss2 WHERE (lss2.LineID = @LineID AND lss2.StartEventCode = @EventCode AND lss2.StartTime < @DatapointTimestamp))
AND -- where it's the same day
[PFDC].[dbo].[fnGetDateTrunc](lss.StartTime) = [PFDC].[dbo].[fnGetDateTrunc](@DatapointTimestamp)
AND -- where there are no shift breaks between the start and stop of this session
NOT EXISTS (
SELECT aot.ID FROM dbo.AnyOtherTable aot
In a nutshell: the UPDATE fails when that last NOT EXISTS is included, and it succeeds when that NOT EXISTS is absent.
I need the NOT EXISTS there. But is that illegal or something? Bad syntax? Seems like I couldn't alter the trigger if it were syntactically invalid.
Oh, I've also tried other tables in that NOT EXISTS. Because originally, it was targeting a table in another database (PFDC), so I changed it to see if that was the problem. Nope. I can use other local tables, and very basic SELECTs inside that NOT EXISTS. Same problem.
I'd put PRINT statements in here if I could see them after the fact. I don't know if there's a way to do that in Sql Server though--have PRINT statement results written to a text file that's retrievable after the trigger is fired.
Points to the first answer that solves and explains. Thank you!