Using DDL Triggers in Microsoft SQL Server

In this blog post I want to illustrate how you could use a DDL (Data Definition Language) trigger in order to prevent database users from dropping or altering tables in a database. I will use the AdventureWorks database for this example.

STEP 1]  Ensure that the AdventureWorks database is being used.

---

---

STEP 2] Use the transact SQL code similar to the one shown below in order to create a trigger. I named the trigger “NoALTERorDROP”! The objective for creating this trigger is to prevent users from altering and/or dropping tables and to send out a message every time a user attempts to do so. Note that the ROLLBACK command is the part of the code that prevents the action (I.e. altering or dropping) from happening.

CREATE TRIGGER NoALTERorDROP
       ON DATABASE
       FOR ALTER_TABLE, DROP_TABLE
AS
PRINT 'You do not have permission to alter or drop this table.'
ROLLBACK
GO

Step 3] In order to test the functionality of this trigger, I will create a table and then try to drop it (You could use an already existing table as well if you wish). I will name the table “Example”.

CREATE TABLE dbo.Example
(ID int) 
GO  

Step 4] Now let’s attempt to drop (or alter) the table to see whether the trigger actually works. As could be observed in the screenshot below, upon executing the drop table command the trigger did not permit the action from being committed and our customized message appeared in the Messages screen.

---