Introduction
In this tutorial, I am going to explain the method to prevent tables from being created, altered, or dropped in SQL Server. This detailed article will cover the following topics:
- Introduction
- Introduction to DDL Triggers
- DDL Trigger Scope
- Prevent Table from being Created, Altered, or Dopped in SQL Server
- Difference Between DDL and DML Triggers
- Conclusion
First, let's create a database with some tables containing some dummy data. Here, I am providing you with the database along with the tables containing the records, on which I am showing you the various examples. Let's see.
Let's check our following tables by using the following queries.
1) To get the data from the "Employee" table, use the following query.
![Prevent Tables From Being Created, Altered, Or Dropped In SQL Server]()
2) To get the data from the "tbl_Menu" table, use the following query.
![Prevent Tables From Being Created, Altered, Or Dropped In SQL Server]()
DDL Triggers
SQL Server DDL triggers fire in response to various DDL (Data Definition Language) events. These events mainly correspond to T-SQL statements that begin with one of the following keywords CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS. DDL triggers can be used for the following cases:
- Record changes or events in the database schema.
- Prevent some changes to the database schema.
- Respond to changes in the database schema.
Key Points
- DDL Triggers was introduced in SQL Server 2005.
- Multiple triggers can be created for each DDL event.
- DDL triggers can be used to restrict DDL operations.
- DDL triggers are not triggered by events affecting local or global temporary tables.
- DDL triggers do not create the special inserted and deleted tables.
Syntax
CREATE TRIGGER <trigger_name>
ON [ALL SERVER | DATABASE]
WITH [trigger_option]
FOR <event_type(s)>
AS
BEGIN
<trigger_body>
END
DDL Trigger Scope
The scope of the trigger depends on the event. DDL triggers can be created either on a specific database or on the server. DDL triggers can be either in database or server scope.
- DDL triggers with SERVER level scope are triggered in response to DDL events having SERVER Scope, such as CREATE_DATABASE, DROP _DATABASE, CREATE _LOGIN, ALTER _LOGIN, CREATE_LINKED_SERVER, and so on.
- DDL triggers with DATABASE Scope fire in response to DDL events with DATABASE Scope, such as CREATE _TABLE, ALTER _TABLE, DROP_TABLE, CREATE _PROCEDURE, ALTER _PROCEDURE, CREATE _FUNCTION, ALTER _FUNCTION, CREATE _VIEW, ALTER_VIEW, and so on.
Note
To find the,
- Database Scoped Triggers, Navigate: <Database_Name> > Programmability > Database Triggers. (Be sure to refresh, Database Triggers).
- Server Scoped Triggers, Navigate: Server Objects > Triggers (Be sure to refresh, Triggers).
ENABLE Trigger
The ENABLE TRIGGER statement enables a trigger to be fired whenever an event occurs.
Syntax
ENABLE TRIGGER <trigger_name>
ON [ALL SERVER | DATABASE | Object_Name]
DISABLE Trigger
The DISABLE TRIGGER statement deactivates a trigger so that it cannot be fired. Sometimes, we need to disable it.
Syntax
DISABLE TRIGGER <trigger_name>
ON [ALL SERVER | DATABASE | Object_Name]
DROP Trigger
The DROP TRIGGER statement is used to drop one or more triggers from the database or server.
Syntax
DROP TRIGGER [IF EXISTS] <trigger_name>
ON [ALL SERVER | DATABASE]
Prevent Tables from being Created, Altered, or Dropped in SQL Server
Follow the given steps carefully to prevent tables from being created, altered, or dropped in SQL Server. Let's see.
A) Prevent Tables From Being Created
Step 1
Execute the following trigger statement to create a DDL Trigger.
![Create DDL Query1]()
Note
You can find the Database Scoped Triggers in, <Database_Name> > Programmability > Database Triggers. (Be sure to refresh, Database Triggers).
![Create DDL Query2]()
Step 2
Now, try to create the table by executing the following statement.
![Create DDL Query3]()
Step 3
From the above result, we can clearly see that the "Tr_CreateTableSecurity" trigger is preventing the user from creating the table. Now, disable the trigger by executing the following query.
![Create DDL Query4]()
Step 4
Now, execute the "Step 2" query again. And, to confirm, execute the following query.
![Create DDL Query5]()
B) Prevent Tables From Being Altered
Step 1
Execute the following trigger statement to create a DDL Trigger.
![Alter DDL Query1]()
Step 2
Now, try adding a new column "DOB" to the "Employee" table.
![Alter DDL Query2]()
Step 3
From the above result, we can clearly see that the "Tr_AlterTableSecurity" trigger is preventing the user from adding a new column to the table. Now, disable the trigger by executing the following query.
![Alter DDL Query3]()
Step 4
Now, execute the "Step 2" query again. After that, execute the following query to see the result.
![Alter DDL Query4]()
C) Prevent Tables From Being Dropped
Step 1
Execute the following trigger statement to create a DDL Trigger.
![Drop DDL Query1]()
Step 2
Now, try to drop the table.
![Drop DDL Query2]()
Step 3
From the above result, we can clearly see that the "Tr_DropTableSecurity" trigger is preventing the user from dropping the table. Now, disable the trigger by executing the following query.
![Drop DDL Query3]()
Step 4
Again, try to drop the table. After that, execute the following query to see the result.
![Drop DDL Query4]()
Difference Between DDL and DML Triggers
Now, let's look at the difference between DDL and DML Triggers in SQL Server. Let's see.
S.No. |
Key Points |
DDL Triggers |
DML Triggers |
1 |
Definition |
DDL triggers fire in response to various DDL events. |
DML triggers fire in response to various DML events. |
2 |
History |
DDL Triggers was introduced in SQL Server 2005. |
--- |
3 |
Used Special Tables? |
No |
Yes |
4 |
Create Multiple triggers on an object? |
Yes |
Yes |
5 |
Usage |
Can be used to audit and prevent changes in the database schema. |
Can be used to enforce business rules and data integrity. |
See you in the next article, till then take care and be happy learning.
You may also visit my other articles,
Conclusion
In this article, we have discussed the method to prevent the tables from being created, altered, or dropped in SQL Server with various examples.
I hope you enjoyed this article. Follow C# Corner to learn more new and amazing things about SQL Server.
Thanks for reading.