Using DDL triggers to monitor database changes in SQL Server 2005

Adding, deleting, or modifying the object of the database, once it is wrong, can cause a lot of trouble, requiring a database administrator or developer to rewrite code for related entities that might be affected.

In order to be able to track the problem and locate the root cause of the problem in the event of a change in the structure of the database, we can use DDL triggers to record operations like "user-created Tables", which can greatly reduce the complexity of tracking and locating database schema changes.

1. Introduction to DDL triggers

A DDL trigger is a special trigger that fires when it responds to a data definition language (DDL) statement. They can be used to perform administrative tasks in the database, such as auditing and standardizing database operations.

Using DDL triggers, you can accomplish the following purposes:

To prevent certain changes to the database schema.

You want something to happen in the database in response to changes in the database schema.

To record changes or events in the database schema.

As with standard DML triggers, DDL triggers execute stored procedures in response to an event. Unlike standard DML triggers, however, they do not execute stored procedures when responding to an UPDATE, INSERT, or DELETE statement on a table or view. They execute stored procedures primarily in response data definition language (DDL) statements. These include statements such as CREATE, ALTER, DROP, GRANT, DENY, REVOKE, and UPDATE STATISTICS. system stored procedures that perform DDL-type operations can also fire DDL triggers.

2. How to use DDL triggers

As a first step, you need to create a table that records all DDL operations in the database scope.

The following code creates a table in the AdventureWorks sample database to hold all DDL action records:

USE AdventureWorks
GO
CREATE TABLE AuditLog
(ID INT PRIMARY KEY IDENTITY(1,1),
Command NVARCHAR(1000),
PostTime NVARCHAR(24),
HostName NVARCHAR(100),
LoginName NVARCHAR(100)
)
GO