🧚 Listen! Beekeeper Studio is a fast, modern, and open source SQL Server GUI Download
April 19, 2023 By Beekeeper Studio Staff

Understanding DDL Triggers in SQL Server 2005 Much like regular DML triggers, DDL triggers fire in response to an event happening on the server. However, DDL triggers do not fire in response to UPDATE, INSERT, or DELETE statements on a table or view. Instead, they fire in response to Data Definition Language (DDL) statements that start with the keywords CREATE, ALTER, and DROP.

You can use DDL triggers to audit and regulate database operations. For instance, you can write a DDL trigger to prevent certain changes to your database schema, or maybe allow the change but record it in a log table for auditing purposes.

Certain system stored procedures that perform DDL-like operations can also fire DDL triggers. The CREATE TYPE statement and the sp_addtype stored procedure can both fire a DDL trigger. However, not all system stored procedures fire DDL triggers. The sp_rename stored procedure for instance does not fire any DDL triggers. You need to test your DDL triggers to determine their responses to system stored procedures.

DDL triggers can be server-scoped or database-scoped. A database-scoped DDL trigger is simply called a database trigger. The following example shows how a database trigger can be used to prevent any table in the current database from being modified or dropped. Keep in mind that DDL triggers fire only after the DDL statements that trigger them are run:

CREATE TRIGGER trigger1
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE AS
PRINT 'You cannot drop or alter this table.'
ROLLBACK

You can temporarily disable a DDL trigger. Disabling a DDL trigger does not drop it. The trigger will still exists as an object; however, it will not be triggered by any of statements on which it was programmed. DDL triggers that are disabled can be re-enabled. Enabling a DDL trigger causes it to fire in the same way the trigger did when it was originally created.

DDL triggers do not fire in response to events that affect local or global temporary tables and stored procedures.

Some DDL statements, like CREATE DATABASE, cannot be used in DDL triggers. These statements are usually asynchronous, non-transacted statements.

Beekeeper Studio Is A Free & Open Source SQL Server GUI

I’ve upgraded from SQL Server and it was a smooth change — Beekeeper Studio enhanced my own workstream, offering a cleaner experience and better performance, as well as flexibility. - Aman Bhattarai

Beekeeper Studio is a fast, intuitive and easy to use SQL Server client. It supports loads of other databases too, and works great on Windows, Mac and Linux.

Beekeeper's Linux version is 100% full-featured, no cut corners, no feature compromises.

What SQL Server Users Say About Beekeeper Studio

Mark Koops

So far the most comprehensive DB admin GUI out there. The only reason I haven’t given 5 stars is I cannot claim to have used it exhaustively only having discovered it two weeks ago. But so far, mySQL, MSSQL and Postgres in one tool is simply incredible.
Planning to purchase once pay day hits.

Neal

I had been using the free version of Beekeeper studio for a while, but when I needed to work with some MongoDB databases I upgraded to the paid version. And honestly, I was up and running in no time. Beekeeper is slick, fast, and it just works. Very intuitive, very easy to use. I work with MongoDB, Postgresql, SQL Server, and other databases in my job, and having one tool that can talk to them all is just wonderful.
Thank you, Matthew and team!

Ready to Improve Your SQL Server Workflow?

download Download Free