Systems Engineering and RDBMS

DDL Triggers in SQL Server 2005

Posted by decipherinfosys on September 27, 2007

We all are familiar with DML (Data Manipulation Language) triggers which can be invoked after any DML operations (used for update, insert and delete commands).  We can achieve auditing functionality using DML triggers but this does not help us in auditing if someone has altered the definition of the tables or dropped the tables etc.. We can achieve this by using DDL (Data Definition Language) triggers introduced in SQL Server 2005.

As the name suggests, DDL triggers do not get fired for any DML operations but get fired for CREATE, ALTER, DROP, GRANT, DENY, REVOKE, and UPDATE STATISTICS DDL statements. Some of the system stored procedures which perform CREATE like operations, also fire the DDL trigger. One exception is sp_rename. Sp_rename procedure does not invoke DDL trigger even though it performs the operation of renaming the object. Most of the time, DDL triggers are used to prevent modification in the database or to audit the DDL changes in the database.

DDL triggers can be created at the database level for a specific database or at the server/instance level. For creating trigger at the database level, we have to use the ‘ON DATABASE’ clause and for creating trigger at the server/instance level we have to use the ‘ON ALL SERVER’ clause. When created at the server level, it keeps track of DDL changes for any database on that specific instance.

Let us create a table first.

CREATE TABLE dbo.AUDIT_EVENT
(
AUDIT_EVENT_ID INT IDENTITY(1,1),
EVENT_TEXT     VARCHAR(MAX),
ACTION_LOGIN   VARCHAR(100),
ACTION_DATE    DATETIME,
CONSTRAINT PK_AUDIT_EVENT PRIMARY KEY(AUDIT_EVENT_ID)
)
GO

In the above table, we will record the information pertaining to any DDL operation.  Let us create trigger to record the changes at database level.

CREATE TRIGGER AUDIT_TRIG
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE, CREATE_TABLE
AS
INSERT INTO dbo.Audit_Event(Event_Text, Action_Login, Action_date)
SELECT EVENTDATA().value(‘(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]’,’nvarchar(max)’), original_login(), getDate()
GO

In the trigger shown above, we are using the EVENTDATA() function to obtain the actual text. We will cover more about EVENTDATA() function in future blog,. We have adopted the idea of using EVENTDATA() function from BOL example. Once trigger is in place, we can start testing it by creating, altering and dropping the table. Let us try to create table first. We will also alter it to add a new column and then we will drop it.

CREATE TABLE TEST(ID INT IDENTITY(1,1))
GO
ALTER TABLE TEST ADD TEST_DESC VARCHAR(30)
GO
DROP TABLE TEST
GO

For all of the above statements, once the statement is executed successfully, trigger will get fired. Let us check our audit table now.

SELECT Event_Text,Action_Login, Action_Date FROM AUDIT_EVENT
GO

Here is the result set:

Event_Text                                  Action_login  Action_Date
------------------------------------------- ------------- -----------------------
CREATE TABLE TEST(ID INT IDENTITY(1,1))     sa            2007-09-26 13:43:57.177
ALTER TABLE TEST ADD TEST_DESC VARCHAR(30)  sa            2007-09-26 13:43:57.223
DROP TABLE TEST                             sa            2007-09-26 13:43:57.223

From the above result set, we know what command got executed, at what time and by whom. This prevents unwanted surprises during the development phase and in addition,  we can keep track of database changes made within the application. Similar to the example shown above, we can also create trigger for CREATE_PROCEDURE, DROP_PROCEDURE event types to audit the procedure changes.

2 Responses to “DDL Triggers in SQL Server 2005”

  1. […] it has limited but very important usage. As far as Transact-SQL goes, it can be used only in DDL triggers or logon triggers which was introduced in SP2 of SQL Server […]

  2. […] 2008 SQL Server 2005 already has a good set of auditing capabilities namely C2 Audit Mode Option, DDL Triggers and SQL Server Profiler (besides writing one’s own DML triggers). In SQL Server 2008, one can […]

Sorry, the comment form is closed at this time.

 
%d bloggers like this: