Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)')
) 
share|improve this question
    
This is exactly what SQL Audit was made for... msdn.microsoft.com/en-us/library/cc280386.aspx –  Sean Gallardy Jul 30 at 1:09
    
I don't have access on SQL Audit –  Emman Bangis Jul 30 at 1:29

1 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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.