SQL Server Triggers – Part 2 DDL & LOGON Triggers

In SQL Server, triggers are database objects which will get executed whenever a triggering event happens on the database or server. Triggers play a key role in achieving business requirements like alerting targeted people based upon a condition achieved, starting a job, or other operations. In the previous article on DML triggers, we talked about Triggers, types of Triggers, and various Trigger options available for DML Triggers. In this article, we will explore SQL DDL and LOGON triggers.

DDL Triggers

DDL Triggers can be fired for a variety of Server or Database Scoped events including both DDL and DCL commands. DDL stands for Data Definition Language which is used to CREATE, ALTER, DROP any objects and DCL stands for Data Control Language statements like GRANT, DENY and REVOKE commands. Below are the characteristics of SQL DDL Triggers.

1. DDL Triggers can be created on the Database Level or Server instance level covering a wide variety of DDL operations or DDL alike operations, e.g. DCL commands.
2. DDL Triggers can be only invoked or fired as a FOR or AFTER Trigger type. SQL Server doesn’t support INSTEAD OF DDL Trigger and we can see how to prevent some DDL operations via DDL Triggers.
3. SQL Server has built-in functions like EVENTDATA() and IS_MEMBER() for usage within DDL triggers to obtain more information related to the Trigger events.
   1. EVENTDATA() function returns complete details about Database or Server scoped events in XML format within the scope of the Database or Server scoped DDL trigger or Logon triggers as well. EVENTDATA() function returns the complete Event Details for the session that performs the DDL or Logon activities. EVENTDATA() returns the below details
      • EventType – Type of the Event which fires the DDL trigger available in sys.trigger_event_types table.
      • PostTime – Time the Event was triggered or posted.
      • SPID – Session ID of the event.
      • ServerName – SQL Server instance name in which the event was triggered.
      • LoginName – SQL Server Login name which performed the event.
      • UserName – User Name of the Login which will be dbo by default.
      • DatabaseName – Database Name under which the DDL Trigger was fired.
      • SchemaName – Schema Name of the Object which was impacted.
      • ObjectName – Object Name which was impacted.
      • ObjectType – Type of SQL Server Object like Table, View, Stored Procedure.
      • TSQLCommand – T-SQL Script which was executed by a user which invoked the DDL Trigger.
      • SetOptions – SET options used by User or Client like SSMS while the TSQLCommand was executed.
      • CommandText – Actual DDL or DCL statements with the DDL Event specified in sys.trigger_event_types table.
   2. IS_MEMBER() function returns whether the current user is a member of the Windows group or SQL Server Database role or not.
4. System DMV sys.triggers stores the list of all Database scoped triggers. We can use the below query to fetch the details of all Database scoped DDL triggers.

SELECT * 
FROM sys.triggers
WHERE type = 'TR';

5. System DMV sys.server_triggers stores the list of all Server scoped triggers and we can use the below query to fetch the details about all Server scoped DDL triggers.

SELECT * 
FROM sys.server_triggers;

6. DDL Trigger definitions can be viewed if the trigger is not encrypted by using any of the below options from sys.sql_modules or using OBJECT_DEFINITION() function or using sp_helptext stored procedure.

SELECT OBJECT_SCHEMA_NAME(object_id, db_id()) Schema_name, OBJECT_NAME(object_id) Trigger_Name, definition
FROM sys.sql_modules  
WHERE object_id = OBJECT_ID(<trigger_name>);   

SELECT OBJECT_DEFINITION (OBJECT_ID(<trigger_name>)) AS ObjectDefinition; 

EXEC sp_helptext '<trigger_name>';

7. All possible DDL Events are available in sys.trigger_event_types table and can be viewed using the below query.

SELECT *
FROM sys.trigger_event_types;

The syntax of a DDL Trigger is:

CREATE TRIGGER <trigger_name>
ON < ALL SERVER | DATABASE > 
[ WITH <DDL_trigger_option> [ ,...n ] ]  
{ FOR | AFTER } <event_type>
AS { sql_statement | EXTERNAL NAME <method specifier> }  

Creating Database Scoped DDL Trigger

Let’s create a Database Scoped trigger to track all Table Creations and log into a Logging table named Track_DDL_Changes using the script below.

CREATE TABLE Track_DDL_Changes (EventData xml, PostDtm datetime)
GO
CREATE TRIGGER TR_D_CREATETABLE
ON DATABASE
FOR CREATE_TABLE
AS
BEGIN
	INSERT INTO Track_DDL_Changes
	SELECT EVENTDATA(),GETDATE()
END
GO

Let’s create a new table named trigger_test and verify whether the CREATE TABLE event was audited or not by using the below script.

CREATE TABLE Trigger_Test ( a int, b datetime);

Selecting the data from the Track_DDL_Changes table shows the above CREATE_TABLE event was captured successfully as shown below:

Clicking the EventData value will open the XML EVENTDATA() value in a new window as shown below.

We are able to verify the complete details about the triggering event via the EVENTDATA() function and hence the EVENTDATA() function would play a significant role for any DDL or LOGON triggers.

We can further enhance our DDL Trigger with the help of the EVENTDATA() function and XML parsing and prevent anyone from creating any table in the test database using the script given below:

CREATE TRIGGER TR_D_PREVENT_CREATETABLE
ON DATABASE
FOR CREATE_TABLE
AS
BEGIN
   SELECT EVENTDATA().value  
        ('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')  
   RAISERROR ('Creation of New tables restricted in this database, Kindly contact DBA.', 16, 1)   
   ROLLBACK  
END
GO

Creation of Database Scoped trigger completed successfully and let’s verify by creating another table using the script below.

CREATE TABLE Trigger_Test1 (a int, b datetime);

The trigger has prevented us to create new tables on this database and left a meaningful message to users as well. We can similarly handle any other DDL or Server scoped events to match the requirements.

To drop Database scoped DDL trigger, we need to use the below syntax:

DROP TRIGGER <trigger_name> ON DATABASE;

And to drop the trigger we have created just now, the script would be

DROP TRIGGER TR_D_PREVENT_CREATETABLE ON DATABASE;

To view the database scoped DDL triggers in SSMS, expand the Test database -> Programmability -> Database Triggers as shown below.

Similar to SQL DML Triggers, DDL triggers can be dropped, disabled, or enabled by just right-clicking the Trigger name as shown below.

Via T-SQL, we can drop or disable or enable Database scoped DDL trigger using the below syntax:

-- DROP Database scoped DDL Trigger
DROP TRIGGER <trigger_name> ON DATABASE;
-- Enable Database scoped DDL Trigger
ENABLE TRIGGER <trigger_name> ON DATABASE;
-- Disable Database scoped DDL Trigger
DISABLE TRIGGER <trigger_name> ON DATABASE;

To disable the trigger we have created, we might need to use the below script.

-- DROP Database scoped DDL Trigger
DROP TRIGGER TR_D_PREVENT_CREATETABLE ON DATABASE;
-- Enable Database scoped DDL Trigger
ENABLE TRIGGER TR_D_PREVENT_CREATETABLE ON DATABASE;
-- Disable Database scoped DDL Trigger
DISABLE TRIGGER TR_D_PREVENT_CREATETABLE ON DATABASE;

Creating Server Scoped DDL Trigger

Server scoped DDL trigger follows the same syntax similar to Database scope DDL trigger except that the events will be based upon the Server scope.

Let’s try to create a Server scoped DDL trigger to prevent any user from creating a new database on this server instance using the below script.

CREATE TRIGGER TR_S_PREVENT_CREATEDATABASE
ON ALL SERVER
FOR CREATE_DATABASE
AS
BEGIN
   SELECT EVENTDATA().value  
        ('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')  
   RAISERROR ('Creation of New Databases restricted in this Instance, Kindly contact DBA.', 16, 1)   
   ROLLBACK  
END
GO

When trying to create a new database using the below command, we will receive an error as shown below.

CREATE DATABASE DATABASE_TEST;

In SSMS, Server scoped DDL Triggers under Triggers in the Server Objects section as shown below.

We can drop, disable or enable the Server scoped DDL trigger by simply right-clicking the Server Scoped DDL Trigger as shown below.

Via T-SQL, we can Drop or Disable or Enable using the below command.

-- DROP Server scoped DDL Trigger
DROP TRIGGER TR_S_PREVENT_CREATEDATABASE ON ALL SERVER;
-- Disable Server scoped DDL Trigger
DISABLE TRIGGER TR_S_PREVENT_CREATEDATABASE ON ALL SERVER;
-- Enable Server scoped DDL Trigger
ENABLE TRIGGER TR_S_PREVENT_CREATEDATABASE ON ALL SERVER;

The purpose of DDL Triggers

1. To audit any DDL events happening across database or server level.
2. To prevent any DDL events happening across database or server level.
3. To alert whenever any DDL events happening across database or server level.

Logon Triggers

Logon Triggers as the name indicates are executed for LOGON events in SQL Server. Once the Authentication phase completes for a Login event, the LOGON Trigger script would get executed in addition to the Login activity. If the login is not authenticated successfully, then LOGON triggers won’t be fired. Logon triggers will be listed in SSMS under the Triggers section of Server Objects. The syntax of a LOGON Trigger is as follows:

CREATE TRIGGER <schema_name.trigger_name>
ON ALL SERVER
{ FOR| AFTER } LOGON    
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }  

Create Triggers

Let’s create a simple LOGON trigger to capture more information about the LOGON event from the EVENTDATA() function as shown below.

CREATE TABLE Track_LOGON_EVENTS (EventData xml, PostDtm datetime)
GO
CREATE TRIGGER TR_LOGON
ON ALL SERVER
FOR LOGON
AS
BEGIN
	INSERT INTO Track_LOGON_EVENTS
	SELECT EVENTDATA(),GETDATE();
END
GO

The above LOGON trigger will capture all details about a login activity similar to what we have noticed while using the EVENTDATA() function in DDL Trigger. We should be careful while planning to use LOGON triggers as if there are any logic errors inside the trigger, it wouldn’t allow anyone or most users to connect to the instance of SQL Server.

To DROP, Disable or Enable LOGON triggers, we can use the below script.

-- DROP LOGON Trigger
DROP TRIGGER TR_LOGON ON ALL SERVER;
-- Disable LOGON Trigger
DISABLE TRIGGER TR_LOGON ON ALL SERVER;
-- Enable LOGON Trigger
ENABLE TRIGGER TR_LOGON ON ALL SERVER;

The purpose of LOGON Triggers

1. To audit any LOGON events happening on the server.
2. To prevent any LOGON events happening on the server
3. To alert whenever any LOGON events happening on the server.

Trigger Properties

sp_settriggerorder

sp_settriggerorder is used to define the order of trigger execution only for the first and last triggers. If there are more than 2 DML triggers in a table let’s say 5 DML triggers, then we can define the first DML trigger and last DML trigger but cannot define the order of the middle 3 triggers.

Note: Setting the FIRST or LAST option is specific to a particular Event category for DML triggers. For example in a table having 3 INSERT triggers, we can define which INSERT trigger is FIRST and which INSERT trigger is LAST. If you have 3 triggers on a table like INSERT, UPDATE, and DELETE ones, then there is no need to set the Trigger order condition.

The syntax for setting the trigger order would be like this:

exec sp_settriggerorder @triggername = '<trigger_schema_name.trigger_name>' 
    , @order = 'FIRST' | 'LAST'   
    , @stmttype = '<trigger event type>'   
    , @namespace = 'DATABASE' | 'SERVER' | 'NULL'

For DDL triggers, we can define the first and last Server Scoped triggers and then define the first and the last Database Scoped triggers. For example, if we have 5 Server Scoped triggers and 5 Database Scoped triggers, then the order can be defined like this:

1. First trigger for Server Scoped DDL trigger
2. 3 Other Server Scoped DDL triggers in random order
3. Last trigger for Server Scoped DDL trigger.
4. First trigger for Database Scoped DDL trigger (One per database)
5. 3 Other Database Scoped DDL triggers in random order
6. Last trigger for Database Scoped DDL trigger.

With respect to setting the first or the last option, the database scoped DDL triggers can be ordered within the database and the Server scoped DDL Triggers at the Instance level.

Even though SQL Server allows us to create lots of triggers on a table, it is recommended to analyze the requirements of the trigger carefully for better maintenance and troubleshooting.

Recursive Triggers

SQL Server also supports invoking triggers recursively for DML triggers. Recursive Triggers can be classified as direct or indirect as shown below.

Direct Recursive Triggers – User or Application updates a record in Table A. UPDATE Trigger A in Table A gets fired and updates Table A again. Since the record in Table A was updated via Trigger, it will again invoke UPDATE Trigger A and this will happen recursively.

Let’s create a Direct Recursive Triggers on the Sales table using the below script:

CREATE TRIGGER TR_UPD_Recursive_Sales ON Sales
FOR UPDATE 
AS
BEGIN
  UPDATE Sales 
  SET SalesDate = GETDATE() 
  WHERE SalesId = (SELECT SalesId FROM Inserted)
END
GO

Execute the below script:

UPDATE Sales 
SET SalesDate = GETDATE() 
WHERE SalesId = 3;

Indirect Recursive Triggers – User or Application updates a record in Table A. The UPDATE Trigger A in Table A gets fired and updates a record in Table B. If Table B has an UPDATE trigger to update records back to Table A, it will invoke the UPDATE trigger in Table A which will happen recursively.

Let’s create an Indirect Recursive Trigger on IDR_Test1 and IDR_Test2 tables using the below script:

DROP TABLE IDR_Test1
DROP TABLE IDR_Test2

CREATE TABLE IDR_Test1 (PK int NOT NULL);
GO
INSERT INTO IDR_Test1 
values (10),(20)
GO
CREATE TABLE IDR_Test2 (PK int NOT NULL);
GO
INSERT INTO IDR_Test2
values (10),(20)
GO

CREATE TRIGGER TR_IDR_Test1
ON IDR_Test1
FOR UPDATE 
AS
BEGIN
	UPDATE IDR_Test2
	SET PK = 30
	WHERE PK IN (SELECT PK FROM inserted);
END
GO
 
CREATE TRIGGER TR_Temp2
ON IDR_Test2
FOR UPDATE 
AS
BEGIN
	UPDATE IDR_Test1
	SET PK = 30
	WHERE PK IN (SELECT PK FROM inserted);
END
GO

Execute the below script:

UPDATE IDR_Test1
SET PK = 1
WHERE PK = 10;

To avoid these kinds of Recursive triggers invocation at the Database level, SQL Server has an option called RECURSIVE_TRIGGERS at every database level to break the Recursive Trigger triggering. By default, the Recursive trigger option is set to False for a database. Enable only as required after careful consideration of the performance impacts or data changes involved.

In SSMS, right-click our Test database -> Choose Properties -> Click Options and scroll down to see Recursive Triggers option is enabled or not as shown below. For Test Database, it is set to False as False is the default value for the Recursive Triggers option. To turn on the Recursive Triggers option for a specific database, just click the dropdown value, change it to True and click OK.

Via T-SQL, we can verify the Recursive Trigger option of the Test database by checking the is_recursive_triggers_on column from sys.databases DMV as shown below.

select name, is_recursive_triggers_on
from sys.databases
where name = 'test'

To change the Recursive triggers option for a database (Test in my example), we can execute the below script.

ALTER DATABASE [Test] SET RECURSIVE_TRIGGERS ON WITH NO_WAIT
GO

To disable it back to the false status (default status) for a database (Test in my example), execute the below script.

ALTER DATABASE [Test] SET RECURSIVE_TRIGGERS OFF WITH NO_WAIT
GO

Nested Triggers

Recursive Triggers are a classic example of Nested Triggers but there can be few other cases resulting in Nesting of multiple triggers. SQL Server allows nesting of triggers down to a maximum of 32 levels and any trigger exceeding that nesting level will be canceled by SQL Server. SQL Server has an instance-wide configuration to disable the Nested triggers option. Please be noted that Nesting of SQL Server triggers using CLR code or managed code doesn’t come under the 32 levels limit as it is outside the scope of the SQL Server. By default, the nested triggers option will be enabled across all SQL Server instances and we can disable it as required.

We can verify whether the nested triggers option is enabled at the instance level in SSMS by following the steps below:

Right-click Server -> Choose Properties -> Click Advanced

To disable or turn off the nested triggers option, click the dropdown and change it to False, and click OK.

Via T-SQL, we can verify whether the Nested triggers option is enabled by checking the value_in_use column in sys.configurations DMV for nested triggers configuration name.

To disable this option, we need to use the sp_configure the system stored procedure as shown below:

EXEC sp_configure 'nested triggers', 0;  
GO  
RECONFIGURE;  
GO  

Within any DML or DDL triggers, to find the current level of nesting SQL Server has a built-in function named TRIGGER_NESTLEVEL to return the number of triggers executed for the current statement that fired the trigger including itself. The syntax of the TRIGGER_NESTLEVEL function would be:

SELECT TRIGGER_NESTLEVEL ( object_id, <trigger_type> , <trigger_event_category> )

Where object_id is the object id of the trigger, trigger_type will be AFTER for AFTER trigger and IOT for INSTEAD OF trigger and trigger_event_category will be either DML or DDL.

For example, if we need to allow only nesting level till 10 and raise error after 10 levels, then we can do it on test trigger like here:

IF ((SELECT TRIGGER_NESTLEVEL(OBJECT_ID('test_trigger'), 'AFTER’, 'DML’)) > 10)  
   RAISERROR ('Trigger test_trigger nested more than 10 levels.',16, -1)   

ENCRYPTION

To encrypt the trigger logic or definition, WITH ENCRYPTION option can be used in trigger definition similar to all other SQL Server objects.

EXECUTE AS Clause

To execute the trigger using a specific security context, the EXECUTE AS clause can be used in the trigger definition.

NOT FOR REPLICATION

To identify that the DML trigger shouldn’t be invoked while executed via replication changes, the NOT FOR REPLICATION property will be set for all objects in the Subscriber database.

Conclusion

Thanks for going through the power-packed article on DDL Triggers and Logon Triggers where we have understood the purpose of DDL and Logon triggers, how to create or drop, disable, or enable these triggers along with how to use the EVENTDATA() function for tracking DDL or Logon activities. In addition to that, we have learned how to set the Execution order of multiple SQL DML or DDL triggers along with Recursive and Nested triggers in detail and how to handle Recursive or Nested triggers carefully.

Tags: ddl, sql server, trigger Last modified: October 18, 2021