Explain how to make views updatable in SQL Server 2000[Hint - using Instead of Triggers and Partitioned views].
INSTEAD OF Triggers: These can be created on a view to make a view updateable. The INSTEAD OF trigger is executed instead of the data modification statement on which trigger is defined. This trigger enables the user to provide set of actions that must take place to process data modification statement. Partitioned views: It’s a view defined by a UNION ALL of member tables structured in same way but stored separately as multiple tables in either same SQL Server or multiples ones (federated database servers).
Explain how to make views updatable in SQL Server 2000[Hint - using Instead of Triggers and Partitioned views].
INSTEAD OF Triggers: Cause the trigger to fire instead of executing the triggering event or action. It prevents unnecessary changes to be made.
Example: Causes the trigger to fire instead of the update (action)
CREATE TRIGGER Employee_update ON Employee INSTEAD OF
UPDATE AS
{
TRIGGER Definition
}
Partitioned view:Partitioned view joins the horizontally portioned data. This data may belong to one ore more servers. It makes the data appear as one table. A portioned view can either be local or distributed. A local portioned view resides on the same instance of the SQL server while the distributed may reside on a different server.
Syntax: The view is then created by UNIONing all the tables and an updateable partitioned View results
Server 1 :
CREATE TABLE Customer1
(CustomerID INTEGER PRIMARY KEY
CHECK (CustomerID BETWEEN 1 AND 32999),
... -- Additional column definitions)
Similar tables created for Server 2 and 3
Partitioned view for server 1
CREATE VIEW Customers AS
SELECT * FROM CompanyDatabase.TableOwner.Customer1
UNION ALL
SELECT * FROM Server2.CompanyDatabase.TableOwner.Customer2
UNION ALL
SELECT * FROM Server3.CompanyDatabase.TableOwner.Customer3
