It's supposed to be a little better in SQL2K, but at least in SQL7 it's even
worse than this:
Given this structure
TABLE x
column a
column b
column c
TABLE y
column x
column y
column z
create view myview as
SELECT x.a, x.b, y.z from x
inner join y on y.x = x.a
go
In my experience / testing, the view "myview" will have to be resaved any
time a column is added to or removed from either table x or table y, even if
myview doesn't reference that column in any way. This has to do with the way
that SQL server references columns in tables and views with an ordinal
number ( i.e. column 3 instead of column "x" ). The fact that these columns
need not be sequential if a column is removed from the middle doesn't seem
to matter either, so, if you had a table with 5 columns and you removed
column 4, the table would then have columns 1-3 and 5, but you would still
have to update any views which referenced that table, even if those views
didn't reference columns 4 or 5 in any way. ( Don't ask me why it's all
cluster-fucked like this, I haven't got a clue ).
The solution that some sql folks have come up with is to check for dependant
views when a column is added or removed from a given table and update all
those dependant views using a stored procedure. If you have full control of
the sql server, you can even go so far as to create an insert/delete trigger
on the syscolumns table so that you don't have to manually run the stored
procedure to update the views. The problem you may run into, however, is
that if you have a view which explicitely references a column that you've
removed from the table, the attempt to update the view will result in an
error and you'll lose the view and have to recreate it manually.
Here are the sp's I use to grab view data from the syscomments table and
updat them.
CREATE VIEW dbo.tap_ViewsDependant AS
SELECT DISTINCT t2.name AS viewname, t.name AS dependson
FROM sysobjects t
inner join syscolumns c ON ( c.id = t.id )
inner join sysdepends dep on ( dep.depid = c.id )
inner join sysobjects t2 on ( t2.id = dep.id )
where objectproperty(t2.id, N'IsView') = 1
and (objectproperty(t.id, N'IsView') = 1
or objectproperty(t.id, N'IsUserTable') = 1)
GO
create PROCEDURE dbo.tap_ViewUpdate
@viewname nvarchar(128)
AS
DECLARE @sysid INT;
DECLARE @view NVARCHAR(4000);
DECLARE @dropview NVARCHAR(200);
SET @sysid = (SELECT ID FROM sysobjects WHERE name = @viewname
AND OBJECTPROPERTY(ID, N'IsView') = 1)
IF @sysid IS NOT NULL BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
SET @view = (SELECT VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = @viewname);
SET @dropview = 'DROP VIEW ' + @viewname;
EXEC sp_executesql @dropview;
EXEC sp_executesql @view;
COMMIT TRANSACTION;
END
GO
create PROCEDURE dbo.tap_ViewsUpdateDependant
@tablename nvarchar(128)
AS
DECLARE @currentview nvarchar(128);
DECLARE curView CURSOR LOCAL FAST_FORWARD FOR
SELECT DISTINCT viewname
FROM dbo.tap_ViewsDependant
WHERE dependson = @tablename
OPEN curView;
FETCH NEXT FROM curView INTO @currentview;
WHILE (@@FETCH_STATUS = 0) BEGIN
EXEC dbo.tap_ViewUpdate @viewname = @currentview;
FETCH NEXT FROM curView INTO @currentview;
END
CLOSE curView;
DEALLOCATE curView;
GO
so after you change the columns in your "employee" table, you would then run
EXEC dbo.tap_ViewsUpdateDependant @tablename = 'employee';
and this would update any views that depend on your employee table
To make this a trigger you'd have to modify the view dbo.tap_ViewsDependant
to include the id of the table from the sysobjects table and use that to
reference the id in the syscolumns table to check for dependancies in your
insert/delete trigger on syscolumns...
hth
> Les,
> I have also seen this. It's related to using "*" in the
> view, and then
> changing what "*" means by adding or removing columns in a
> table called by
> the view.
> To avoid it in the future, take the time to write out the
> specific fields
> you are calling. Otherwise, when you add/remove a field
> from the original
> table, you will have to go back into the view and just
> re-save it.
> If I had to pin down a cause, I would say that when you
> save the view, it
> caches the fields (specifically the location of the fields
> in relation to
> their table) that map to "*", and doesn't re-examine them
> until you save
> again.
> Think of it as a list. If you have a list "listA =
> 1,2,3,4,5" and call
> listGetAt(listA,3), it returns 3. then you delete item 2,
> and
> listGetAt(listA,3) will return 4. Specifically naming the
> fields will
> remove the ambiguity, and call the field directly rather
> than a reference
> to the field.
> -Jeff
>> Date: Mon, 16 Dec 2002 14:18:04 -0500
>> From: "Les Mizzell" <[EMAIL PROTECTED]>
>> Subject: Slightly OT: SQL VIEW gets out of sync??
>>
>> Anybody ever seen this before...
>>
>> Got a View set up in a SQL Database using inner
>> joins...which is then
>> displaying data on a CF page. View looks like:
>>
>> SELECT app_Master.*, app_register.*, app_1.*, app_2.*,
>> app_3.*,
>> app_4.*, app_5.*
> *snip*
>> I'm not calling any specific fields in any tables....
>>
>> All works fine until I go into a specific table in the
>> SQL Administrator
>> any add or change a row to the table. If I now look at my
>> CF display
>> pagencalling the view, the displayed stuff will be "off"
>> - say, last
>> name is now displaying where the first name should be or
>> something - the
>> SQL View has gotten data "out of sync" somehow.
>>
>> Only way I can fix it is to go back into the SQL
>> administrator and
>> resave the view.
>>
>>
>> Ideas?
s. isaac dealey 954-776-0046
new epoch http://www.turnkey.to
lead architect, tapestry cms http://products.turnkey.to
certified advanced coldfusion 5 developer
http://www.macromedia.com/v1/handlers/index.cfm?ID=21816
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
