URGENT: Updating a view with a computed column

You need to create a "Instead of" trigger on thate view and when the update or insert operation is done (or even delete) the trigger will take over and do the update... portion of Books online help on this subject:

****QUOTE ***
INSTEAD OF UPDATE Triggers
INSTEAD OF UPDATE triggers can be defined on a view or table to replace the standard action of the UPDATE statement. Usually, the INSTEAD OF UPDATE trigger is defined on a view to modify data in one or more base tables.

UPDATE statements that reference views with INSTEAD OF UPDATE triggers must supply values for all nonnullable view columns referenced in the SET clause. This includes view columns that reference columns in the base table for which input values cannot be specified, such as:

Computed columns in the base table.


Identity columns in the base table for which IDENTITY INSERT is set to OFF.


Base table columns with the timestamp data type.
Usually, when an UPDATE statement that references a table attempts to set the value of a computed, identity, or timestamp column, an error is generated because the values for these columns must be determined by Microsoft® SQL Server™. These columns must be included in the UPDATE statement to meet the NOT NULL requirement of the column. However, if the UPDATE statement references a view with an INSTEAD OF UPDATE trigger, the logic defined in the trigger can bypass these columns and avoid the error. To do so, the INSTEAD OF UPDATE trigger must not try to update the values for the corresponding columns in the base table. This is done by not including the columns in the SET clause of the UPDATE statement. When a record is processed from the inserted table, the computed, identity, or timestamp column can contain a dummy value to meet the NOT NULL column requirement, but the INSTEAD OF UPDATE trigger ignores those values and the correct values are set by SQL Server.

This solution works because an INSTEAD OF UPDATE trigger does not have to process data from the inserted columns that are not updated. In the inserted table passed to an INSTEAD OF UPDATE trigger, the columns specified in the SET clause follow the same rules as the inserted columns in an INSTEAD OF INSERT trigger. For columns not specified in the SET clause, the inserted table contains the values as they existed before the UPDATE statement was issued. The trigger can test whether a specific column has been updated using the IF UPDATED(column) clause. For more information, see INSTEAD OF INSERT Triggers.

INSTEAD OF UPDATE triggers should use values supplied for computed, identity, or timestamp columns only in WHERE clause search conditions.

The logic an INSTEAD OF UPDATE trigger on a view should use to process updated values supplied for computed, identity, timestamp, or default columns is the same as the logic applied to inserted values for these column types.



Note  INSTEAD OF UPDATE triggers cannot be defined on a table that has a foreign key defined with an UPDATE action.

Thank you for the detailed responsed.

If the table (and subsequent view) ddl was similar to below, how would the INSTEAD OF trigger be written?



CREATE TABLE [dbo].[Products]            -- OBJECT CREATED IN DB1
      (
      ProductID      [int]      PRIMARY KEY,
      ProductOnHand      [int],
      ProductSold      [int],
      ProductAvailable AS ProductOnHand - ProductSold
      )


CREATE VIEW [dbo].[Products]            -- OBJECT CREATED IN DB2
AS
SELECT ProductID,
      ProductOnHand,
      ProductSold,
      ProductAvailable
FROM [DB1].[dbo].[Products]



UPDATE [dbo].[Products]            -- UPDATE PERFORMED IN DB2
SET ProductOnHand = (ProductOnHand + 20)
WHERE (ProductID = 1)



Thanks again.
Anubis.

Again, thank you for your reply.

As there are two potential columns that could be updated and both independently, would there be a major performance hit be performing a trigger something like the below?  The tables which this is upon can contain many millions of records, updates happening to singular records every second or every few hundred milliseconds.  Tables can also contain up to 80 columns (meaning there would be around 80 'CASE' statements in the trigger), or would it all be simpler using the suggestion by AngelIII?

Also, I would assume the same error could happen on inserts and delete statements?  Would I simply appeand the 'Instead Of' to be something like 'Instead Of Insert, Update, Delete' or do I need to define three triggers?


CREATE Trigger MyTriggerName on products
INSTEAD OF UPDATE AS
BEGIN
  UPDATE P
  SET ProductOnHand = CASE  WHEN UPDATE(i.ProductOnHand) THEN i.ProductOnHand ELSE P.ProductOnHand END,
    ProductSold = CASE WHEN UPDATE(i.ProductSold) THEN i.ProductSold ELSE P.ProductSold END
  FROM <serverName>.<DatabaseName>.dbo.Product AS P
  JOIN INSERTED AS I
  ON i.productid = p.productid

END


Thanks for your patience, triggers are the last aspect of MSSQL which I have not really had the change/need to use as yet so just making sure I get as good a base as I can.

Thanks.
Anubis.

As far as I know, but I'm not 100% sure (my memory is failing me now, and I don't have a system in front me to test) the INSERTED and DELETED tables will have "copies" of the changed records... so

if you update only ONE field, the other would values in the inserted table would be copies from the original table...

so effectively, you do not need the case statements (but please test this)...

Performance wise, of course a trigger will add a performance over-head, however, I don't think it's going to be that bad in this case, as no extra processing is done, it's the same update statement directed properly... so should be close to the performance without the trigger....

AngelIII suggestion should also work, but it depends on what can you change within your application? can you do selects from one view and inserts through another?

you could always have the "local" stored procedure execute a "remote" stored procedure to do the update...

I have ran a test based upon needing the case statement, and you are correct, the inserted table does contain a copy so the case statement is not needed.  With this, it is now working as expected.

Thank you all for your input!

Anubis.