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?
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...
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.
>> 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.