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