DELETE restrictions of VIEWS

One of my friends told me that, he has a VIEW from a single base table and the user of the view is always trying to DELETE the records from VIEW. So the underlying TABLE records are also deleted for deletion actions of the view. He wants to restrict the deletions of records from VIEW.

The article is related to the DELETE restrictions of VIEWS. We can easily do it by REVOKING the DELETE permissions from the VIEW objects. But my friends have no permission over GRANT and REVOKE.

So the solution is given by using the INSTEAD OF DELETE Trigger.  Here I am not going to describe the definition of it.

To know more about it, just go to my previous article.

FOR | AFTER | INSTEAD OF

http://sqlknowledgebank.blogspot.in/2012/03/for-after-instead-of.html

Rather I just provide a simple self explanatory example to demonstrate it.

-- Base Table

IF OBJECT_ID('emp_data') IS NOT NULL

   BEGIN

     DROP TABLE emp_data

   END

GO

 

CREATE TABLE emp_data

       (

         IDNO     INT         NOT NULL IDENTITY(1,1) PRIMARY KEY,

         EMPNAME  VARCHAR(50) NOT NULL,

         EMPGRADE VARCHAR(1)  NOT NULL,

         CONSTRAINT CHK_emp_data_EMPGRADE 

         CHECK (EMPGRADE IN('A','B','C'))

        )

       

GO   

-- Insert records

INSERT INTO emp_data 

       (EMPNAME, EMPGRADE)

VALUES ('Sudip Das', 'A'),

       ('Joydeep Das', 'B'),

       ('Sangram Jit', 'B'),

       ('Tuhin Shinah', 'B'),

       ('Bhola', 'C')    

      

-- Display records

SELECT IDNO, EMPNAME, EMPGRADE 

FROM   emp_data       

     

-- Output

IDNO  EMPNAME           EMPGRADE

1     Sudip Das         A

2     Joydeep Das       B

3     Sangram Jit       B

4     Tuhin Shinah      B

5     Bhola             C

    

-- Creation of VIEWS

IF OBJECT_ID('view_emp_data') IS NOT NULL

   BEGIN

     DROP  VIEW   view_emp_data

   END

GO

CREATE VIEW  view_emp_data

AS

SELECT IDNO, EMPNAME, EMPGRADE 

FROM   emp_data

GO 

-- Display the Records of Views

SELECT IDNO, EMPNAME, EMPGRADE 

FROM   view_emp_data

-- Output from View

IDNO  EMPNAME           EMPGRADE

1     Sudip Das         A

2     Joydeep Das       B

3     Sangram Jit       B

4     Tuhin Shinah      B

5     Bhola             C

-- Protect DELETION From VIEWS

IF OBJECT_ID('trg_RestrictDEL') IS NOT NULL

   BEGIN

     DROP  TRIGGER  trg_RestrictDEL 

   END

GO

       

CREATE TRIGGER trg_RestrictDEL ON view_emp_data

INSTEAD OF DELETE

AS

  BEGIN

       IF @@rowcount > 0

       BEGIN

       RAISERROR('Rows from Views Named: view_emp_data, CANNOT be DELETED!', 16, 2 )

       ROLLBACK

  END

END

GO

-- Now try to delete records from views

DELETE view_emp_data

WHERE IDNO=1

-- Output generated for DELETE Action

Msg 50000, Level 16, State 2, Procedure trg_RestrictDEL, Line 8

Rows from Views Named: view_emp_data, CANNOT be DELETED!

Msg 3609, Level 16, State 1, Line 1

The transaction ended in the trigger. The batch has been aborted.

Hope you like it.

Posted by: MR. JOYDEEP DAS

Why VIEW takes long time to Execute

Here in this article we are discussing related to the performance of the views. Why the performance of the view is slow. Please note that I am not taking about the indexed views, it's another thing and out of the scope of this article.

Like stored procedure, the optimizers cache the execution plan in the case for further use.

Let's take an example:

We have a simple base table contains records and we just create a view from this base table objects and then execute both (Base table and View separately)

CREATE TABLE emp_Table

       (empid    INT NOT NULL IDENTITY(1,1) PRIMARY KEY,

        empname  VARCHAR(50) NOT NULL,

        empsal   DECIMAL(20,2))

       

GO

CREATE VIEW vw_empView

AS

SELECT empid, empname, empsal

FROM   emp_Table

GO

-- Example-1

SELECT * FROM emp_Table

GO

-- Example-2

SELECT * FROM vw_empView

GO

Here in this example which one is executed faster?

It's Example-1. That means direct query from Table objects. To get the better understanding related to execution we just look at the execution plan.

Both execution plans are same. So, why the execution of views take long time?

This is because it takes SQL Server extra work such as looking up data in the system tables before it can execute the view.

This extra work is not part of the execution plan, so it appears that the two SELECT statements should run at the same speed, which they don't, because some of the work SQL Server is doing is hidden.

Hope you like it.

Posted by: MR. JOYDEEP DAS