Thursday 28 June 2012

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

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

6 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Thank you joydeep.. nice article..

    How rollback is working inside the instead of trigger even we are not mentoining BEGIN Transation any where. We are not creating any transaction. I mean how sql server managing this INSTEAD OF TRIGGER.. I kindly request you that please write one article on this. Thanks

    ReplyDelete
    Replies
    1. Thanks “Vikas”.
      I am just sort-out my typing problem (Copy/Paste Problem).
      Yes … I am going to write it in my article that you requested.

      Delete
    2. Just go to
      http://sqlknowledgebank.blogspot.in/2012/06/commit-and-rollback-transaction-in.html

      Delete
    3. Heartly thanks Joydeep for giving queck reply.. and new article..

      Delete