asked
What triggers and indexes can be enabled on a view?
Hi All, I would like to know, what are all the triggers that can be enabled on a view and also should there be a unique clustered index first on the view to created other indexes??
@swethaashwini Adding a clustered index on the view will materialize it (actually harden its data). This means that from this moment, updates, inserts and deletes on the underlying table will cause the similar under the hood DML operations on the hardened rows of the materialized view. Practically, this means that DML operations on the underlying table will take a bit longer to execute :)
You can only use `instead of` triggers on views, can you post your code please? [
http://msdn.microsoft.com/en-us/library/ms189799.aspx][1] > table | view Is the table or view on > which the DML trigger is executed and > is sometimes referred to as the > trigger table or trigger view. > Specifying the fully qualified name of > the table or view is optional. A view > can be referenced only by an INSTEAD > OF trigger. DML triggers cannot be > defined on local or global temporary > tables. [1]:
http://msdn.microsoft.com/en-us/library/ms189799.aspx
A view is just a query. It's not a table on the database, although it appears to behave as one. @Fatherjack has already hit it, you can't create indexes or triggers, except INSTEAD OF triggers, on a view. You can't even create a clustered index ON a view. You can create a clustered index that references a view, this what is known as a materialized view or an indexed view. An indexed view is simply a view that has been processed, meaning, all it's data has been retrieved, and is stored within a clustered index. You need to be very careful when using these. They work well with slowly or unchanging data. If you have volatile data, they can be very problematic.
thanks for covering the index part of this, forgot it while I was typing the trigger info! doh!
No worries. I almost fealt like I was piling on, but I wanted to get the definition in there because I suspect better understanding is needed here.
heck, never be afraid of adding something, always happy to have my I's crossed and T's dotted. ;)
Thank you guys, i have got a clear picture with your explanations.
