Understanding Triggers in SQL, with Examples

SQL, or Structured Query Language, is the most popular programming language to use for managing data in relational database management systems.RDBMSsare 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 savecommandsto 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.

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 thecreate triggerstatement, 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 happensbeforeorafterthe trigger condition. Next, you ll define a trigger condition,insert,update, ordelete. After that, you ll need to tell the computer what table to trigger. Now you ll need to use the statementfor each row.Then, you ll finally define the trigger parameters in thetrigger 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 thecreate triggerstatement. 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 onallDDL events.As beginindicates the beginning of a DDL trigger body. Theinsertstatement inserts a new row into the ddl_event_log table, which has three columns: event_name, event_time, and user_name.

Thevaluesclause 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 thevaluefunction is used to extract the name of the event from the XML document.GETDATE, well, it returns the current date and time. Lastly,SUSER_SNAMEreturns 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 ondrop_table. It then usesraiserrorto, 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 thecreate assemblystatement. Finally, you ll create a trigger that references the registered assembly.

The syntax for thecreate assemblystatement 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!