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 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.
Read More Articles
- Linq to SQL Group by and Sum in Select | Linq To SQL Group By and Sum
- How send an HTTP POST request to a server from Excel using VBA?
- What is Difference between where and having clauses in SQL server
- How to Use EXCEPT Operator with an example in Sql
- How to use merge statement in the SQL server
- How to write Re-runnable SQL server query
- How to create Cursor in sql server with example
- How to generate random data in sql server for performance testing
- How to write subquery in select statement in Sql
- How to Log record changes in SQL server in an audit table
- why does sql server execute the second statement redundantly?
- how to insert data into multiple relational tables by using multiple tasks
- Merge two rows of same table with common fields
- VB.NET - Failed to convert parameter value from a String to a Guid issue
- SQL: Building where clause
- Creating a recursive CTE with no rootrecord
- SQL Server AND AND OR AND AND
- Sql Query optimization Issue in JOIN
- Understanding CTE Semicolon Placement
- How can I view the history of inserts to a table?
- Converting text to number
- Why does "WHERE (Time_Stamp >= TDateTime-variable)" ignore fractional seconds?
- getting through 665 Error that prevents database backup
- SQL Server: ODBC Connection pooling / C API
- JDBC connection to Azure fails in Android Studio
- Using table just after creating it: object does not exist
- Paginating Microsoft SQL and PHP
- Delete Specific Backup file through SQL
- SQL Server TSQL Stored Procedure / Queries Slow
- Error connecting to SQL Server via alias from local IIS7
- How to get the WINDOWS USER within "EXECUTE AS OWNER"
- Linking Textboxes C# SQL Server
- Connecting to SqlServer from .Net Core on Ubuntu/Visual Studio Code
- Subquery returned more than 1 value. This is not permitted when the subquery using comparision operators
- Convert String To XML
- SQL Not Auto-incrementing
- Create a list of 10 most expensive products, using Sub-queries, in Microsoft SQL
- SQL server express, cannot read or write in C#
- Trouble connectiong to SQL Server Express 2008 remotely with C# - But Windows ODBC administrator works
- In SQL Server, is it possible to include 2 columns in an IN clause?
- Need help in designing a database schema for a SaaS application
- Sql Try Catch in asp.net
- Native XML Web Services for SQL Server 2008
- Combining and shortening two where clause conditions
- How to access FileTables from Entity Framework?
- choose MySQL or choose SQL Server Express (free)?
- SSIS-Slowly Changing dimension--Nvarchar(MAX) ISSUE
- Binding query result column alias in SQL Server
- Error when trying to make SQL Server database connection in Java code
- SQL - Exclude values of a column which meet a requirement at least once
- .Net / SQL Server race condition
- Sql Server for each row
- How do I insert data from one table to another when there is unequal number of rows in one another?
- SQL And NULL Values in where clause
- Deleting data from tables
- Amazon EC2 Sql Performance
- SQL if statement within WHERE clause
- join two identical table structures with different data
- How to: Configure IIS7 for Web Synchronization
- Make sum of elements when case is lower than specific date
- Getting XML value in SQL query
- Add column with default value from a previous select
- TSQL - Insert values in table based on lookup table
- PHPStorm Encodes Query only sometimes
- What is the difference between these two LINQ Queries and how to correctly optimize them?
- Pass a Variable to SQL Stored Procedure ByRef?
- Optimizing SQL Server / C# queries and custom paging
- rewards the products qualify for
- selectively execute task in ssis control flow
- Django <-> SQL Server 2005, text encoding problem
- Delete table names with special character
- Is SQLFiddle broken? Errors for Oracle, SQL Server, ...?
- SQL Server: Join two tables and only get data of one table without identical rows
- SQL Server : How to count rows within a given date range?
- mssql_query(): Query failed
- Loop a query through multiple Databases using a cursor
- SQL Server 2005 Query Statistics
- Incrementing SQL column during UPDATE
- Change format of datetime in SQL
- TSQL Only Select Rows Where Values Are Not Negated By Another Row
- Querying the Result set of a Previous Query
- SSIS runs package that runs powershell script but SSMS don't
- How to elegantly write a SQL ORDER BY (which is invalid in inline query) but required for aggregate GROUP BY?
- How to do the equivalent of a 'Tsql select into', into an existing table
- Query keeps executing when click cancel it then says rows affected
- HOWTO: Interpret the MSSQL CDC "__$update_mask" field
- Update SAS 9.1 Program to Connect to SQL Server DB instead of Access mdb
- SQL recursive logic
- What is the rule of order by with special character?
- SQL Server query question (Count maybe?)
- How it is possible to create clustered index on a view
- TSQL Count Consecutive records
- SqlConnection throws an exception
- SQL get values for only a few hours in given time period?
- How to search Date in SQL Server 2012 over two columns
- Executing SQL Scripts in a batch
- How to retrieve one record at a time from SQL Server database in MVC Model
- how to select value from group by using linq to sql
- Select status wise rows from duplicate rows and distinct rows from table
- Incorrect syntax near begin in sql server while creating function
- Migrating shape sql to something equally powerful
- SQL Server: Group By based on one column and get columns based on other column
- Java JDBC MySQL connection error: ClassNotFoundException
- How to find difference in days from DD-MM-YYYY formatted dates in SQL Server?
- sql server compatible queries for given oracle queries
- Sql create table with alternate row from two different table
- Max length of SQL Server instance name?
- How to use DbGeography.Filter in Linq with Entity Framework 5?
- Is SQL Server merge replication transaction-aware?
- Would like code improvement on C# TCP listener to SQL Server database app
- How to import mdf file into Azure Data Studio
- Sorting in Array vs Sorting in SQL
- Select TOP not completing the query?
- add extra row of total salaries
- CASE in WHERE Clause
- Error in Count(),Group by and Joins in sql server
- What can I use instead of an Include?
- How to design for higher scalability, in SQL Server databases that need set operations?
- Why there isn't any SQL Server services in SQL Server Configuration Manager?
- Can I configure BizTalk Server 2013 to join an existing SSO system that is based on an older infrastructure?
- PHP - Connect to MS SQL Server
- Store new permanent table in schema using compute
- T-SQL Where Between X and Y Except Z
- How can I do select case to this query?
- SQL Server : get count and sum of columns
- Cannot insert duplicate key: MySQL to SQL server
- SQL group by with like condition
- SQL - what's the best way to look up which table a column belongs to?
- How to map formatted Datetime value in C#
- understanding query to remove non-numeric characters
- merge multiple rows to one in sql
- Isolation level for Select statements in transactions for the same SQL Server trigger/stored procedure?
- SQL Server 2005 query syntax error: "The column 'id_ctrc' was specified multiple times for 'inner_tbl'."
- Use linq equivalent of sql IN operator
- How do I add a prerequisite in an InstallShield installer
- LIMIT 10..20 in SQL Server
- Alternative to View for complex Data?
- how can I use a sproc @param as column alias?
- SQL: Nested subquery is returning entries incorrectly
- Date data from Text file to date time in SQL Server
- Searching inside a SQL record with date limits
- Azure SQL Management instance - restore db different version
- Getting next matching string value from a SQL table
- T_SQL Rank function not working as expected
- MS SQL is this possible to do in a view?
- Max value in a many-to-many relationship
- Reverse col and rows in SQL
- How to save a PDF file in a SQL Server column using vb.net code
- How to clean a columns with a dots at the beginning and end of a text using T SQL for SQL Server?
- Does Azure SQL Database support two-phase commit (2PC)?
- SQL Query to Re-build Indexes
- Cannot Call function sqlsrv_connect()
- CREATE PROCEDURE gets "Msg 6567, Level 16, State 2" for SQLCLR stored procedure
- SQL Find Max of a row
- TSQL update table using call for one stored procedure to another
- What aspects of a sql query are relatively costly to one another? Joins? Num of records? columns selected?
- Stored Procedure returning 0 on scope identity
- Using SQLCMD in a batch file, how can I parse the response and check for an error?
- Android: HttpPost - Trying to insert data entered by a user into an SQL table on the server
- SQL Server 2005, Enforce Foreign Key Constraint and Cascade Delete