Understanding triggers in SQL Server

In this page

  • What is SQL triggers
  • How to create SQL Server triggers
  • How triggers work
  • Operations in SQL triggers
  • Types of triggers
  • Benefits of using SQL triggers
  • Auditing SQL servers with triggers

SQL triggers

Auditing your SQL Server instance can be done in many ways. One of these ways is using auditing triggers. Triggers hold a pivotal role within SQL Server databases, serving as indispensable tools for enforcing business rules, upholding data integrity, and streamlining repetitive actions.

What are triggers in SQL?

A trigger is a stored database procedure that gets automatically invoked when a specific event, such as an insert, update, or delete operation, occurs in your database.

How to create triggers in SQL Server

To create a trigger in SQL Server, use the CREATE TRIGGER statement comprised of these elements:

  • The trigger name
  • The associated table
  • The triggering event (INSERT, UPDATE, DELETE, etc.)
  • SQL statements to execute when the trigger fires

Syntax

CREATE TRIGGER TriggerName
ON TableName
AFTER INSERT, UPDATE
AS
BEGIN
---SQL statements here---
END;

How triggers work

  • Event activation: Triggers are bound to a specific table or view and listen for specified events (INSERT, UPDATE, DELETE, CREATE, etc.). When one of these events occurs, the trigger is activated.
  • Code execution: Upon activation, the trigger executes a predefined set of SQL statements. These can include complex logic involving conditional statements, loops, and calls to other procedures.
  • Execution scope: Triggers can operate on both the row and statement levels.
    • Row-level triggers: A row-level trigger executes once for every row impacted by the triggering event, ensuring that the trigger's actions are applied at the granularity of each affected row.
    • Statement-level triggers: A statement-level trigger executes once for the triggering statement, regardless of the number of rows it affects.

Operations in triggers

Drop triggers

You can use the DROP command to remove a trigger from the database.

Syntax

Drop TRIGGER TriggerName;

Display triggers

You can use the SHOW command to display triggers from the database.

Syntax

SHOW TRIGGERS
In database_ name;

Insert triggers

An insert trigger in SQL Server is a special type of stored procedure that automatically executes in response to an INSERT event in the database.

Syntax

CREATE TRIGGER [schema_name.]trigger_name
ON table_name
AFTER INSERT
AS
BEGIN
-- Insert your trigger logic here.
-- Specify the SQL statements to be executed upon trigger activation.
END

Here, schema_name specifies the schema of the trigger. If not provided, the default schema (usually dbo) is used. Next, trigger_name is the name of the trigger, table_name is the name of the table on which the trigger operates, and AFTER INSERT specifies that the trigger should be invoked after the insertion of rows into the table. SQL Server does not support BEFORE INSERT triggers like some other database systems do. Next, AS BEGIN...END encloses the SQL statements that define what the trigger does. This block can contain complex logic, including calling other procedures, inserting data into other tables, and more.

How many types of triggers are present in SQL Server?

There are four types of triggers:

  • Data Definition Language (DDL) triggers
  • Data Manipulation Language (DML) triggers
  • Common Language Runtime (CLR) triggers
  • Logon triggers

1. DDL triggers

DDL triggers are fired in response to DDL events. The CREATE, ALTER, DROP, GRANT, DENY, and REVOKE Transact-SQL statements can invoke DDL triggers. The invoked DDL triggers can perform one of the following functions:

  • They execute a set of procedures when there is a modification in your database schema.
  • They record the changes carried out using these statements in another table.

2. DML triggers

DML triggers are the procedures executed when DML statements such as INSERT, UPDATE, and DELETE are executed. These triggers help with auditing by automatically creating tables called INSERTED and DELETED to record the values before and after the modification of your database.

DML triggers are classified into two types based on their execution timings:

  • AFTER triggers: They execute after the associated DML operation completes successfully. These are used for actions that need to occur after data modifications.
  • INSTEAD OF triggers: They execute in place of the intended DML operation. They are ideal for modifying the behavior of operations, such as validating or transforming data before it is written to the database.

3. CLR triggers

CLR triggers allow you to create objects in your SQL Server instance that have a reference object outside your SQL Server environment. This is a special type of trigger that can be inserted directly into .NET language codes and is available in SQL Server 2008 and above.

4. Logon triggers

Logon triggers are fired when an SQL Server LOGON event occurs. Logon triggers can be used to track logon activities, regulate logon attempts, and restrict logons to critical SQL Server instances.

Benefits of using SQL triggers

  • Triggers in SQL Server automate tedious tasks like logging changes and updating related data, saving you valuable time and effort.
  • Triggers enforce rules and constraints and ensure your database remains accurate and reliable, sparing you from the headache of data inconsistencies.
  • With triggers, you can seamlessly integrate business rules directly into the database layer, ensuring consistent behavior throughout your system.
  • Triggers can enforce access controls and auditing, safeguarding your database from unauthorized access and malicious activities.

Best practices for using triggers in SQL Server

Triggers are a powerful feature in SQL Server that can automate tasks, enforce business rules, and maintain data integrity. However, they should be used judiciously due to their potential impact on database performance and complexity. Below are some key guidelines for leveraging triggers efficiently:

  • Minimize the logic in triggers: Keep the logic within triggers as simple and efficient as possible.
  • Document triggers: Maintain thorough documentation of all triggers, including their purposes and effects, to aid in maintenance and debugging.
  • Monitor performance: Regularly review the performance impact of triggers and optimize or refactor them as necessary.

Are you effectively auditing your SQL Server environment?

Although auditing SQL servers with triggers is somewhat efficient, this method has significant limitations. Triggers can only be created for individual tables; thus, if you have 100 tables that need monitoring, an admin would need to create 100 triggers. This represents a major operational bottleneck. The time and effort required to create triggers for auditing are considerable, meaning auditing cannot rely solely on triggers. Admins who manually audit databases should carefully consider choosing SQL Server. Moreover, the information recorded by SQL Server triggers does not provide insights into incidents or attacks that may be occurring in your SQL Server environment.

Besides using SQL triggers, you can also employ other methods such as SQL Trace and SQL Server Profiler, database audit specifications, server audit specifications, and command-and-control auditing. Alternatively, you can use a log management solution like EventLog Analyzer to effectively audit your SQL Server logs.

EventLog Analyzer is an effective log management solution that collects all the SQL Server logs; analyzes them using its powerful correlation engine; and provides intuitive reports for all DDL, DML, and security-related events taking place in your database. It can provide reports for critical events like privilege abuse, sensitive data leakage, account lockouts, storage media exposure, SQL injection, and DoS attacks, to name a few. You can set up alerts for any of these events to get notified in real time via email and SMS.

Check out the other reports EventLog Analyzer provides for SQL Server auditing.