Home > DeveloperSection > Articles > View in SQL Server

View in SQL Server


Database Database 
Ratings:
0 Comment(s)
 1985  View(s)
Rate this:

View in SQL Server

A view is a virtual table that consists of columns from one or more tables. Though it is similar to a table, it is stored in the database. It is a query stored as an object. Hence, a view is an object that derives its data from one or more tables. These tables are referred to as base or underlying tables.

Once you have defined a view, you can reference it like any other table in a database.

Use of a View

Views are used for security purposes because they provide encapsulation of the name of the table. Data is in the virtual table, not stored permanently. Views display only selected data.

Types of View

These are two types of views.

1.      Simple View

2.      Complex View   

Simple View

A Simple View is a user defined view. A Simple View takes the data from a single table and has no function. The user defined view is created by the user as needed. We are showing a Simple View using a student table.

Syntax of a View

CREATE VIEW <view name>

 AS
SELECT <column_name(s)> FROM <table_name> WHERE <condition>

 

Example of Simple View

--Create DataBase

CREATE DATABASE VIEWS

 

--Create College Table

CREATE TABLE COLLEGE

(

SNo INT IDENTITY(1,1),

COLLEGE_CODE INT PRIMARY KEY,

COLLEGE_NAME VARCHAR(50) NOT NULL,

COLLEGE_ADD VARCHAR(50) NOT NULL

)

 

-- Create Simple View

CREATE VIEW ALD_COLLEGE

AS

SELECT * FROM COLLEGE WHERE COLLEGE_ADD='ALLAHABAD'

 

Complex View

A Complex View is created for multiple tables and contains functions and group data.

CREATE VIEW <view name>

 AS
SELECT <column_name(s)> FROM <table_name1> JOIN <table_name2> ON <condition>

 

Example of Complex View

--Create DataBase

CREATE DATABASE VIEWS

 

--Create Table1

CREATE TABLE COLLEGE

(

SNo INT IDENTITY(1,1),

COLLEGE_CODE INT PRIMARY KEY,

COLLEGE_NAME VARCHAR(50) NOT NULL,

COLLEGE_ADD VARCHAR(50) NOT NULL

)

-- Insert Values in COLLEGE Table

 

-- Create Table2

CREATE TABLE COURSE

(

SNo INT IDENTITY(1,1) PRIMARY KEY,

COLLEGE_CODE INT FOREIGN KEY REFERENCES COLLEGE(COLLEGE_CODE),

COURSE_CODE INT NOT NULL,

COURSE_NAME VARCHAR(50) NOT NULL,

COURSE_MAX_SEAT INT NOT NULL,

COURSE_FEE MONEY NOT NULL

)

-- Insert Values in COURSE Table

 

--CREATE VIEW

CREATE VIEW DETAILS

AS

SELECT COLLEGE.COLLEGE_CODE,COLLEGE.COLLEGE_NAME,COURSE.COURSE_FEE FROM COLLEGE  INNER JOIN COURSE  ON COLLEGE.COLLEGE_CODE = COURSE.COLLEGE_CODE

 

Drop View

Simple and Complex View are Drop by same syntax

--Syntax for Drop Simple or Complex View

DROP VIEW <View Name>

 

--Example for Drop Simple or Comple View

DROP VIEW DETAILS

 

Trigger on View

‘INSTEAD OF’ keyword used for creating Trigger on View.

Basically there are three types of INSTEAD Of Trigger in SQL server which are namely define as follows:

1.       INSTEAD OF INSERT Trigger

2.       INSTEAD OF UPDATE Trigger

3.       INSTEAD OF DELETE Trigger

INSTEAD OF INSERT Trigger:

INSTEAD OF INSERT triggers can be defined on a VIEW or TABLE to replace the standard action of the INSERT statement. Usually, the INSTEAD OF INSERT trigger is defined on a view to insert data into one or more base tables.

Syntax: Creating Instead Of Insert Trigger

 

-----SYNTAX DEMONSTRATION OF INSTEAD OF INSERT TRIGGER--

CREATE TRIGGER <TRIGGER_NAME>

ON <VIEW_NAME> OR <TABLE_NAME>

INSTEAD OF INSERT

AS

BEGIN

----INSERT SQL QUERY TO INSERT DATA INTO TABLE--

--TRIGGER BODY SQL CODE---

END

GO

 

Example: Creating Instead Of Insert Trigger

 

----CREATE VIEW FOR DEMONSTRATION OF CREATING INSTEAD OF INSERT TRIGGER

 CREATE VIEW TEST_INSERT_INSTEADOF

 AS

 SELECT * FROM Userlogin

 ------DEMONSRATION OF CREATING INSTEAD OF INSERT TRIGGER--

  CREATE TRIGGER TEST_INSTEADOF_TRIGGER

  ON TEST_INSERT_INSTEADOF

  INSTEAD OF INSERT

  AS

  INSERT INTO Userlogin

  SELECT * FROM INSERTED

  GO

 

INSTEAD OF DELETE Trigger:

INSTEAD OF DELETE triggers can be defined on a VIEW or TABLE to replace the standard action of the DELETE statement. Usually, the INSTEAD OF DELETE trigger is defined on a view to modify data in one or more base tables. DELETE statements do not specify modifications to existing data values. DELETE statements specify only the rows that are to be deleted.

Syntax: Creating Instead of Delete Trigger

 

  -----SYNTAX DEMONSTRATION OF INSTEAD OF DELETE TRIGGER--

CREATE TRIGGER <TRIGGER_NAME>

ON <VIEW_NAME> OR <TABLE_NAME>

INSTEAD OF DELETE

AS

BEGIN

----DELETE SQL QUERY TO INSERT DATA INTO TABLE--

--TRIGGER BODY SQL CODE---

END

GO

 

Example: Creating Instead of Delete Trigger

 

----CREATE VIEW FOR DEMONSTRATION OF CREATING INSTEAD OF DELETE TRIGGER

 CREATE VIEW TEST_DELETE_INSTEADOF

 AS

 SELECT * FROM Userlogin

 ------DEMONSRATION OF CREATING INSTEAD OF DELETE TRIGGER--

  CREATE TRIGGER TEST_INSTEADOF_TRIGGER

  ON TEST_DELETE_INSTEADOF

  INSTEAD OF DELETE

  AS

  INSERT INTO Userlogin

  SELECT * FROM DELETEED

  GO

 

INSTEAD OF UPDATE Trigger:

INSTEAD OF UPDATE triggers can be defined on a VIEW or TABLE to replace the standard action of the UPDATE statement. Usually, the INSTEAD OF UPDATE trigger is defined on a view to modify data in one or more base tables. UPDATE statements that reference views with INSTEAD OF UPDATE triggers must supply values for all non null view columns referenced in the SET clause.

Syntax: Creating Instead of Update Trigger

 

-----SYNTAX DEMONSTRATION OF INSTEAD OF UPDATE TRIGGER--

CREATE TRIGGER <TRIGGER_NAME>

ON <VIEW_NAME> OR <TABLE_NAME>

INSTEAD OF UPDATE

AS

BEGIN

----UPDATE SQL QUERY TO INSERT DATA INTO TABLE--

--TRIGGER BODY SQL CODE---

END

GO

 

Example: Creating Instead of Update Trigger

 

----CREATE VIEW FOR DEMONSTRATION OF CREATING INSTEAD OF UPDATE TRIGGER

 CREATE VIEW TEST_UPDATE_INSTEADOF

 AS

 SELECT * FROM Userlogin

 ------DEMONSRATION OF CREATING INSTEAD OF UPDATE TRIGGER--

  CREATE TRIGGER TEST_INSTEADOF_TRIGGER

  ON TEST_UPDATE_INSTEADOF

  INSTEAD OF DELETE

  AS

  UPDATE Userlogin SET Id = deleted.id, Name = deleted.name 

  GO

  


Don't want to miss updates? Please click the below button!

Follow MindStick