Prevent DML Operations on SQL Server table using Triggers

Prevent DML Operations on SQL Server table using Triggers

Create a  Table called Sports

CREATE TABLE [dbo].[Sports] (

    [Id]      INT          IDENTITY (1, 1) NOT NULL primary key,

    [Name]    VARCHAR (20) NOT NULL,

    [Country] NCHAR (2)    NOT NULL,

   

);

Insert Data Into Sports table

insert into sports(name,country) values
('BaseBall','US'),('Hand FoodBall','US')

insert into sports(name,country) values
('cricket','IN'),('Tennis','IN'),('Hockey','IN'),('Football','IN'

ID	Name	Country
1	cricket	IN
2	Tennis	IN
3	Hockey	IN
4	Football	IN
5	BaseBall	US
6	Hand FoodBall	US

Now Add a Trigger Which Blocks all DML operations a Table Sports

create trigger Sports_DML_BLOCK
on [dbo].[sports]
for insert ,update,delete
as


begin try
RAISERROR ('Can not perform DML operations on sports table, either disable/delete the trigger',16,1);

end try
begin catch
throw
end catch

Now Insert into Sports Table

insert into sports(name,country) values('Ice Hockey','US');




Msg 50000, Level 16, State 1, Procedure Sports_DML_BLOCK, Line 25
Can not perform DML operations on sports table, either disable/delete the trigger

Same thing happens for UPDATE AND DELETE Operations.


Tags:Restrict DML Operations a Table in SQL SERVER 2012,Prevent DML Operations on SQL Server table using Triggers.Block DML Operations on a Table in SQL-Server 2012, using RAISError in Stored Procedures, using RaisError in triggers.