HOW TO USE UPDATABLE VIEWS IN MSSQL

An updatable view in a relational database, including Microsoft SQL Server, is a view that allows users to perform data modification operations such as INSERT, UPDATE, and DELETE on the underlying tables through the view. In other words, users can manipulate the data in a way that seems as if they are interacting directly with a table, even though they are working with a virtual representation provided by the view.

To create an updatable view, certain conditions must be met, and the structure of the view must adhere to specific criteria. Here are some key requirements for creating updatable views:

1. Uniqueness:

• The view must be able to uniquely identify each row in the result set. This often involves ensuring that the view includes columns or expressions that form a unique key or identifier.

2. No Aggregations or DISTINCT:

• Views should not include aggregate functions (e.g., SUM, AVG) or the DISTINCT keyword.

3. No GROUP BY or HAVING:

• The view should not contain GROUP BY or HAVING clauses.

4. No TOP or OFFSET-FETCH:

• Avoid using the TOP keyword or OFFSET-FETCH clauses in the view definition.

5. No JOINs with Multiple Tables:

• While joins can be used in the view definition, they should not result in a view that references multiple tables directly.
• Updatable views are typically based on a single underlying table. However, if you need to update multiple tables, you can achieve this by creating an INSTEAD OF UPDATE trigger on the view.

6. No Subqueries or Derived Tables:

• Avoid using subqueries or derived tables in the view definition.

It’s important to note that while updatable views can simplify interactions with the underlying data, they should be used carefully, and the view definition should adhere to the requirements mentioned above to ensure proper functionality and performance.

In SQL Server, updatable views can be created using the INSTEAD OF triggers, and stored procedures can be used to perform actions on these views.

Example:

Here are some step by step instructions to demonstrate how updatable view works.

Step 1: Create base tables and insert some values

Below, I created 2 tables. PRODUCTS table holds the product information and PRODUCT_SALES holds the quantity sold information of that PRODUCT. Both tables can be mapped using PRODUCT_ID column.

USE TEMPDB
GO

--Create Products table
CREATE TABLE PRODUCTS
(
  PRODUCT_ID        INT
, PRODUCT_NAME      CHAR(255)
, PRODUCT_CATEGORY  CHAR(255)
, PRODUCT_PRICE     FLOAT
, CURRENCY          CHAR(5)
);

--Insert into Products table
INSERT INTO PRODUCTS (PRODUCT_ID, PRODUCT_NAME, PRODUCT_CATEGORY, PRODUCT_PRICE, CURRENCY) VALUES (1, 'Soap', 'Beauty Products', 25, 'GBP');
INSERT INTO PRODUCTS (PRODUCT_ID, PRODUCT_NAME, PRODUCT_CATEGORY, PRODUCT_PRICE, CURRENCY) VALUES (2, 'TV', 'Electronics', 500, 'GBP');
INSERT INTO PRODUCTS (PRODUCT_ID, PRODUCT_NAME, PRODUCT_CATEGORY, PRODUCT_PRICE, CURRENCY) VALUES (3, 'Laptop', 'Electronics', 900, 'GBP');
INSERT INTO PRODUCTS (PRODUCT_ID, PRODUCT_NAME, PRODUCT_CATEGORY, PRODUCT_PRICE, CURRENCY) VALUES (4, 'Hand cream', 'Beauty Products', 10, 'GBP');
INSERT INTO PRODUCTS (PRODUCT_ID, PRODUCT_NAME, PRODUCT_CATEGORY, PRODUCT_PRICE, CURRENCY) VALUES (5, 'Barbie', 'Toys', 56, 'GBP');

--Create Product_Sales table
CREATE TABLE PRODUCT_SALES
(
  SALES_ID          INT
, PRODUCT_ID        INT
, SALES_QUANTITY    INT
, SALES_DATE        DATE
);

--Insert into Product_Sales table
INSERT INTO PRODUCT_SALES (SALES_ID, PRODUCT_ID, SALES_QUANTITY, SALES_DATE) VALUES (101, 1, 5, '2023-09-01');
INSERT INTO PRODUCT_SALES (SALES_ID, PRODUCT_ID, SALES_QUANTITY, SALES_DATE) VALUES (102, 2, 1, '2022-12-01');
INSERT INTO PRODUCT_SALES (SALES_ID, PRODUCT_ID, SALES_QUANTITY, SALES_DATE) VALUES (103, 3, 2, '2022-11-01');
INSERT INTO PRODUCT_SALES (SALES_ID, PRODUCT_ID, SALES_QUANTITY, SALES_DATE) VALUES (104, 4, 3, '2023-09-15');
INSERT INTO PRODUCT_SALES (SALES_ID, PRODUCT_ID, SALES_QUANTITY, SALES_DATE) VALUES (105, 5, 5, '2022-07-01');
INSERT INTO PRODUCT_SALES (SALES_ID, PRODUCT_ID, SALES_QUANTITY, SALES_DATE) VALUES (105, 1, 6, '2021-12-17');
INSERT INTO PRODUCT_SALES (SALES_ID, PRODUCT_ID, SALES_QUANTITY, SALES_DATE) VALUES (105, 5, 7, '2023-05-01');

If we select from both the tables, we get the following result.

Step 2: Create a view with all the columns needed.

The basic syntax to create a view is,

CREATE VIEW [schema_name . ] view_name [ (column_name [ ,...n ] ) ]
AS
SELECT select_statement;

Below, we create a view which combines the result of both our Products and Product_Sales tables.

CREATE VIEW VW_TOTAL_PRODUCT_SALES
AS
SELECT PS.SALES_ID
      , P.PRODUCT_ID
      , P.PRODUCT_NAME
      , P.PRODUCT_PRICE
	  , P.PRODUCT_CATEGORY
	  , PS.SALES_QUANTITY
	  , PS.SALES_DATE
	  , P.CURRENCY
FROM PRODUCTS AS P
INNER JOIN 
PRODUCT_SALES AS PS
ON  P.PRODUCT_ID = PS.PRODUCT_ID;

Once the view is executed, we can query the view with a simple select statement.

SELECT * FROM VW_TOTAL_PRODUCT_SALES;

Before we go to step 3, let us perform UPDATE, DELETE and INSERT into the current view, to see the limitations we have with the current view.

• Upate:

UPDATE VW_TOTAL_PRODUCT_SALES SET PRODUCT_PRICE = 2000 WHERE PRODUCT_ID = 3;

If we try to update the view directly, we see that the row gets updated.

If we directly query the view, we could see the updates.

If we check the underlying base tables, we could also see the data being updated there.

In MSSQL Server, the views are updatable.

• Delete:

Let us now try to delete an entry from the view.

Delete operation will throw an error, as it has dependencies in the other table.

• Insert:

Let us try to insert some values into this view.

INSERT INTO VW_TOTAL_PRODUCT_SALES ( SALES_ID
                                   , PRODUCT_ID
				   , PRODUCT_NAME
				   , PRODUCT_PRICE
				   , PRODUCT_CATEGORY
				   , SALES_QUANTITY
				   , SALES_DATE
				   , CURRENCY)
                            VALUES ( 6
				   , 7
				   , 'Lipstick'  
			           , 10 
				   , 'Beauty Products'
				   , 2             
				   , '2023-09-30'          
				   , 'GBP');

Trying to insert into a view also fails because of multiple base tables.

Step 3: Create the INSEAD OF triggers

CREATE TRIGGER trigSalesData
ON VW_TOTAL_PRODUCT_SALES
INSTEAD OF INSERT
AS
BEGIN			-- TO SPECIFY START OF TRIGGER CODE
BEGIN TRY		-- IF ANY ERROR WITHIN THIS TRY BLOCK THEN "CATCH" BLOCK IS AUTO EXECUTED.
			BEGIN TRANSACTION
			DECLARE  @PRODUCT_ID   INT
			       , @SALES_ID     INT
			       , @PRODUCT_NAME CHAR(255)	
			       , @PRODUCT_PRICE FLOAT
			       , @PRODUCT_CATEGORY CHAR(255)
			       , @SALES_QUANTITY INT
			       , @SALES_DATE DATE
			       , @CURRENCY  CHAR(255)

			SELECT @SALES_ID   = SALES_ID FROM INSERTED
			SELECT @PRODUCT_ID = PRODUCT_ID FROM INSERTED
			SELECT @PRODUCT_NAME = PRODUCT_NAME FROM INSERTED
			SELECT @PRODUCT_PRICE = PRODUCT_PRICE FROM INSERTED
			SELECT @PRODUCT_CATEGORY = PRODUCT_CATEGORY FROM INSERTED
			SELECT @SALES_QUANTITY = SALES_QUANTITY FROM INSERTED
			SELECT @SALES_DATE = SALES_DATE FROM INSERTED
			SELECT @CURRENCY = CURRENCY FROM INSERTED

			INSERT INTO PRODUCTS(PRODUCT_ID, PRODUCT_NAME, PRODUCT_CATEGORY, PRODUCT_PRICE, CURRENCY)	
			             VALUES (@PRODUCT_ID, @PRODUCT_NAME, @PRODUCT_CATEGORY, @PRODUCT_PRICE, @CURRENCY)
			INSERT INTO PRODUCT_SALES (SALES_ID, PRODUCT_ID, SALES_QUANTITY, SALES_DATE) VALUES (@SALES_ID, @PRODUCT_ID, @SALES_QUANTITY, @SALES_DATE)
			COMMIT TRANSACTION
END TRY

BEGIN CATCH
			ROLLBACK TRANSACTION
END CATCH 
END	

Now try to run the same insert script provided before.

We can now see that our insert works.

If we query from the base tables directly, we can see the tables are updated through updatable views.

Some Real time use cases of Updatable views;

Updatable views in Microsoft SQL Server can be useful in various scenarios, providing a layer of abstraction and simplifying interactions with the underlying data. Here are some real-time use cases for updatable views:

1. Security Filtering:

• Updatable views can be used to restrict access to certain rows or columns in a table based on user roles or permissions. This allows users to update data through a view without having direct access to the underlying table.

2. Simplifying Complex Joins:

• Views can be created to simplify complex joins and aggregations, providing a more straightforward interface for users or applications to update or insert data without dealing with the intricacies of the underlying tables.

3. Data Partitioning:

• Views can be used to partition data, presenting subsets of a table to different users or applications. This can simplify data management and improve performance.

4. Audit Trails:

• Updatable views can be used to maintain audit trails by inserting records into a history table when data is updated or deleted. This provides a way to track changes over time.

5. Denormalization:

• Views can be employed to present a denormalized or simplified version of data for reporting purposes. Users can update or insert data into the view, and the view can handle the underlying complexity.

6. Application Interface:

• Updatable views can serve as an interface for applications, providing a simplified structure for data manipulation. This can help shield applications from changes in the underlying schema.

When using updatable views, it’s essential to carefully design them based on specific use cases, considering security, performance, and maintainability. Additionally, thorough testing is crucial to ensure that updates through the views behave as expected.