Today, I will be looking at how INSTEAD OF TRIGGERS can be used to make our VIEW correctly work with a DELETE statement. I will be using the [AUTOS] sample database for this demonstration. The [VW_JOIN_MAKES_2_MODELS] view will be enhanced with a instead of trigger so that delete action can be perform on two tables at the same time. The enclosed script has code to perform the following actions in the AUTOS database. 5A – Remove aggregate view 5B – Reload tables with good data 5C – Create a instead…
Tag: USER DEFINED VIEW
Instead of Insert
Most information technology professionals do not know that data can be modified via a VIEW. The INSERT, UPDATE, and DELETE statements of the Data Manipulation Language (DML) can be executed on a view as long as the following set of rules are adhered to. Modifications must reference columns from only one base table. The columns must directly reference the underlying data table. I am going to continue my exploration of updatable views. Last time, we were using the [AUTOS] sample database and discovered that DELETE statements were not working the…
User Defined Views – Part 4
Most information technology professionals do not know that data can be modified thru a VIEW. The insert, update, and delete statements, Data Manipulation Language (DML), can be executed on a view as long as the following set of rules are adhered to. Modifications must reference columns from only one base table. The columns must directly reference the underlying data table. Today, I am going to explore how to modify data in a view using the [AUTOS] sample database. Enclosed is the complete TSQL for this article including the…
User Defined Views – Part 3
Today, I want to continue talking about database objects that are associated with stored (compiled) code. A view can be though as a virtual table whose contents are defined by a TSQL query based on one or more tables in the database. Typical uses of a view are the following: To simplify or customize the perception each user has of the database. Security mechanism to grant users access to the view, not the underlying base tables. To provide a backward compatible interface to emulate a table whose schema has…
User Defined Views – Part 2
Today, I want to continue talking about database objects that are associated with stored (compiled) code. A view can be though as a virtual table whose contents are defined by a TSQL query based on one or more tables in the database. Typical uses of a view are the following: To simplify or customize the perception each user has of the database. Security mechanism to grant users access to the view, not the underlying base tables. To provide a backward compatible interface to emulate a table whose schema has…
User Defined Views – Part 1
Today, I want to continue talking about database objects that are associated with stored (compiled) code. A view can be though as a virtual table whose contents are defined by a TSQL query based on one or more tables in the database. Typical uses of a view are the following: To simplify or customize the perception each user has of the database. Security mechanism to grant users access to the view, not the underlying base tables. To provide a backward compatible interface to emulate a table whose schema has…