Solved

URGENT: Updating a view with a computed column

Posted on 2006-06-30
9
486 Views
Last Modified: 2012-06-27
Hello All,

We're currently using views as the connection points to key tables in other database on the same database server.  This is done to make development and management easier and to ensure that should any table location need to be updated, it can be done all from the one place.  For example:

DB1 has a table call 'Products'  (dbo.Products)
DB2 has a stored procedure which requires the data in Products.  Instead of connecting to products by DB1.dbo.Products we have create a view which connects as DB1.dbo.Products and then in the stored procedures, we reference the local view as dbo.Products.  This ensures that if Products was ever to move or be renamed we would only need to update the view to ensure consistency.

(NB: I note that this may not be many dba's preference in working methods, please keep in mind that this is a snap shot of a setup where in we have a few hundred databases and many thousands of stored procedures all relational, so management needs to be kept as simple as possible and movement of some/many tables will be common as data expands across more servers in the setup)

The issue that has come to hand is this, one of the tables we reference remotely, needs to be updated, only on two columns.  Once of these columns participated in a 'Computed' column in the source table.  This is what appears to be the cause of this error:  

Server: Msg 4406, Level 16, State 2, Procedure UpdateTest001, Line 1078
Update or insert of view or function 'dbo.Products' failed because it contains a derived or constant field.

We do not use any triggers on the table and this single computed column is very much a required item, so I would like to ask if there is anyone out there who may have came across this issue before and/or anyone who may have a work-around for this issue.

Thanks in advance.
Anubis.
0
Comment
Question by:Anubis2005
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 50 total points
ID: 17022610
I guess you will have to put 2 views, 1 for the viewing of data, and 1 for updating the data, latter will not contain the computed field.
0
 
LVL 12

Expert Comment

by:Einstine98
ID: 17022625
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.
0
 
LVL 1

Author Comment

by:Anubis2005
ID: 17022699
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.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 12

Accepted Solution

by:
Einstine98 earned 400 total points
ID: 17022727
CREATE Trigger MyTriggerName on products
INSTEAD OF UPDATE AS
BEGIN
  UPDATE P
  SET ProductOnHand = i.ProductOnHand
  FROM <serverName>.<DatabaseName>.dbo.Product AS P
  JOIN INSERTED AS I
  ON i.productid = p.productid

END
0
 
LVL 1

Author Comment

by:Anubis2005
ID: 17023683
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.
0
 
LVL 12

Expert Comment

by:Einstine98
ID: 17024558
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?
0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 50 total points
ID: 17024977
actually its only the insert that is a problem,,,

where you get the message about the derived column...

an update statement in which you don't specify the derived column works ok...

select * from products

update products
 set productonhand = 2
where productid = 1
select * from products
insert into products (productid,productonhand,productsold) values(2,1,0)
select * from products

which version/edition of SQL server are you using?
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 17024979
you could always have the "local" stored procedure execute a "remote" stored procedure to do the update...
0
 
LVL 1

Author Comment

by:Anubis2005
ID: 17025000
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.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question