DDL Triggers in SQL Server
First Create a database with the name TestDB
Example1: Create a trigger that will restrict creating a new table on a
specific database.
USE TestDB
GO
CREATE TRIGGER trRestrictCreateTable
ON DATABASE
FOR CREATE_TABLE
AS
BEGIN
PRINT 'YOU CANNOT CREATE A TABLE IN THIS DATABASE'
ROLLBACK TRANSACTION
END
Where can I find the DDL triggers?
Note: If you can’t find the trigger that you just created in the TESTDB
database, make sure to refresh the Database Triggers folder. When you
execute the following code to create a table, the trigger will
automatically fire and will print the message
– YOU CANNOT CREATE A TABLE IN THIS DATABASE
CREATE TABLE tblTest (ID INT)
Example2: Create a trigger that will restrict ALTER operations on a
specific database table.
CREATE TRIGGER trRestrictAlterTable
ON DATABASE
FOR ALTER_TABLE
AS
BEGIN
PRINT 'YOU CANNOT ALTER TABLES'
ROLLBACK TRANSACTION
END
Example3: Create a trigger that will restrict dropping the tables from a
specific database.
CREATE TRIGGER trRestrictDropTable
ON DATABASE
FOR DROP_TABLE
AS
BEGIN
PRINT 'YOU CANNOT DROP TABLES'
ROLLBACK TRANSACTION
END
Note: We cannot implement business logic in DDL Trigger. To be able to
create, alter or drop a table we either have to disable or delete the
trigger.
How to drop a DDL trigger in SQL Server?
Right-click on the trigger in object explorer and select “Delete” from the
context menu. We can also drop the trigger using the following T-SQL
command
DROP TRIGGER trRestrictCreateTable ON DATABASE
DROP TRIGGER trRestrictAlterTable ON DATABASE
DROP TRIGGER trRestrictDropTable ON DATABASE
Let us see an example of how to prevent users from creating, altering,
or dropping tables from a specific database using a single trigger.
CREATE TRIGGER trRestrictDDLEvents
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
BEGIN
PRINT 'You cannot create, alter or drop a table'
ROLLBACK TRANSACTION
END
How to disable a Database Scoped DDL trigger in SQL Server?
Right-click on the trigger in object explorer and select “Disable” from
the context menu. We can also disable the trigger using the following T-
SQL command
DISABLE TRIGGER trRestrictDDLEvents ON DATABASE
How to enable a Database Scoped DDL trigger in SQL Server?
Right-click on the trigger in object explorer and select “Enable” from
the context menu. We can also enable the trigger using the following T-
SQL command
ENABLE TRIGGER trRestrictDDLEvents ON DATABASE
Certain system stored procedures that perform DDL-like operations can
also fire DDL triggers. The following trigger will be fired whenever we
rename a database object using the sp_rename system stored
procedure.
CREATE TRIGGER trRenameTable
ON DATABASE
FOR RENAME
AS
BEGIN
PRINT 'You just renamed something'
END
Let’s create a table and test this.
First, disable the trRestrictDDLEvents trigger: DISABLE TRIGGER
trRestrictDDLEvents ON DATABASE
Then create a table using the command: CREATE TABLE tblTest
(ID INT)
The following code changes the name of the table tblTest to
tblTestChanged. When this code is executed, it will fire the trigger
trRenameTable automatically.
sp_rename ‘tblTest’, ‘tblTestChanged’
When we execute the above code, it will display the below
output.
Server-scoped DDL Triggers in SQL Server:
Let’s understand the need for a Server-Scoped DDL Trigger with an
example. We already created the following trigger.
CREATE TRIGGER trRestrictDDLEvents
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
BEGIN
PRINT 'You cannot create, alter or drop a table'
ROLLBACK TRANSACTION
END
