DDL Trigger In SQL Server
DDL Trigger is used to prevent or tract the records of event which is fired in database or server. You can also prevent the any table and database modification or DDL operation from any specific user. DDL triggers are introduced from SQL Server 2005 version which will be used to restrict the DDL operations such as CREATE, ALTER and DROP commands.
In this article we are going to learn DDL trigger:
Let’s first create a DDL trigger for Create, Alter and Drop:
CREATE TRIGGER tr_DDLEvent_For_Create
ON DATABASE
FOR CREATE_TABLE
AS
BEGIN
PRINT 'YOU CANNOT CREATE TABLE IN THIS DATABASE'
ROLLBACK TRANSACTION
END
CREATE TRIGGER tr_DDLEvent_For_Alter
ON DATABASE
FOR ALTER_TABLE
AS
BEGIN
PRINT 'YOU CANNOT ALTER TABLE IN THIS DATABASE'
ROLLBACK TRANSACTION
END
CREATE TRIGGER tr_DDLEvent_For_Drop
ON DATABASE
FOR DROP_TABLE
AS
BEGIN
PRINT 'YOU CANNOT DROP TABLE IN THIS DATABASE'
ROLLBACK TRANSACTION
END
When you execute this queries, you will find these trigger in your database. As shown in below image:
Let’s test the triggers:
- When you execute below query:
create table test(id int)
you will find:
This error is because we are using ROLLBACK TRANSACTION in script. This prevent from formation of table.
Same for alter and drop command.
I hope this article is useful for you. If you find any mistake or suggestion please comment.
Happy Coding!!
Rahul Kumar
Friend of Hackersfriend.
