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