DDL Trigger Real-Time Example in SQL Server

DDL Trigger Real-Time Example in SQL Server

In this article, we are going to discuss the DDL Trigger Real-Time Example in SQL Server. Please read the following articles before proceeding to this article.

Database Scoped DDL Trigger in SQL Server

Server-Scoped DDL Trigger in SQL Server

DLL Trigger Execution Order in SQL Server

The DDL triggers in SQL Server will be very much handy to audit and control the DDL changes in databases. Below are such real-time scenarios:

  1. To Track the DLL changes
  2. Track the DDL statement which is fired
  3. Who has fired the DDL statements. For example, we may be interested in identifying who has dropped the table or who have modified the table.
  4. When the DDL statement is fired.
  5. Block the user from doing some DDL changes like DROP TABLE, DROP PROCEDURE etc
  6. Allow the DDL changes only during a specified window (i.e. only during particular hours of the day)
So let us discuss how to audit table changes in SQL Server using a DDL trigger.
-- Create the TableAudit table
CREATE TABLE TableAudit
(
    DatabaseName nvarchar(250),
    TableName nvarchar(250),
    EventType nvarchar(250),
    LoginName nvarchar(250),
    SQLCommand nvarchar(2500),
    AuditDateTime datetime
)
Go

-- The following trigger audits all table changes in all databases on a particular Server
CREATE TRIGGER tr_AuditTableChangesInAllDatabases
ON ALL SERVER
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
BEGIN
    DECLARE @EventData XML
    SELECT @EventData = EVENTDATA()

    INSERT INTO SQL_TESTING_DB.dbo.TableAudit
    (DatabaseName, TableName, EventType, LoginName,
     SQLCommand, AuditDateTime)
    VALUES
    (
         @EventData.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(250)'),
         @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(250)'),
         @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(250)'),
         @EventData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(250)'),
         @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2500)'),
         GetDate()
    )
END

In the above example, we are using the EventData() function which will returns event the data in XML format. The following XML is returned by the EventData() function when I created a table with name = MyTestTable in SQL_TESTING_DB database.

CREATE TABLE MyTestTable
(
    Id INT,
    Name VARCHAR(50),
    Gender VARCHAR(50),
    Salary INT
)

Once you create the above table then select the TableAudit as shown below

SELECT * FROM TableAudit

It will give us the below output.

DDL Trigger Real-Time Example in SQL Server

The EVENTDATA() function give us the data in below XML Format
<EVENT_INSTANCE>
  <EventType>CREATE_TABLE</EventType>
  <PostTime> 2018-10-10 22:05:37.453 </PostTime>
  <SPID>58</SPID>
  <ServerName> LAPTOP-2HN3PT8T </ServerName>
  <LoginName>LAPTOP-2HN3PT8T\Pranaya</LoginName>
  <UserName>dbo</UserName>
  <DatabaseName>SQL_TESTING_DB</DatabaseName>
  <SchemaName>dbo</SchemaName>
  <ObjectName>MyTestTable</ObjectName>
  <ObjectType>TABLE</ObjectType>
  <TSQLCommand>
    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON"
                ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON"
                ENCRYPTED="FALSE" />
    <CommandText>
     CREATE TABLE MyTestTable
     (
       Id INT,
       Name VARCHAR(50),
       Gender VARCHAR(50),
       Salary INT
      )
   </CommandText>
  </TSQLCommand>
</EVENT_INSTANCE>

In the next article, I will discuss the Creating and Managing Users in SQL Server.

SUMMARY

In this article, I try to explain the DDL Trigger Real-Time Example in SQL Server. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.

Leave a Reply

Your email address will not be published. Required fields are marked *