Sql-server – Trigger on a Schema

insertschemasql servertriggerupdate

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)')
)

Best Answer

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

Related Solutions

Postgresql – Inherit audit columns and triggers

From http://postgresql.1045698.n5.nabble.com/DELETE-and-UPDATE-triggers-on-parent-table-of-partioned-table-not-firing-td5683717.html:

The DELETE and UPDATE triggers need to be on the child tables. An operation on a child doesn't fire the triggers of the parent.

As such, the following code must also be executed:

CREATE TRIGGER ${name}_delete BEFORE DELETE ON $name
    FOR EACH ROW EXECUTE PROCEDURE audit_delete();

The audit_delete() function won't work as written in the question: setting the deleted date is not possible using OLD.deleted = current_timestamp. Instead, assuming that the table has a primary key field of id:

CREATE OR REPLACE FUNCTION audit_delete()
  RETURNS trigger AS
$BODY$
BEGIN
  EXECUTE
    'UPDATE ' || TG_TABLE_NAME || ' SET deleted = current_timestamp WHERE id = ' || OLD.id;
  RETURN NULL;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 1;

Sql-server – sysadmin cannot drop login when ddl audit trigger enabled

After a considerable amount of testing, I finally discovered the reason behind this error. The client connection explicitly set ANSI_WARNINGS and CONCAT_NULL_YIELDS_NULL OFF. XML data operations, such as @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), require both to be ON. I had attempted to override these within the trigger, but I may have placed them wrong. The final code below works, even with the explicit SET options in the connections from Great Plains:

CREATE TRIGGER [ddl_log] 
ON ALL SERVER 
FOR DDL_DATABASE_LEVEL_EVENTS, DDL_SERVER_LEVEL_EVENTS 
AS
BEGIN
SET NOCOUNT ON;
SET ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL ON;

DECLARE @data XML
SET @data = EVENTDATA() 

EXECUTE AS LOGIN='<dummy login>'
INSERT admin.dbo.ddl_audit (PostTime, DB_User, [Event], [TSQL], Host, DatabaseName)
VALUES (
   GETDATE(),
   CONVERT(NVARCHAR(100), ORIGINAL_LOGIN()),
   @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), 
   @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'), 
   CONVERT(NVARCHAR(100), HOST_NAME()),
   @data.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(100)')
   ) ;
REVERT 
END

As an alternative, I also could have simply inserted EVENTDATA() as an XML LOB into a table, rather than parsing it out into columns. Because I would not be manipulating the XML, the SET options do not matter. Then I would just build an XML index for querying performance, and construct a view to use for audit log reporting that parses the XML in the view definition, in the same manner as I am doing above in my INSERT statement.

Thanks to Max for pointing me in a different research direction, and @AaronBertrand on #sqlhelp who helped me with correct SET options within the body of the trigger.

Best Answer

Related Solutions

Postgresql – Inherit audit columns and triggers

Sql-server – sysadmin cannot drop login when ddl audit trigger enabled

Related Question