Post reply

Updating a view with an INSTEAD OF trigger

  • June 8, 2009 at 5:44 pm

    #205992

    Hi all...I need your assistance here!

    I need to update a view. It has an UPDATE trigger (INSTEAD OF) on it. Of course, the data that I need to use to update it has to be from joined tables! Problem #1. I understand from reading a couple of articles today that joins are a NO NO on updatable views. So, I tried doing the update statement like the example shows and it still isn't updating. I will give you an example of my code if you can please tell me where I am going wrong!! I have a query of all the information I need in a temp table called #TEMP and I am using it for this update:

    UPDATE

    CustomElementAttribute

    SET CustomAttributeValue =

    (Select PersonName

    FROM #TEMP AS t

    WHERE

    (EnterpriseUserID = t.EnterpriseUserID

    AND CustomAttributeKey = 'NICKNAME'

    AND t.PersonName <> CustomAttributeValue))

    WHERE EXISTS (SELECT *

    FROM #TEMP AS t

    WHERE t.EnterpriseUserID = EnterpriseUserID)

    thank you in advance! I always appreciate your input!! 😛

    Thank you!!,

    Angelindiego

  • June 8, 2009 at 6:33 pm

    #1006377

    Could you also provide the Indexed View definition and the related table definitions?

    Angelindiego (6/8/2009)

    Hi all...I need your assistance here!

    I need to update a view. It has an UPDATE trigger (INSTEAD OF) on it. Of course, the data that I need to use to update it has to be from joined tables! Problem #1. I understand from reading a couple of articles today that joins are a NO NO on updatable views.

    ...[/quote]

    This doesn't seem quite right. Could you provide a link(s) to these so that we can double-check what the concerns are?

    Thanks,

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 2 posts - 1 through 1 (of 1 total)

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