Update Statement fails

Posted on 2008-06-26
Last Modified: 2010-03-19
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:

      UPDATE dbo.LineSpeedSessions
            StopEventID = @EventID,
            StopTime = @Time,
            StopEventValue = @EventValue,
            StopEventCode = @EventCode,
            StopStatusCode = @StatusCode,
            DurationSeconds = DATEDIFF(ss, lss.StartTime, @Time)
         FROM dbo.LineSpeedSessions lss
         WHERE (
            (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!
Question by:bamapie
  • 4
  • 3
LVL 21

Expert Comment

ID: 21876264
You don't say what the failure is.  You get an error message?  I'm guessing it just doesn't update because you want some criteria on that not exists.  That Select act.ID thing will always success as long as there are any records at all in AnyOtherTable, so Exists will always be true and Not Exists will always be false.  Is there an ID in LineSpeedSessions  that you want to check for?

Author Comment

ID: 21876333
It just doesn't work.  That aforementioned data-pump just says, "it failed".  Really, it only increments a counter of the number of falied transactions.

>so Exists will always be true and Not Exists will always be false

Right.  And that should cause the UPDATE to not actually update anything, but it shouldn't cause it to actually fail the entire script.  I mean, there are other things this trigger does (ending up with an INSERT).  It's fine with me if the UPDATE affects zero rows.  But it failing--that I don't get.  I don't see why it would actually fail.

>Is there an ID in LineSpeedSessions  that you want to check for?

That SELECT inside the NOT EXISTS has been greatly simplified.  Originally it was doing a BETWEEN on some dates.  I simplified it down to this to just see if a very simple SELECT would succeed.
LVL 21

Expert Comment

ID: 21876636
Ah.  So no real sql error and maybe the "it failed" is because the app detects something it doesn't like?  Can you run the sql profiler?  It can show you specific sql being executed along with any errors, or it can be used to capture the sql being executed and then you can replay the sql against a copy of the database (another method of seeing what goes wrong).

Author Comment

ID: 21877082
Do I run sql profiler, and then somehow monitor this trigger?  And wait for it to fire?
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

LVL 21

Accepted Solution

mastoo earned 500 total points
ID: 21877426
As a first cut I wasn't thinking you necessarily need to see the trigger itself execute.  It seems like the first question is, does the trigger cause some kind of sql server error or does it cause a condition that leads the app to consider it a failure?  You can use the standard profiler template, add the error events, and then watch for the inserts on the table.  If the trigger is causing a sql error you'll see it after the insert and it might point in the right direction.  But if the insert seems to work with no sql error then it would be the app just considers something to be wrong and I'm not sure where you would go on that on.
LVL 21

Expert Comment

ID: 21877462
Although if you want to see the trigger execute, just add the profiler events under stored proc for statement starting and statement completed.
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21879052
the syntax on what you showed is not bad.
however, I must assume that your actual syntax is wrong somehow.
please show the actual code.

Author Comment

ID: 21899223

>It seems like the first question is, does the trigger cause some
>kind of sql server error or does it cause a condition that leads the app to consider it a failure?

>But if the insert seems to work with no sql error then it would be the app
>just considers something to be wrong

You know, I really think my "problem" isn't really a problem at all.  My updates are affecting rows (when applicable).  I just think the app is somehow detecting when a statement executed doesn't affect any rows, and saying "Hey it failed", when really that's not the case.

So, in my opinion, "False Alarm".  Thanks for your help.

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Query resolving a string conversion issue 26 39
datetime in sql 6 31
Sql Join Problem 2 33
SQL 2008 R2 calc date formula 3 28
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now