Adventures in Machine Learning

Automating SQL Server with Triggers: DDL and Logon Trigger Best Practices

Working with Triggers in SQL Server

Have you ever needed to automatically perform an action on a table in SQL Server as a result of an INSERT, UPDATE, or DELETE statement? This is precisely what triggers are designed to do.

In this article, we will take an in-depth look at triggers, their types, practical applications, and how to create and manage triggers in SQL Server.

Creating a Trigger

Triggers in SQL Server are a type of special stored procedures that are triggered automatically when an appropriate event occurs, like an INSERT, UPDATE, or DELETE statement. To create a trigger, you need to use the CREATE TRIGGER statement.

Let’s take a look at the syntax for creating a trigger that fires after an INSERT statement:

CREATE TRIGGER trigger_name
ON table_name
AFTER INSERT
AS
BEGIN
  -- Trigger code here
END

The keyword AFTER specifies when the trigger fires. In this case, it fires after an INSERT statement.

You can replace AFTER with INSTEAD OF, which specifies that the trigger fire instead of the triggering statement, effectively altering the command. This is useful when you need to manipulate the data of the command before executing it.

Creating an INSTEAD OF Trigger

Let’s say you want to prevent specific users from making changes to certain tables in your database. One way to achieve this is by creating an INSTEAD OF trigger.

This type of trigger fires instead of the triggering action and allows you to modify the action before executing it. For instance, let’s create an INSTEAD OF trigger that prevents updates on the employees table for employees whose salaries are above a specific amount:

CREATE TRIGGER update_employee_salaries_trigger
ON employees
INSTEAD OF UPDATE
AS
BEGIN
  IF (SELECT user_name() = 'unauthorized_user')
  BEGIN
    RAISERROR('You are not authorized to update employee salaries', 16, 1)
    ROLLBACK TRANSACTION
  END
  ELSE
  BEGIN
    -- Original UPDATE statement modified here
    UPDATE employees SET salary = i.salary
    FROM employees e INNER JOIN inserted i ON e.employee_id = i.employee_id
    WHERE i.salary < 1000000
  END
END

In the trigger, we first check if the current user is authorized to update employee salaries. If the user is unauthorized, we raise an error and rollback the transaction.

If the user is authorized, we modify the original UPDATE statement to update only salaries that are below one million dollars.

Creating a DDL Trigger

DDL triggers fire in response to DDL events, such as creating or altering tables, views, or indexes. It is essential to manage these triggers carefully because they can be catastrophic and change the underlying structure of your database.

Let’s create a DDL trigger that fires whenever a table is created in our database, which writes the name of the table to a log file:

CREATE TRIGGER table_created_trigger
ON ALL SERVER
FOR CREATE_TABLE
AS
BEGIN
  -- Write the name of the newly created table to a log file
  DECLARE @logfile NVARCHAR(1000)
  SET @logfile = 'C:DBLogstable_created.log'
  DECLARE @msg NVARCHAR(4000)
  SET @msg = 'Table ' + EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','NVARCHAR(256)') + ' was created'
  EXEC master.dbo.xp_logevent 60000, @msg, informational
END

In this trigger, we specify that it fires for the CREATE_TABLE event on ALL SERVER. It then writes the name of the newly created table to a log file using xp_logevent.

Disabling/Enabling Triggers

At times, you may find it necessary to disable triggers temporarily. For instance, when performing a large data import operation that triggers many insert statements, you may want to disable certain triggers to improve performance.

To disable or enable a trigger, use the DISABLE TRIGGER or ENABLE TRIGGER statements. 

-- Disabling a trigger
DISABLE TRIGGER trigger_name ON table_name;
-- Enabling a trigger
ENABLE TRIGGER trigger_name ON table_name;

Viewing the Definition of a Trigger/List of Triggers

To view the definition of a trigger, use the sp_helptext system stored procedure or query the sys.triggers view:

-- Using sp_helptext
sp_helptext 'trigger_name';
-- Querying the sys.triggers view
SELECT OBJECT_DEFINITION(OBJECT_ID('schema_name.trigger_name'));

To list all the triggers in your database, query the sys.triggers view:

SELECT name, object_name(parent_id), create_date, modify_date
FROM sys.triggers;

DML Triggers

DML triggers fire in response to DML (Data Manipulation Language) events, such as INSERT, UPDATE, or DELETE statements.

Types of Triggers

In SQL Server, there are three types of DML triggers: INSERT, UPDATE, and DELETE.

INSERT Trigger

An INSERT trigger fires whenever a new row is inserted into a table. The inserted row can be accessed using the inserted table.

CREATE TRIGGER insert_employee_trigger
ON employees
FOR INSERT
AS
BEGIN
  -- Trigger code here
END

UPDATE Trigger

An UPDATE trigger fires whenever a row in a table is updated. The before and after images of the row can be accessed using the deleted and inserted tables, respectively.

CREATE TRIGGER update_employee_trigger
ON employees
FOR UPDATE
AS
BEGIN
  -- Trigger code here
END

DELETE Trigger

A DELETE trigger fires whenever a row in a table is deleted. The deleted row can be accessed using the deleted table.

CREATE TRIGGER delete_employee_trigger
ON employees
FOR DELETE
AS
BEGIN
  -- Trigger code here
END

Triggers with Multiple Actions

A single trigger can contain multiple actions. For instance, you can create a trigger that performs multiple actions on two or more tables.

CREATE TRIGGER insert_employee_trigger
ON employees
FOR INSERT
AS
BEGIN
  -- Insert a new row into the roles table
  INSERT INTO roles (role_id, role_name)
  SELECT employee_id, 'Employee' FROM inserted
  -- Trigger code here
END

In this trigger, we insert a new row into the roles table with a new employee’s role_id and role_name set to Employee.

Conclusion

In conclusion, triggers are an essential component of SQL Server that can significantly improve the functionality and maintenance of your database. Understanding how triggers work, the types of triggers available, and how to disable, enable, or view triggers’ definition and list are essential to developing robust, effective systems.

In addition, properly implementing DML triggers helps you audit and maintain data integrity within SQL Server.

DDL Triggers

DDL (Data Definition Language) triggers execute automatically in SQL Server in response to DDL changes made to the database, such as creating, altering, or dropping objects like tables, views, or stored procedures. DDL triggers can be used to enhance the database’s functionality, audit changes, and enforce best practices.

What are DDL Triggers?

DDL triggers are a type of trigger in SQL Server that execute automatically in response to DDL events.

Like all types of triggers, DDL triggers have an associated event, a set of conditions to execute, and a trigger action. DDL events include CREATE_TABLE, ALTER_TABLE, DROP_TABLE, CREATE_DATABASE, ALTER_DATABASE, and many more.

In essence, DDL triggers act as “watchdogs” that monitor changes in a database. Designing

DDL Triggers

To implement a DDL trigger, you must first identify the DDL event that you want to respond to.

You then create a trigger method that will execute if the event occurs. For example, if you want to audit changes made to a table, you can create a trigger that fires after an ALTER statement is executed on the table.

The trigger code would then execute SQL statements to log the changes made to the table.

Examples of DDL Triggers

There are several use cases for DDL triggers.

One common use case is auditing changes made to a database. For instance, creating a DDL trigger that logs all actions done on the tables, views, or stored procedures within the database, complete with the relevant details like time stamp, user ID, and the changed object can be useful for change tracking.

Here’s an example of a DDL trigger that logs the name of the user, the action, and the executed query when someone creates a new table:

CREATE TRIGGER log_ddlCreateTable
ON DATABASE
FOR CREATE_TABLE
AS
BEGIN
  DECLARE @sql NVARCHAR(MAX);
  SET @sql = 'INSERT INTO auditing.dbo.DDL_LOG
              (UserName, Command, Query) 
              VALUES 
              (USER_NAME(), ''CREATE TABLE'', ''CREATE TABLE ['' + EVENTDATA().value(''(/EVENT_INSTANCE/SchemaName) [1]'',''nvarchar(256)'') + ''].['' + EVENTDATA().value(''(/EVENT_INSTANCE/ObjectName)[1]'',''nvarchar(256)'') + ''] ('+ (SELECT STUFF((SELECT ',' +  name + ' ' + system_type_name FROM sys.dm_exec_describe_first_result_set(''select * from '' + EVENTDATA().value(''(/EVENT_INSTANCE/SchemaName) [1]'',''nvarchar(256)'') + ''.'' + EVENTDATA().value(''(/EVENT_INSTANCE/ObjectName) [1]'',''nvarchar(256)''),NULL,NULL) FOR XML PATH ('''')),1,1,'''') ) + ');'')';
  EXEC sp_executesql @sql;
END;

This trigger fires every time a CREATE_TABLE event occurs on the database. It fetches all the relevant information using the EVENTDATA() function and then adds it as a log to the DDL_LOG table in the auditing schema.

Use Cases for DDL Triggers

DDL triggers can be used for a wide range of use cases such as auditing, enforcing data standards, or implementing metadata management. Here is a more in-depth look at other common use cases:

  • Data Definition Language Auditing: As demonstrated above, auditing changes in the data definition language is a crucial application of DDL triggers.
  • Data Standardization: DDL triggers can be used to enforce consistency in the naming conventions of tables, columns, and other objects. For example, ensuring that all tables follow a particular schema convention.
  • Metadata Management: One reason to use DDL triggers is to detect the changes made to the database and update metadata accordingly.
  • Websites and Application Integration: DDL triggers can be used to update metadata in real-time with other applications.

If data is being updated on one application, the DDL trigger can allow the metadata to update at the same time.

Logon Triggers

Logon Triggers are a type of SQL Server trigger that responds to the login event of a user or application accessing a database. The event occurs before the user has access to the database and can be used for security and monitoring purposes.

Creating Logon Triggers

Logon triggers can be created using the CREATE TRIGGER statement, similar to DDL triggers. Here’s an example of a logon trigger that prevents any login attempts from an IP address outside a certain range:

CREATE TRIGGER PreventBadIP
ON ALL SERVER WITH EXECUTE AS 'dbo'
FOR LOGON
AS
BEGIN  
  DECLARE @IP VARCHAR(50) 
  SELECT @IP = client_net_address
  FROM sys.dm_exec_sessions
  WHERE session_id = @@SPID
  IF NOT (@IP LIKE '192.168.%')
  BEGIN
    ROLLBACK;
    SET @Error = CAST(CAST(70000 AS INT) AS VARBINARY(4)) + CAST('You are not allowed to log in from this IP address.' AS VARBINARY(100));
    RAISERROR(@Error, 20, 1) WITH LOG;  
  END
END;

In this logon trigger, we use the sys.dm_exec_sessions dynamic management view to fetch the current user’s IP address, then we use it to decide whether the login should proceed or not. The ROLLBACK; statement cancels the login attempt, while the RAISERROR statement returns an error message.

Use Cases for Logon Triggers

Logon triggers can be used to implement a wide variety of security and monitoring scenarios. Here are a few examples:

  • Enforce Password Policy: Logon triggers can be used to check the strength of passwords and enforce the policy defined by the certain database.
  • Monitor Logins: Create a logon trigger that records each login to a table to track who uses a particular database.
  • Auditing: Logon triggers can be used to audit activities such as login attempts and changes to login information.

Best Practices for Logon Triggers

When designing and developing logon triggers in SQL Server, there are certain best practices that you should follow. Here are a few:

  • Keep it simple: Logon triggers can add overhead when many logins occur.
  • Keep their logic as simple as possible.
  • Keep failures silent: When a logon trigger fails, it denies access to a user.
  • It is essential to ensure that the failure is logged and not shown to the user.
  • Audit Logon Triggers: Ensure to audit the logon triggers in a database.

In Conclusion

DDL and logon triggers in SQL Server provide a convenient method for automating tasks, enforcing security policies, and auditing a database. Well-designed triggers can help improve the efficiency and functionality of a database while ensuring that all necessary security measures are in place.

In summary, the article covers two essential types of SQL Server triggers: DDL triggers and logon triggers. DDL triggers are used to automatically execute tasks that respond to DDL events, such as creating tables or altering objects.

On the other hand, logon triggers help maintain security by ensuring only authorized users can log in and auditing activities, such as login attempts. Their best practices and use cases have also been discussed.

Both trigger types allow for the automation of critical system tasks and can help ensure the smooth operation of an SQL Server platform. As such, developers and database administrators should be aware of triggers’ functionality and implement them where necessary to build more efficient and secure systems.

Popular Posts