Results 1 to 12 of 12
  1. #1
    Join Date
    Nov 2003
    Posts
    5

    Question Unanswered: Problem with INSTEAD OF UPDATE trigger

    Maybe this is a silly question, but I am not used to SQL Server.

    I have a view where I put an INSTEAD OF UPDATE trigger. In this trigger, I have to deal with the two trigger tables: inserted and deleted. My problem is that I don't know what to do when an UPDATE command changes more than one row in my view. In this case, both inserted and deleted tables have more than one row. How can I know which inserted record correspond to a given deleted record?

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    join them on the primary key.

  3. #3
    Join Date
    Nov 2003
    Posts
    5
    Originally posted by nigelrivett
    join them on the primary key.
    But what should I do when the primary key is changing? In this case the PKs in 'inserted' and 'updated' will not match.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...yet another reason to use artificial primary keys...

  5. #5
    Join Date
    Nov 2003
    Posts
    5
    I am used to Oracle triggers. In Oracle, you can create a FOR EACH ROW trigger, which enables you to match the :new and ld records. Is is very easy, and doesn't require an artificial PK.

    I don't want to add a new column to my view just to deal with this limitation of SQL Server. Is there another alternative? Maybe there is some function that helps us to match the records, but I still could not find one...

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by blindman
    ...yet another reason to use artificial primary keys...
    Not another "surrogate" for the use of IDENTITY...

    Oh the humanity....


    Why do you need know? What's the code doing...why can;t this be done the procedure that makes the update? (because you allow dynamic sql, right)

    If you post the trigger, maybe we can come up with some ideas...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    That may be easier, but it is essentially a cursor and could not be as efficient as a set-based operation against a true primary key.

    You could try to match on another set of columns that constitues a natural key.

    ...but you should avoid updates to views anyway. It is better programming practice (for many reasons) to handle input and output from tables through stored procedures. Most of the views I've seen created were made by developers who had stepped up from MS Access and its saved querys. Most experienced dbas I've met strongly perfer to use stored procedures.

    blindman

  8. #8
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    >> But what should I do when the primary key is changing? In this case the PKs in 'inserted' and 'updated' will not match.

    A PK is a record identifier. If you update it (possibly an indication of poor choice of PK) then this is a logical delete/insert so failing to match rows is correct.
    If you carry out the physical delete and insert for this then you won't have this problem otherwise anything unmatched as a deleted record and and inserted record in the trigger.

  9. #9
    Join Date
    Nov 2003
    Posts
    5
    My problem is that I am dealing with legacy code, that uses a table that does not exist anymore. The old table was replaced by my view. I can't (and don't want to) change the legacy code. It uses (yes) a lot of dynamic SQL, that makes changes also in the PK. I wanted to make the view work transparently, just like the old table. The view has a lot of joins, so I cannot just create an updateable view. When the user changes a record in the view, he is really changing records in two or three distinct tables.

    My application uses both Oracle and SQL Server databases. I could solve the problem easily in Oracle, but don't know how to do it in SQL Server.

  10. #10
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    Unfortunately sql server has less features that contravene rdbms rules so is more suseptable to problems due to bad design.

    Don't think there is much you can do about this other than correct the implementation.

    You could traverse the inserted and deleted tables row by row (or insert the recs into temp tables with identities) and hope that the record order obtained corresponds to the matching records but there is no guarantee that this will always work even if it happens to on some instances.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I understand your use of the temporary table to avoid having to rewrite a ton of code. That makes sense in many instances.

    You will still need to add a unique column to your table, which other procedures do not even need to be aware of, and which you can use for matching up records. This is your best option.

    blindman

  12. #12
    Join Date
    Nov 2003
    Posts
    5
    Yes. I created a new column in my view, a copy of the PK that is not changed by the legacy code. This will be my new unique ID in the trigger.

    Quick and dirty, but it solves the problem. Thanks a lot for the suggestion.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •