Home

 › 

Articles

 › 

Understanding Triggers in SQL, with Examples

SQL language programming data

Understanding Triggers in SQL, with Examples

Key Points

  • Triggers in SQL are statements that automatically execute commands when certain data scenarios are met, with two types: DML and DDL triggers.
  • DML triggers execute when data is inserted, removed, or updated within a table, while DDL triggers execute when the structure of a table changes.
  • CLR triggers, exclusive to Microsoft .NET Framework, can be used with functions, stored procedures, aggregate functions, and types.

SQL, or Structured Query Language, is the most popular programming language to use for managing data in relational database management systems. RDBMSs are the most common database types in use right now, and SQL is an excellent language for managing data in a structured way. Triggers are a crucial aspect of SQL that allows you to manage the database’s procedures and automatically execute commands when certain scenarios are met. Here’s what you need to know about triggers in SQL.

What Is a Trigger in SQL?

Triggers are a type of statement used in SQL that allows the user to save commands to execute automatically when certain scenarios regarding the data in the database are met. Two types of triggers can be used in SQL: DML and DDL triggers.

What Is a DML Trigger?

Data Manipulation Language, or DML, triggers execute when data is inserted, removed, or updated within the referenced table. DML triggers are used to enforce data integrity and business rules within the database. DML triggers can also be used to query other tables and include complex Transact-SQL statements. The trigger, and any statements it executes, are treated as one transaction within the program and can be rolled back within the trigger. If a DML trigger runs into a fatal error of any kind, it will automatically roll the transaction back.

What Is a DDL Trigger?

Data definition language, or DDL, triggers execute when the structure of the queried table changes. Structure changes, such as adding or removing rows or columns from the table, can be used with DDL triggers to log changes to the database structure, prevent changes to the database structure, or take additional actions related to changes in the database structure.

Programmer's text editor showing SQL code on a dark background.
Adding a table to a database using DDL code.

©samjoule/Shutterstock.com

How to Execute a DML Trigger in SQL

DML triggers are executed in code using the following pseudocode:

CREATE TRIGGER [trigger_name] 
[BEFORE | AFTER]  
{INSERT | UPDATE | DELETE}  
ON [table_name]  
[FOR EACH ROW]  
[trigger_body] 

You start by using the “create trigger” statement, which defines the code after it as the parameters for a trigger. You’ll need to name your trigger on this line. Then you need to define whether the trigger happens before or after the trigger condition. Next, you’ll define a trigger condition, insert, update, or delete. After that, you’ll need to tell the computer what table to trigger. Now you’ll need to use the statement “for each row.” Then, you’ll finally define the trigger parameters in the trigger body. Altogether, that looks something like this:

CREATE TRIGGER triggerA 
AFTER INSERT 
ON 
tableA 
FOR EACH ROW 
SET tableA.total = tableA.cost + tableA.supplies + tableA.labor, tableA.total = tableA.total;

How to Execute a DDL Trigger in SQL

The pseudocode for creating a DDL trigger in SQL is as follows:

CREATE TRIGGER trigger_name
ON table_name
FOR {INSERT | UPDATE | DELETE}
[FOR EACH ROW]
AS
BEGIN
    -- Trigger body
END

For DDL events, we have an example with two different triggers to provide practical examples of different events that can trigger a DDL trigger. The first event in the following code logs all of the events to a log table. The second prevents the table from dropping.

CREATE TRIGGER log_ddl_events
ON DATABASE
FOR DDL_EVENT_GROUP = ALL
AS
BEGIN
    -- Log the event details to a table.
    INSERT INTO ddl_event_log (event_name, event_time, user_name)
    VALUES (EVENTDATA().value('(/EVENT_INSTANCE/EVENT_NAME)[1]', 'nvarchar(50)'),
           GETDATE(),
           SUSER_SNAME());
END
GO

CREATE TRIGGER prevent_table_drop
ON DATABASE
FOR DROP_TABLE
AS
BEGIN
    -- Prevent the table from being dropped.
    RAISERROR('Table cannot be dropped.', 16, 1);
END
GO

Just like with DML events, DDL events start with the create trigger statement. The first event then names the trigger log_ddl_events. It then stipulates that the DDL trigger will trigger on the table called DATABASE. The next line stipulates that the trigger will trigger on all DDL events. As begin indicates the beginning of a DDL trigger body. The insert statement inserts a new row into the ddl_event_log table, which has three columns: event_name, event_time, and user_name.

The values clause takes a list of expressions to use as its argument. When using the values clause, each expression will be evaluated and subsequently inserted into the appropriate column in the table. In this case, values takes the expressions EVENTDATA().value(‘(/EVENT_INSTANCE/EVENT_NAME)[1]’, ‘nvarchar(50)’), GETDATE(), SUSER_SNAME(). The EVENTDATA statement returns an XML document containing information about the event. Then the value function is used to extract the name of the event from the XML document. GETDATE, well, it returns the current date and time. Lastly, SUSER_SNAME returns the name of the user who instigated the DDL trigger.

The second DDL trigger in this code block is much simpler. It starts much the same as the first trigger. However, it triggers only on drop_table. It then uses raiserror to, well, raise an error and tell you that you can’t do that. 

Using CLR Triggers in SQL

If your company’s database uses the Microsoft .NET Framework, there is a secret third, Microsoft-only type of trigger called a Common Language Runtime or CLR trigger. CLR triggers are a subset of DDL and DML triggers, but CLR can also be used with functions, stored procedures, aggregate functions, and types.

To use a CLR trigger, you must first define the trigger as a class in a .NET-supported language and then compile the class to build an assembly using an appropriate language compiler. Then you register the assembly with the SQL Server using the create assembly statement. Finally, you’ll create a trigger that references the registered assembly.

The syntax for the create assembly statement is as follows:

CREATE ASSEMBLY assembly_name  
[ AUTHORIZATION owner_name ]  
FROM { <client_assembly_specifier> | <assembly_bits> [ ,...n ] }  
[ WITH PERMISSION_SET = { SAFE | EXTERNAL_ACCESS | UNSAFE } ]  
[ ; ]  
<client_assembly_specifier> :: =  
        '[computer_name]share_name[path]manifest_file_name'  
  | '[local_path]manifest_file_name'  
  
<assembly_bits> :: =  
{ varbinary_literal | varbinary_expression }  

The following code is a trigger written in C# to be used as an assembly with an SQL Server.

using System;
using Microsoft.SqlServer.Server;
public class MyTrigger : Trigger
{
    public override void Trigger_RowUpdated(Object source, Object[] args)
    {
        // Get the updated row.
        SqlDataRecord updatedRow = args[0] as SqlDataRecord;
        // Check if the `Name` column has been updated.
        if (updatedRow["Name"] != null)
        {
            // Update the `Name` column in the audit table.
            SqlConnection connection = new SqlConnection("Data Source=localhost;Initial Catalog=MyDatabase;Integrated Security=True");
            SqlCommand command = new SqlCommand("UPDATE AuditTable SET Name = @NewName WHERE ID = @ID", connection);
            command.Parameters.AddWithValue("@NewName", updatedRow["Name"]);
            command.Parameters.AddWithValue("@ID", updatedRow["ID"]);
            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
        }
    }
}

Finally, we’ll create a trigger that references the assembly.

CREATE TRIGGER MyTrigger
ON MyTable
INSTEAD OF UPDATE
AS
BEGIN
    // Use the MyTrigger class to update the AuditTable table.
END;

The first code block uses C# to define a trigger that checks if the Name column of the table has been updated. If the column is updated, it updates the column in the AuditTable. The second block references the trigger using SQL.

Final Thoughts

Triggers are a crucial part of SQL that can be used to automate parts of your database, protect data integrity, and create changelogs. Now that you know how to use triggers in your SQL scripts, you can take control of your databases in new ways. If you learned something new from our article, please leave us a comment and let us know! We love to hear from our readers!

Summary Table

TypeDescription
DML TriggerExecutes when data is inserted, removed, or updated within the referenced table. Used to enforce data integrity and business rules.
DDL TriggerExecutes when the structure of the queried table changes. Used to log changes to the database structure, prevent changes, or take additional actions.
CLR TriggerMicrosoft-only type of trigger used with the .NET Framework. Can be used with functions, stored procedures, aggregate functions, and types.

Frequently Asked Questions

What is SQL?

SQL, or Structured Query Language, is a programming language designed to manipulate data in relational database management systems.

What are triggers in SQL?

Triggers in SQL execute functions or statements when certain scenarios are met within the commands entered to the database.

What types of triggers are available in SQL?

Data Manipulation Language (DML) and Data Definition Language (DDL) are the two types of triggers that are standard for SQL. Databases using Microsoft’s .NET Framework can also use Common Language Runtime (CLR) triggers.

What is a DML trigger in SQL?

DML triggers in SQL trigger when data is inserted, removed, or altered within the database.

What is a DDL trigger in SQL?

DDL triggers in SQL trigger when the structure of the database is changed.

To top