Work Around / Avoid "Update or insert of view or function failed because it contains a derived or constant field."

[Kristen]

I have a VIEW with a derived column; the VIEW is only in place if there is an earlier version of the table (the view simulates the renamed table and some additional columns) which newer reports expect to find. I have one generic SProc that has a rarely used option to INSERT a row into that table, I'm happy that that will not work with the VIEW in place, so I want a conditional INSERT into that "Table / View" that does not execute if it is a VIEW rather than a TABLE.

(There is a separate upgrade path where a PATCH updates MyOldTableName to MyNewTableName, renames some column names and adds the extra columns; The view is a stop-gap until the Patch is applied at some future point)

I tried some conditional code:

IF @SomeParameter IS NOT NULL
	AND EXISTS (SELECT * FROM sys.tables AS T WHERE T.name = 'MyNewTableName')
BEGIN
	INSERT INTO dbo.MyNewTableName
	(
		NewCol1Name, NewCol2Name, ...
	)
	SELECT
		NewCol1Name	= 1
		, NewCol2Name	= @SomeParameter
	 	, ...
END

but I get the

"Update or insert of view or function 'Mytable' failed because it contains a derived or constant field."

when I execute the SProc, regardless of whether @SomeParameter IS NULL (and

SELECT * FROM sys.tables AS T WHERE T.name = 'MyNewTableName'

definitely returns zero rows, 'coz [MyNewTableName] is actually a VIEW)

Is the only way around this to use an EXEC MySProc to do the INSERT?

The VIEW looks like this:

CREATE VIEW dbo.MyNewTableName
WITH SCHEMABINDING
AS
SELECT
	[NewCol1Name]		= OldCol1,
	[NewCol2Name]		= OldCol2,
	...
	[NewColX]	= CONVERT(int, 5),
	[NewColY]	= CONVERT(varchar(100), NULL)
FROM	dbo.MyOldTableName

[gbritton]

In your sproc, insert into the table, not the view

[Kristen]

Yes, that would work, but i don't want to do that (in that way).

The INSERT is INSERT INTO NewTableName, there was an earlier version of the SProc that was INSERT INTO OldTableName ... but I don't really want to maintain two versions (this is a very rare thing) ... so I was using teh VIEW to "fake" the NewTableName (and then when the PATCH is rolled out the NewTableName VIEW will be dropped and the OldTableName renamed to NewTableName), so I was looking for a way of allowing this code to be in the SProc, but to disallow execution (if NewTableName does not exist)

[stephen_hendricks]

If I'm understanding things correctly, the error occurs at compile time. Even though the IF condition will stop the code from being executed, the compiler is still enforcing the no update rule. I'll suggest two possible workarounds:

1. Dynamic SQL inside the IF's BEGIN-END (yuck)
2. Use a Synonym to define which table to operate on instead of a View. You'll still have a common name for the object but without the limitations of the View.

[ScottPletcher]

I don't suppose an "INSTEAD OF INSERT" trigger resolves this problem?

[Kristen]

@stephen_hendricks

If I'm understanding things correctly, the error occurs at compile time

Yes, I reckon that's it. The CREATE works fine, but I reckon something must be deferred until runtime

1. Agree - Yuck!
2. That would be a much better solution than using a VIEW instead of a table (feels "cleaner" and easy to jsut REMOVE the SYNONYM when the PATCH runs, which i think is cleaner than having to check if there is a VIEW / TABLE and then deciding which method to do). However, I also have Column Name renames ... so can't catch those with a SYNONYM

@ScottPletcher

I don't suppose an "INSTEAD OF INSERT" trigger resolves this problem?

Yup, definitely would. I had thought of that and discounted it, but actually if I create an INSTEAD OF TRIGGER when I make the "Forward-Compatibility" VIEW then anything that wants to insert into the NewTableName will actually Work Just Fine.

I'm still a bit torn with whether I should replace the INSERT with an EXEC MySProc. That seems to me to be OK provided that there are literally just one or two instances, but if it turns out to be more then INSTEAD OF TRIGGER would be a much better solution.

For now I have commented the code out ...

... needs a proper solution though.

[Kristen]

Can't find anything appropriate in Google using:

SQL using VIEWs for "forward-compatibility"

anyone suggest better keywords pls?

[ScottPletcher]

A trigger is by far the "cleanest" solution. I know that a lot of developers aren't comfortable with triggers, but they're actually much more consistent than coding. Gasp! Yes, I actually said it, developers let in a lot of inconsistencies in their code that could be resolved by using a trigger(s) instead.

[jeffw8713]

Just thinking out loud...

What happens if you put those derived columns on the 'old' table and removed the view altogether? Are they really as simple as you have shown - or are they complex enough to prevent you from creating them as derived columns on the table directly?

If your code has to reference the new table name - then keeping the old table, adding derived columns directly - then using a synonym for the 'new' table should resolve the issue.

[Kristen]

Can't change the DDL for the table (unless I upgrade the database by rolling out with the latest update version) because we go through a full QA cycle before any changes to Tables / Columns are allowed (existing code may depend on the tables being the way they were), so where we want to rollout (piecemeal) an SProc, which needs some DDL changes, we work around that by having a forward-compatibility VIEW.

Its a PITA though ... only comes about with Clients that don't have a maintenance contract (which would have gotten their system updated to Latest Version, so would have avoided the problem in the first place) and then that Client wants "a really simple report", or somesuch, which we reckon we can easily do (e.g. we have something that "almost does that already" and thus only requires a small change) ... and THEN we discover that that code is dependent on some new improvement we made and needs a DDL change ... and so on.

At that point we should just shoot the client ...

It normally goes something like this:

"I want to add these TWO new Columns" ... and (maybe) "Actually that table uses the old column-naming convention, if we are adding two columns let's fix up the column names too".

So:

Create a Script to do the Column renames, ADD the new columns and rename the Table from MyTable_V1 to MyTable_V2. Add that script to the "Next Version Rollout Package"

Create a backward-compatibility VIEW for MyTable_V1 - basically just:

CREATE VIEW MyTable_V1
AS
SELECT NewCol1 AS OldCol1,
      Col2, 
      ... 
FROM MyTable_V2

So all the existing code will work (because it will, now, reference the MyTable_V1 View instead of the MyTable_V2 Table)

Then we change any Sprocs /Triggers / etc. to use MyTable_V2. MAYBE all code referencing MyTable_V1 gets Found and Fixed (but MAYBE we decide to only fix part of it). When there are no longer any references to MyTable_V1 we add a DROP VIEW MyTable_V1 to the rollout script for the next release.

At some point we do a Full QA cycle, and build a Release Package. We can then roll out the Release Package to all clients that are entitled to it.

THEN ... we have to do some work for a client that hasn't had the rollout. So THEN we create a Forward-Compatibility VIEW

CREATE VIEW MyTable_V2
AS
SELECT OldCol1 AS NewCol1,
   Col2,
   ...
   CONVERT(int, 1234) AS SomeNewColumn
FROM MyTable_V1   

Normally I would not expect anything (which needed the new table) to want to INSERT to it - that's going to almost certainly need the "real" new Columns ... so at that point the Client would have to upgrade to the new version.

But in this case there is indeed an INSERT in the Sproc, but it is definitely not going to be called, so I want to leave the INSERT in (rather than create a bespoke, one-off, version of that SProc for that client) ... but SQL doesn't like it (as per my O/P)

Simple answer, as @ScottPletcher said, is to put an INSTEAD OF TRIGGER on my forward-compatibility MyTable_V2 view ... then in the event that the SProc DID execute the INSERT statement all would be well ...

... but because this is just String & Gum to Elastoplast this Client until they stump-up for the upgrade I was trying to do as little work as possible.

Now you lot can stop rolling around on the floor laughing, at my expense, and get on with some real work!

[Kristen]

Actually I had a thought ... given that the INSERT, to the forward-compatibility MyTable_V2 view should never happen, the INSTEAD OF TRIGGER could just do a RAISERROR / ROLLBACK. I haven't actually got to write any code to perform the INSERT at all (well ... UNTIL I find that there IS a need to Insert into the forward-compatibility view).

That's a simple enough job that I will be happy to do that