update commands on a view inside a transaction

  • When running two update commands (1 for each table I want to update in the view) on a view inside a transaction, does the view result set change before the second update command?

  • If it's ran precisely between the 2 updates then yes.

    The answer changes depending on the isolation level of all the connections involved.

  • I suppose some code is in order.

    This was the state of the code before the weekend.

    View

    ALTER view [dbo].[view_set_DispInvNoComp] AS

    SELECT Dispatch.Dispatch, Dispatch.Invoice, Dispatch.RecDate, Dispatch.Complete, DispTech.Status, DispTech.Complete AS DispTechComplete, Sales.InvDate, DispTech.PromDate, DispTech.TPromDate, DispTech.TPromTime,DispTech.DispDate, DispTech.DispTime, DispTech.TimeOn,

    DispTech.TimeOff, DispTech.DateOff, Employee.EmpName, Employee.EmpNo, DispTech.SortDate, DispTech.SortTime,

    COUNT(case when dispatch.dispatch <> '' then 1 else null end) OVER(PARTITION BY dispatch.dispatch) AS entries,

    COUNT(case when dispatch.dispatch <> '' AND DispTech.Complete <> 'Y' then 1 else null end) OVER(PARTITION BY dispatch.dispatch) AS NOTYs

    FROM Dispatch INNER JOIN

    DispTech ON Dispatch.Dispatch = DispTech.Dispatch INNER JOIN

    Employee ON DispTech.ServiceMan = Employee.EmpNo INNER JOIN

    Sales ON Dispatch.Invoice = Sales.Invoice

    /*AND (Employee.EmpNo = '9003' OR

    Employee.EmpNo = '9005' OR

    Employee.EmpNo = '9006' OR

    Employee.EmpNo = '9026' OR

    Employee.EmpNo = '9014' OR

    Employee.EmpNo = '9000' OR

    Employee.EmpNo = '9015' OR

    Employee.EmpNo = '9019' OR

    Employee.EmpNo = '9991')

    */

    Where(Dispatch.CustNo <> '0000011' AND Dispatch.CustNo <> '0005209') AND

    Dispatch.Invoice <> '' AND

    Dispatch.Complete IS NULL AND

    Dispatch.RecDate < '2011-07-01'

    /* AND (DispTech.DispDate IS NULL OR DispTech.DispDate < '2011-06-01') */

    Transaction

    /*select *

    */

    USE Test2

    DECLARE @intErrorCode INT

    BEGIN TRANSACTION

    /* UPDATE DISPATCH*/

    UPDATE view_set_DispInvNoComp

    SETComplete = InvDate

    Where NOTYs = '1' AND DispTechComplete <> 'Y'entries = '1' AND

    (EmpNo = '9003' OR

    EmpNo = '9005' OR

    EmpNo = '9006' OR

    EmpNo = '9026' OR

    EmpNo = '9014' OR

    EmpNo = '9000' OR

    EmpNo = '9015' OR

    EmpNo = '9019' OR

    EmpNo = '9991') AND Dispatch = '57693'

    SELECT @intErrorCode = @@ERROR

    IF (@intErrorCode <> 0) GOTO PROBLEM

    /* UPDATE DISPTECH*/

    UPDATE view_set_DispInvNoComp

    SET[Status] = 'Complete',

    DispTechComplete = 'Y',

    DispDate = InvDate,

    DispTime = '12:00:00',

    DateOff = InvDate,

    TimeOff = '12:00:00',

    SortDate = InvDate,

    SortTime = '12:00:00'

    Where entries = '1' AND

    (EmpNo = '9003' OR

    EmpNo = '9005' OR

    EmpNo = '9006' OR

    EmpNo = '9026' OR

    EmpNo = '9014' OR

    EmpNo = '9000' OR

    EmpNo = '9015' OR

    EmpNo = '9019' OR

    EmpNo = '9991') AND Dispatch = '57693'

    SELECT @intErrorCode = @@ERROR

    IF (@intErrorCode <> 0) GOTO PROBLEM

    COMMIT TRANSACTION

    PROBLEM:

    IF (@intErrorCode <> 0) BEGIN

    PRINT 'Unexpected error occurred!'

    ROLLBACK TRAN

    END

  • That changes nothing.

    What are you trying to debug?

  • I added the view to the previous post.

    I ran this as a test, and the first update made its changes and the second one did not, so I pose the question, since the first update modifies one of the filtering criteria of the view, which would in turn eliminate any updates from happening on the second update.

    I may have just missunderstood the explinations of how one could use a transaction.

  • Yes the first update changes the data and there's nothing left to do in the 2nd one.

    You need to do both updates in the same statement.

  • so...

    I guess how do I do that, subquery the second update at the end of the first?

  • Something like that should do it.

    UPDATE view_set_DispInvNoComp

    SET Complete = CASE WHEN NOTYs = '1' AND DispTechComplete <> 'Y' THEN InvDate ELSE Complete END,

    [Status] = 'Complete',

    DispTechComplete = 'Y',

    DispDate = InvDate,

    DispTime = '12:00:00',

    DateOff = InvDate,

    TimeOff = '12:00:00',

    SortDate = InvDate,

    SortTime = '12:00:00'

    Where entries = '1' AND

    (EmpNo = '9003' OR

    EmpNo = '9005' OR

    EmpNo = '9006' OR

    EmpNo = '9026' OR

    EmpNo = '9014' OR

    EmpNo = '9000' OR

    EmpNo = '9015' OR

    EmpNo = '9019' OR

    EmpNo = '9991') AND Dispatch = '57693'

  • I do believe this was the first thing I tried, but was under the impression that even in the view I still could not update to (* typo here, I meant two *) tables in the one statement like that.

    I will give it a try though.

  • amos-870870 (12/5/2011)


    I do believe this was the first thing I tried, but was under the impression that even in the view I still could not update to tables in the one statement like that.

    I will give it a try though.

    Why not?

    I haven't done this since access back in 2000 so I'm a little rusty here.

  • Complete is part of a different table that the rest of columns.

    View or function 'view_set_DispInvNoComp' is not updatable because the modification affects multiple base tables.

  • That to was suppose to be two in that post you quoted me on. I edited that post to reflect that.

    Anyhow here are the results...

    View or function 'view_set_DispInvNoComp' is not updatable because the modification affects multiple base tables.

  • amos-870870 (12/5/2011)


    That to was suppose to be two in that post you quoted me on. I edited that post to reflect that.

    Anyhow here are the results...

    View or function 'view_set_DispInvNoComp' is not updatable because the modification affects multiple base tables.

    Can you reverse the updates?

    If not the only idea I have is the do 1 update, output into #tmp

    Then run the second updated based on the #tmp keys.

  • Ninja's_RGR'us (12/5/2011)


    amos-870870 (12/5/2011)


    I do believe this was the first thing I tried, but was under the impression that even in the view I still could not update to tables in the one statement like that.

    I will give it a try though.

    Why not?

    I haven't done this since access back in 2000 so I'm a little rusty here.

    Couldn't do it in 2k either, but a lot of folks had trouble with this and it's rarely done, so don't feel too bad.

    Short version: You cannot update multiple tables in a single update statement. If that update statement works against an updateable view you can only update one of the sub-tables at a time.

    Ninja's correct, when you update one of the tables, you must expect that information to be updated (even within a transaction, since you're within your own transaction) when you do the second update. I definately would transactionalize this process.

    The basic plan for this is usually update the primary table, noting any pertinent identification changes in the subtables in a local #tmp for reference, and then updating any subtables in the chain. It's a royal pain and it's why a lot of folks avoid updatable views.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Reverse the updates?? Is there some tactick you refer to?

    This was done to 1 test record in a test DB before I unleashed it on my live DB, if that is what you were refering to.

    Yea that was what I was thinking originally, using temp tables, I thought there would be a more efficient way of accomplishing this. I guess I was wrong.

Viewing 15 posts - 1 through 15 (of 26 total)

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