Trigger on a Schema

Question

Is it possible to create a Trigger on a Schema? I have to audit the changes on my tables. But I have a lot of tables and I don't one to create a trigger on them one by one.

--Database level trigger.    
CREATE TRIGGER AuditProcChanges
ON DATABASE
FOR CREATE_PROC, ALTER_PROC, DROP_PROC, CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS

DECLARE @ed XML
SET @ed = EVENTDATA()

INSERT INTO DDLAudit (PostTime, DatabaseName, Event, ObjectName, TSQL, Login) 
VALUES
(
    GetDate(),
    @ed.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),
    @ed.value('(/EVENT_INSTANCE/EventType)[1]', nvarchar(100)'),
    @ed.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'),
    @ed.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'),
    @ed.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')
) 

Answer 1

you are almost on the right track with a slight misunderstanding.

But I have a lot of tables and I don't one to create a trigger on them one by one.

This is not correct. You just have to create SERVER LEVEL / DATABASE LEVEL TRIGGER that will take care of the database events that occur on the server instance or database. You can even filter out the databases that you need to audit for schema change programatically if you are using server level trigger.

Refer to : DDL Event Groups

Code Project has a working example - Send Email Alert on Database Schema Change in SQL Server

Be careful as this can generate a lot of data as the trigger will fire for every alter, create, drop events.

My preferred way of doing this is using Event Notification using DDL_DATABASE_LEVEL_EVENTS- creating a notification on an individual databases.

Event notifications are asynchronous and run outside the transaction that fired the DDL event. The transaction that fired the DDL event doesn't need to wait for you auditing code to complete so there is much lower performance overhead associated with event notifications. It also means that it's not possible to rollback the DDL event using an event notification, but in this we just want to audit DDL events - not to prevent them. If you just want to audit events, event notifications are a generally a better option than DDL Triggers.

if you want a GUI for DDL Triggers or Event Notification - TSQL DDL Code History Tool is a good choice. David Wiseman (author of the tool) has written a good article on SSC - A DDL Auditing Solution