How to automatic update view when table's structure has changed

jeRKer

For example:

Create view View_AutoUpdate as

(

select a.*,b.[name] from User as a,Company as b where a.CompanyID=b.CompanyID

)

Done,but when i do it follow:

Alter table User add UserAddress nvarchar(50) null

Done.

finally,i type "select * from View_AutoUpdate ",the list column is not right.

thanks

Geert Verhoeven

Hi,

You can do this by implementing a DDL Trigger on the User Table. This trigger will then recreate your view.

Info on DDL Triggers: http://msdn2.microsoft.com/en-us/library/ms175941.aspx

Greetz,

Geert

Geert Verhoeven
Consultant @ Ausy Belgium

My Personal Blog

Asvin Ananthanarayan MSFT

After you alter the table,

exec sp_refreshview 'view_name'.

This should update the view definition to include the newly added table column.

HTH

jeRKer

thank you very much

Umachandar Jayachandran - MS

Please don't use * as SELECT list in views or inline TVFs or even SPs for that matter. It will create lot of problems to applications. For example, the column order can be significantly different when you alter the table (depends on how you do it) and this can break client applications. You can also encounter problems where the data type of the columns have changed if you hadn't refreshed the view. This can cause exceptions in the client and server code depending on what you are doing with the view. It is also not good from performance perspective to send all the columns to the client. You should only send the required data and even if there is a case where you need to send all columns you should list those explicitly.

jeRKer

thanks

jeRKer

thank you for advice and explain