Another usage of Database Triggers is track Data Definition Language (DDL) changes that occur in a database. Today, I am going to enhance the AUTOS database script to add such auditing. This auditing is a very good practice when multiple people have syadmin rights to make such changes. First, I am going to create a schema called Audit Database Tracking (ADT) to seperate the data tables from audit tracking tables.
<span style="color: #008000; font-size:small;">-- Delete existing schema.
IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'ADT')
DROP SCHEMA [ADT];
-- Create a ADT schema
CREATE SCHEMA [ADT] AUTHORIZATION dbo;
Second, I am going to create a table to hold the keep track of the DDL changes that are…