Can we write Insert,update and delete queries in views?

Hello, welcome to Appsloveworld Technologies ,In the session, we learn about updatable views before continuing with the session. I strongly recommend reading the below post first.

• What is a view and the advantages of views with an example

what are updatable views?

Let’s understand that with an example. I have a TblProfessor which has got Id, name, salary Gender, and University_Id columns.

Usually, salary is confidential information and we don’t want employees to be looking at other employee salary details.there is a requirement where a single user needs to know employee details.

In that case, if you grant the user access to the table, he will be able to see all rows and columns, including salary, and we don’t want that. So to avoid that, we can actually create a view that selects all the columns except the salary column.

Create View vWTblProfessor_withoutsalary_Detail
as

Select Id, Name, Gender
from TblProfessor

And if you look at the select statement, it is selecting all the columns except the salary column. when you select data from that view, look at the result. We get all the columns except the salary column.

SELECT * from vWTblProfessor_withoutsalary_Detail

Now, if you look at the view itself, does it really store any data? No, “a view is only a store select statement. So it’s a virtual table. It’s not a real table.”

So when you execute the above query, the view actually gets its data from the TblProfessor table. So here for this view TblProfessor is the underlying base table.

Now when you issue select * from view name, that view is actually going to get its data from the underlying base table because the view itself doesn’t store any data.

So since you are able to issue a statement against a view which in turn will get its data from the underlying base table, can you update a view that will update the underlying base table? Absolutely. That’s definitely possible.

it’s possible to update a view, to insert data into your view, and to delete data from a view which in turn will actually do those respective operations on the underlying base table, in this case, TblProfessor. So when you update or when you insert into this view, it will actually update the underlying base table.

How to create updatable view in sql server?

Now let’s update the view. So I’m updating the view update view and we are saying the set name is equal to ‘Mark ‘where Id = 1.

Update vWTblProfessor_withoutsalary_Detail Set Name = 'Mark' Where Id = 1

Let’s execute the above query and then select data from the view

Now let’s select the data from The View. as you can in the above image ,name have been changed to ‘Mark‘.

Can we update real data in views?

Now let’s select data from the TblProfessor table and see if the underlying base table is updated. So if you look at the underlying base table TblProfessor table itself, the name is updated to ‘Mark‘.

So it’s possible to update the base table using views. In this case, we call The View as updatable view and in the SQL server, our views are updatable.

Delete and insert data from a view

Now we can also delete and insert data from a view which in turn will obviously delete and insert data from the underlying table. Let’s try to delete a record with id equal to 2 in this case Mackenzie record.

Delete from vWTblProfessor_withoutsalary_Detail where Id = 2

So let’s exclude and then select the data back from view and see if it’s really deleted.

As you can see in the above image ,Id=2 is deleted from the table. We can also insert data into the view.

SQL Updatable View with joined tables

Look at the view here, I’m creating. we have TblProfessor and TblUniversity table, on both of these tables. I’m creating a view which returns to me I’d, name, salary, gender, and University name.

So the first four Id, names, salary, and gender are coming from the TblProfessor table and the University name is coming from the TblUniversity table.

Create View vWTblProfessorDetail
as

Select Id, Name, Salary, Gender, UniversityName
from TblProfessor
join TblUniversity
on TblProfessor.University_Id = TblUniversity.University_Id

we are creating a view which joins the TblProfessor table with the TblUniversity . if you have a view that is based on multiple tables and when you update that view, what happens? That’s what we will be looking at now.

Now if you look at Connor’s record with Id=4, Connor is currently within Arizona State University.

Let’s try to update his University to McGill University from Arizona State University ,but before we do that, note who else is within the Arizona State University along with Connor .

Chase is also within Arizona State University. So currently two Professor, Connor , and Chase , within Arizona State University.

when I issued the below update statement, we are actually updating Connor’s university to be McGill University from Arizona State University.
So only Connor’s university should be converted to McGill University. Chase should still remain Arizona State University.

Update vWTblProfessorDetail
set UniversityName='McGill University' where Name = 'Connor'

So let’s update this and see what’s going to happen and then let’s select the data back from The View.

Connor’s university is set to it, which is good. But look at Chase’s university.we didn’t expect “McGill University” we expected this to be “Arizona State University“, but then our update statement didn’t update correctly. Something has gone wrong. So let’s analyze why.

when we issue, no matter whether your view is based on a single based table or multiple based tables, when you update the view, it’s going to update the underlying base tables.

Why? Because the view itself doesn’t store any data. By default, we can change that default behavior using materialist views. But by default, a view doesn’t really store any data.

So when you issue a select, insert, update or delete statement, these statements are executed against the underlying base tables.

so here, when you updated this view, it is actually behind the scenes updating the underlying base tables, TblProfessor and TblUniversity.

let’s select data from those tables and see what’s actually happening.

If you look at this, if you look at the TblUniversity’s data and look at this, Connor’s University_Id is 3, Chase’s University_Id is 3.

When you issued an update statement, when you say update, this view said University name is equal to “McGill University“, where the name is equal to Connor.

So for Connor, you are saying said University name is equal to “McGill University”. That means this University_Id for this Connor’s record should be changed from 3 to 7.

But in fact what has actually happened, when you updated the view, view incorrectly updated the underlying base table. it updates Arizona State University to McGill University in TblUniversity table.

So, if your view is based on multiple tables, and you want to update the view, then it may not update the underlying base tables incorrectly.

To correctly update a view based on multiple tables, we can use INSTEAD OF triggers are used.
Learn more about -INSTEAD OF triggers

Table Create Sql Script

 

/****** Object: Table [dbo].[TblProfessor] Script Date: 09/30/2020 5:35:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TblProfessor](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](max) NOT NULL,
[Gender] [nvarchar](50) NULL,
[Salary] [float] NULL,
[University_Id] [int] NULL,
CONSTRAINT [PK_TblStudent] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[TblUniversity] Script Date: 09/30/2020 5:35:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TblUniversity](
[University_Id] [int] IDENTITY(1,1) NOT NULL,
[UniversityName] [nvarchar](max) NULL,
CONSTRAINT [PK_University] PRIMARY KEY CLUSTERED
(
[University_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[TblProfessor] ON
GO
INSERT [dbo].[TblProfessor] ([Id], [Name], [Gender], [Salary], [University_Id]) VALUES (1, 'Christopher', 'Male', 50000, 2)
GO
INSERT [dbo].[TblProfessor] ([Id], [Name], [Gender], [Salary], [University_Id]) VALUES (2, 'Mackenzie', 'Female', 60000, 2)
GO
INSERT [dbo].[TblProfessor] ([Id], [Name], [Gender], [Salary], [University_Id]) VALUES (3, 'Julia', 'Female', 40000, 4)
GO
INSERT [dbo].[TblProfessor] ([Id], [Name], [Gender], [Salary], [University_Id]) VALUES (4, 'Connor', 'Male', 30000, 3)
GO
INSERT [dbo].[TblProfessor] ([Id], [Name], [Gender], [Salary], [University_Id]) VALUES (5, 'Madeline', 'Female', 90000, 6)
GO
INSERT [dbo].[TblProfessor] ([Id], [Name], [Gender], [Salary], [University_Id]) VALUES (6, 'Easton', 'Male', 35000, 5)
GO
INSERT [dbo].[TblProfessor] ([Id], [Name], [Gender], [Salary], [University_Id]) VALUES (7, 'Gianna', 'Female', 40000, 7)
GO
INSERT [dbo].[TblProfessor] ([Id], [Name], [Gender], [Salary], [University_Id]) VALUES (8, 'Chase', 'Male', 32000, 3)
GO
SET IDENTITY_INSERT [dbo].[TblProfessor] OFF
GO
SET IDENTITY_INSERT [dbo].[TblUniversity] ON
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName]) VALUES (1, 'Carnegie Mellon University')
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName]) VALUES (2, 'Michigan State University')
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName]) VALUES (3, 'Arizona State University')
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName]) VALUES (4, 'The Australian National University')
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName]) VALUES (5, 'The University of Arizona')
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName]) VALUES (6, 'The University of Melbourne')
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName]) VALUES (7, 'McGill University')
GO
SET IDENTITY_INSERT [dbo].[TblUniversity] OFF
GO
ALTER TABLE [dbo].[TblProfessor] WITH CHECK ADD CONSTRAINT [FK_TblStudent_University] FOREIGN KEY([University_Id])
REFERENCES [dbo].[TblUniversity] ([University_Id])
GO
ALTER TABLE [dbo].[TblProfessor] CHECK CONSTRAINT [FK_TblStudent_University]
GO

The post Can we write Insert,update and delete queries in views? appeared first on Software Development | Programming Tutorials.