Solved

URGENT: Updating a view with a computed column

Posted on 2006-06-30
9
483 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 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 50 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 12

Accepted Solution

by:
Einstine98 earned 400 total points
Comment Utility
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 1

Author Comment

by:Anubis2005
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
you could always have the "local" stored procedure execute a "remote" stored procedure to do the update...
0
 
LVL 1

Author Comment

by:Anubis2005
Comment Utility
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

762 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now