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?join them on the primary key.|||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.|||...yet another reason to use artificial primary keys...|||I am used to Oracle triggers. In Oracle, you can create a FOR EACH ROW trigger, which enables you to match the :new and :old 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...|||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...|||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|||>> 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.|||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.|||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.|||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|||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.sql