Post reply

Last Modified Trigger in Management Studio Edit Mode

  • July 30, 2009 at 10:20 am

    #385091

    Hi,

    I'm trying to use triggers to maintain a "Last Modified" column in various tables. I use the code below to create the trigger:

    CREATE TRIGGER trVendorsModified ON Vendors

    FOR UPDATE

    AS

    UPDATE Vendors SET Vendors.Modified=getdate()

    WHERE kpVendorID IN (SELECT kpVendorID FROM Inserted)

    GO

    So everything works fine if I modify rows via SQL statements. But when I enter Edit mode in SQL Server Management Studio and try to change something there, the trigger fails and will not allow me to commit the change. The error is:

    "The row value(s) updated or deleted do not make the row unique or they alter multiple rows(2 rows)"

    I assume that the error has to do with multiple rows (since the error says 2 rows) and that it's related to that new row filled with NULLs that the editor has at the end? Anyway, can I change my trigger so that it works both within the editor *and* using regular SQL? Or another approach?

    Thanks

  • July 30, 2009 at 10:25 am

    #1032514

    dunno if it will make a difference, but instead of using IN()

    i always use a join for the inserted table instead:

    UPDATE Vendors

    SET Vendors.Modified=getdate()

    FROM INSERTED

    WHERE Vendors.kpVendorID = INSERTED.kpVendorID

    in that situation, i think that makes it a clear 1:1 update for the compiler for which row was updated fromt he INSERTED table, so that should fix your issue.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 30, 2009 at 10:36 am

    #1032524

    Lowell, you rock! That works like a charm... Thanks so much

  • July 30, 2009 at 10:57 am

    #1032540

    glad i could help and thanks for the feedback!

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 30, 2009 at 11:32 am

    #1032561

    ???

    Weird. I cannot figure how this would make any difference? Nothing wrong with you code, Lowell, it just shouldn't fix a "non-unique" problem. ...

    [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]

  • August 3, 2009 at 3:03 pm

    #1033925

    Hi Lowell and RBarry,

    A little caveat that may shed some light into the why of things (??) When the join column (in this case kpVendorID) is a Primary Key, everything works fine. But in other tables, the join column is unique but not defined as a primary key. In those cases, the editor bombs again - same error...trying to modify two rows. This is a bit bothersome for my many-to-many join tables wherein I already have a unique key (the two foreign keys from the parents) and don't *really* want to add another column just to have a single defined primary key. For now I'll do that, but would appreciate any further insight as to *why* this is happening so as to come up with a better solution. Thanks,

    Bob

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

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