DDL triggers fire when somebody changes the shape of your database rather than the data in it. A dropped table, an altered column, a new login. They are the cheapest way to answer the question that comes up after every unexplained change, which is simply who did this.
DDL and DML Are Different Jobs
A normal trigger, a DML trigger, watches INSERT, UPDATE and DELETE. It cares about rows.
A DDL trigger watches CREATE, ALTER and DROP. It cares about objects. Nobody inserted anything, somebody rebuilt something, and that is the kind of change that is hardest to explain a week later.
Two Levels
A database level trigger lives in one database and sees changes to objects in it. Tables, views, procedures, users.
A server level trigger lives on the instance and sees changes that are bigger than one database. Logins, new databases, linked servers. It fires no matter which database the person was in.
Pick the smaller one that answers your question. A server level trigger that fires on everything slows every deployment down.
A Database Level Trigger
This one ran on SQL Server 2025. It reports the event rather than blocking it.
CREATE TRIGGER trg_DDLDemo ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
BEGIN
DECLARE @d xml = EVENTDATA();
SELECT @d.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)') AS event_type,
@d.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)') AS object_name,
@d.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(128)') AS login_name;
ENDCreating and then dropping a table produced exactly this:
event_type object_name login_name
CREATE_TABLE TrigTest CORP\dba
event_type object_name login_name
DROP_TABLE TrigTest CORP\dbaEVENTDATA Is the Whole Point
EVENTDATA returns XML describing what just happened. It is only populated inside the trigger. Call it anywhere else and you get nothing.
The fields worth knowing are EventType, ObjectName, SchemaName, DatabaseName, LoginName and TSQLCommand. That last one holds the statement that was run. It is usually the field people wanted all along.
A SELECT inside a trigger is fine for a demonstration. In production nobody is watching. Write it to a table instead.
CREATE TABLE dbo.DDLLog (
id int IDENTITY(1,1) PRIMARY KEY,
logged_at datetime2(0) NOT NULL DEFAULT SYSDATETIME(),
event_type nvarchar(100),
object_name nvarchar(128),
login_name nvarchar(128),
command nvarchar(max)
);Then have the trigger insert into it, pulling TSQLCommand out of the same XML.
Blocking a Change Instead of Recording It
A DDL trigger can refuse. ROLLBACK inside it cancels the statement that fired it.
CREATE TRIGGER trg_NoDrop ON DATABASE
FOR DROP_TABLE
AS
BEGIN
RAISERROR ('Tables are not dropped on this database without a change request.', 16, 1);
ROLLBACK;
ENDUseful where a dropped table ruins an afternoon. Be careful. It stops your own deployment scripts too.
A Server Level Trigger
CREATE TRIGGER trg_LoginAudit ON ALL SERVER
FOR CREATE_LOGIN, ALTER_LOGIN, DROP_LOGIN
AS
BEGIN
DECLARE @d xml = EVENTDATA();
INSERT INTO master.dbo.LoginAudit (event_type, object_name, login_name)
SELECT @d.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@d.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)'),
@d.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(128)');
ENDNote ON ALL SERVER rather than ON DATABASE, and note that the table it writes to has to exist somewhere every session can reach.
Finding the Ones You Already Have
SELECT name, parent_class_desc, create_date, modify_date, is_disabled
FROM sys.triggers
WHERE parent_class IN (0, 1) AND is_ms_shipped = 0;
SELECT name, create_date, modify_date, is_disabled
FROM sys.server_triggers;Two views, because the two levels are stored apart. A server trigger never appears in sys.triggers. People waste time looking for it there.
How to Get Back In
Read this part before you write your first one. A bad DDL trigger can lock you out of your own schema. A server level one can be worse.
DISABLE TRIGGER trg_NoDrop ON DATABASE;
DISABLE TRIGGER ALL ON DATABASE;
DISABLE TRIGGER ALL ON ALL SERVER;If a server level trigger refuses even that, start SQL Server in single user mode with the minimal configuration flag. Server triggers do not fire then. That is deliberate, and it exists for this exact situation.
What I Would Actually Use Them For
Recording schema changes with the statement text, so who changed the column has an answer. Blocking DROP on a few tables that must never go. Noticing a new login where logins should be rare.
What I would not do is build a full audit system out of them. SQL Server Audit was made for that. It is cheaper, and it does not run your code in the middle of a deployment.
A DDL trigger is not an audit system, it is a bell on the door of the room where the shape of your database lives.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
50 Comments. Leave new
Hello Niranjan,
Create an INSTEAD OF trigger that raises an error, so the INSERT, UPDATE or DELETE is stopped and the caller knows why:
CREATE TRIGGER dbo.trg_BlockChanges
ON dbo.YourTable
INSTEAD OF INSERT, UPDATE, DELETE
AS
BEGIN
THROW 50001, ‘Changes to this table are not allowed.’, 1;
END;
An INSTEAD OF trigger with no statements inside also stops the change, but silently, so the application thinks it worked. If you only need to stop certain users, DENY INSERT, UPDATE, DELETE on the table is simpler than a trigger.
Regards,
Pinal Dave
hello sir,
can we pass parameter to trigger
No. It is not possible
Thank you sir.
Dear Sir i am using below code in Sql2005 and getting error . Please help me.
USE Test
GO
CREATE TRIGGER PreventDropSP
ON Test
FOR DROP_PROCEDURE
AS
PRINT ‘Dropping Procedure is not allowed.’
ROLLBACK;
GO
“””Msg 170, Level 15, State 1, Procedure PreventDropSP, Line 6
Line 6: Incorrect syntax near ‘DROP_PROCEDURE’.””””””
hai ved,
use test
go
create trigger preventdropsp
on database
for drop_procedure
as
print PRINT ‘Dropping Procedure is not allowed.’
ROLLBACK;
GO
Regards
kalyan
Hello Ved,
Use ON DATABASE rather than ON Test. Run this in the database you want to protect:
USE Test;
GO
CREATE TRIGGER PreventDropSP
ON DATABASE
FOR DROP_PROCEDURE
AS
BEGIN
PRINT ‘Dropping procedures is not allowed.’;
ROLLBACK TRANSACTION;
END;
GO
Please test it in a test database first and keep a way to disable the trigger if it blocks a needed change.
Regards,
Pinal Dave
Dear Sir
Thanks for your reply .However i am getting same error ..
Can you please give me any one of DDL Trigger Example.
Thanks
Ved
Hello,
I wish to track when anyone does INSERT,UPDATE, DELETE on any of the tables in a database. Is it possible to write database level trigger for this case?
Thanks,
-Saroj
hi sir i m creating trigger as
create trigger check_id
ON dbo.teacher
AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF(t_no<100)
THEN
print('not allowed');
END;
but getting an error
Msg 156, Level 15, State 1, Procedure check_id, Line 8
Incorrect syntax near the keyword 'THEN'
pls. help me as ma a beginner in sql .
Hi,
There is an syntax error in the IF. check this out.
create trigger check_id
ON dbo.interest_rate
AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF(select Interest_rate from inserted) <100
print('not allowed')
END
Help Plz..
Well hello there.. i am facing problem in updating using trigger
following the trigger..
well my requirements is to update the column . I want to store(update) the prev. value while updating in another columns correspond to id.
like prevsal and newsalary.
following trigger which i have created…
*as we know when we update then (inserted and deleted will fire also in which the old value which we can retrieve using (select * from deleted))
ALTER trigger [dbo].[tb] on [dbo].[tb_name]
for insert, update
as
declare @count int
declare @loop int
declare @empid int
select @loop =1
select @count = (select * from tblname)
WHILE(@loop <= @count)
update tblname set newsalary=1200 where id=@loop and date='12/12/2010'
set @loop= @loop+1
update tblname set prevsal=(select newsalary from deleted)
End
*if there any Error then let me know well i am updating the field with column name newsalary but for column name prevsal they givng me NULL value except old value..
hope you can understand my requirement
with regards
vikram
Hi Phillip,
I am trying to create a simple DDL trigger on server level, everything is fine on my local machine but when I am trying on our server (SQL 2005 Clustered) I am getting Error, any idea?
Create TRIGGER test
ON ALL SERVER
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
print ‘Hello’
END
Msg 1098, Level 15, State 1, Procedure test, Line 7
The specified event type(s) is/are not valid on the specified target object.
i want to execute the trigger whenever the new login is created i.e. new entry added to master..syslogins table ,
Which server trigger i need to have and whats the synatx and minimum permissions needed to create it , now i am security admin.
Hello.
Is it possible to create a trigger to insert, update and delete data from all tables of current database?
Something like this:
CREATE TRIGGER myTrigger
ON
FOR INSERT, UPDATE, DELETE
AS IF (@@ERROR 0)
BEGIN
INSERT into #tableSomething VALUES (1)
END
I’m using SQL Server 2008.
Thank you
TAP
Great stuff
Do you have any way to get alerted (paged/emailed) whenever a database has an Auto_grow?
Hello sir,
If I created a ddl trigger. How can i drop it??
Display all Triggers
SELECT * FROM sys.server_triggers
Drop a Trigger
DROP TRIGGER [DROP_Your_Trigger] ON ALL Server
or see this link for DROP TRIGGER SYNTAX
https://docs.microsoft.com/en-us/sql/t-sql/statements/drop-trigger-transact-sql?view=sql-server-2017
Use drop trigger trigger_name
If your trigger was created ON DATABASE, then you need to DROP TRIGGER trigger_name ON DATABASE, and if it was created ON SERVER then . . .well I’m sure you can work it out!
I m working on Trigger which will prevent database users from executing update statement on table.
Sir, I Want to hide error 3609 after execution of Trigger.
while trying to drop stored procs, error:
Dropping Procedure is not allowed. DDL Trigger is preventing this from happening. To drop stored procedure run following script.
Kindly help me….
I m beginner in triggers i got this error..
plz help me..
thank u..
CREATE TRIGGER preventDB
ON DATABASE
FOR DROP_PROCEDURE
AS
PRINT ‘HELLO’
ROLLBACK
GO
error is…
Incorrect syntax near the keyword ‘DATABASE’.
Dear Pinal Dave,
Where this magic tables are created?
When they will created?
When they will dropped?
How long it will be used?
Thanks,
Prakash.
Can we access the view after the table on which it has been created is dropped ?
I created a DDL trigger which prevents adding new file to the database , After creating the trigger i try to alter the database by adding a new file , The output message says ” The transaction ended in trigger , the batch has been aborted” But still i see the new file added to the database , please share your valuable suggestion to make my trigger work.
DDL Trigger to Prevent adding new file to database:
CREATE TRIGGER prevent_addfile
ON ALL SERVER
FOR ALTER_DATABASE
AS
BEGIN
PRINT ‘ALTER_DATABASE Issued.’
DECLARE @EventData XML,
@TSQL varchar(2000)
SET @EventData=EVENTDATA()
–SELECT @TSQL = @EventData.value(‘(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]’, ‘nvarchar(max)’)
–PRINT @TSQL
–IF CHARINDEX (‘ADD’,@TSQL) > 0
–if( isnull(charindex(‘ADD’,@TSQL), 0) > 0 )
IF @EventData.value(‘(/EVENT_INSTANCE/DatabaseName)[1]’, ‘varchar(50)’) LIKE ‘%dba%’
AND @EventData.value(‘(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]’, ‘nvarchar(max)’) LIKE ‘%ADD%’
RAISERROR (‘dbfile cannot be modified’, 16, 1)
ROLLBACK
END
GO
Output message after triggering the Alter database command on the “dba” database
ALTER_DATABASE Issued.
Msg 50000, Level 16, State 1, Procedure prevent_addfile, Line 21
dbfile cannot be modified
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.