CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012triggers
peter
peter

Reputation: 2516

Trigger to not delete Data from a table

I have a table called PaperInfo and sample data on the table is:

PaperID   Type   Description
1         T      Level 1
2         S      Level 2
3         V      Level 3

How can enforce a trigger where no one can delete a record from the above table. When some one tries to delete it for example as below:

Delete from dbo.paperInfo 
WHERE PaperID = 1

It should display an error message saying "Cannot Delete Records from Paperinfo Table"

How can i do it?

Upvotes: 1

Views: 1227

Answers (1)

Alireza
Alireza

Reputation: 5056

You should write an INSTEAD OF trigger. This will replace the delete operation:

CREATE TRIGGER td_PaperInfo 
    ON  PaperInfo 
    INSTEAD OF DELETE
AS 
BEGIN
    RAISERROR ('Cannot Delete Records from Paperinfo Table', 16, 1)
END

Upvotes: 2

Related Questions