Table of contents
1.
Introduction
2.
SQL triggers 
3.
Syntax 
4.
Types of SQL Server Triggers
5.
DML Triggers
5.1.
Example 
6.
DDL Triggers
6.1.
AFTER Triggers
6.2.
INSTEAD OF Triggers
7.
Logon Triggers
8.
Display Triggers in SQL
9.
Drop Triggers in SQL
10.
Advantages of Triggers
11.
Disadvantages of Triggers
12.
Frequently Asked Questions
12.1.
What is a SQL trigger?
12.2.
What events can trigger a SQL trigger?
12.3.
What are the benefits of using triggers in SQL?
12.4.
Can triggers in SQL cause performance issues?
13.
Conclusion
Last Updated: Mar 27, 2024
Medium

SQL Triggers

Author Nilesh Kumar
0 upvote
Career growth poll
Do you think IIT Guwahati certified course can help you in your career?

Introduction

Hey, Ninjas! Have you ever used triggers in your database design? Triggers in SQL are a powerful feature of SQL that allows you to automate actions in response to certain events or changes in your database. 

SQL Triggers

In this blog, we will explore what triggers are, how they work, and the benefits and drawbacks of using them in your SQL database. 

Whether you're a beginner or an experienced SQL developer, this blog will provide a good understanding of SQL triggers and their use cases. 

So without further ado, let's start!

SQL triggers 

SQL triggers are a type of database object that allows you to automatically execute a set of actions in response to certain events or changes made to your database. Triggers can be used to implement business rules, provide data integrity, or automate complicated processes.

There are two main types of triggers in SQL:

  1. BEFORE triggers 
    These triggers are executed before a specific action is performed on a table, such as an INSERT, UPDATE, or DELETE operation.
  2. AFTER triggers  
    These triggers are executed after a specific action is performed on a table.
     

Triggers can be written in SQL and are attached to a specific table or view. When the specified event occurs, the trigger code is executed automatically.

While triggers can provide many benefits, such as automating tasks and enforcing data integrity, they can also have some disadvantages. 

For example, triggers can add complexity to your database design and impact database performance. It is important to weigh the pros and cons of using triggers before implementing them in your database design.

Syntax 

The following syntax is used to create triggers in SQL by using the CREATE TRIGGER statement.

CREATE TRIGGER trigger_name
BEFORE/AFTER event
ON table_name
FOR EACH ROW
BEGIN
   -- Trigger code goes here
END;

 

  • trigger_name is the name you give to your trigger.
     
  • BEFORE/AFTER specifies whether the trigger should fire before or after the specified event occurs.
     
  • event is the database operation that triggers the trigger, such as INSERT, UPDATE, or DELETE.
     
  • table_name is the name of the table or view that the trigger is associated with.
     
  • FOR EACH ROW specifies that the trigger should be executed once for each row affected by the event.
     
  • BEGIN and END enclose the trigger code, which can be any valid SQL code.
     

Also read, Natural Join in SQL

Types of SQL Server Triggers

There are three types of SQL Server triggers:

  • DML Triggers
  • DDL Triggers
  • Logon triggers

We will discuss each of them in detail in the upcoming sections.

DML Triggers

DML (Data manipulation language ) triggers fire automatically in response to DML statements such as INSERT, UPDATE, and DELETE operations.

Example 

Here is an example of an SQL trigger that updates a table when a new record is inserted into another table:

Suppose we have two tables: "orders" and "order_details". The "orders" table contains the order information, and the "order_details" table contains the details of each order.

We want to create a trigger that updates the "orders" table whenever a new record is inserted into the "order_details" table. Specifically, we want to update the "total_price" column of the "orders" table to reflect the new total price of the order.

Here is the SQL code for the trigger:

CREATE TRIGGER update_order_total
AFTER INSERT ON order_details
FOR EACH ROW
BEGIN
    UPDATE orders
    SET total_price = (SELECT SUM(price * quantity) FROM order_details WHERE order_id = NEW.order_id)
    WHERE id = NEW.order_id;
END;

 

In this example, the trigger is named "update_order_total". It is set to execute after an insert operation on the "order_details" table for each row that is inserted.

The trigger then performs an update operation on the "orders" table. It sets the "total_price" column to the sum of the product of the price and quantity columns in the "order_details" table for the corresponding order ID.

The "NEW" keyword in the trigger refers to the newly inserted row in the "order_details" table.

When a new record is inserted into the "order_details" table, the trigger is executed automatically, and the "total_price" column in the corresponding row of the "orders" table is updated to reflect the new total price. 

Here is an example output of the trigger:

INSERT INTO order_details (order_id, product_id, price, quantity) VALUES (1, 2, 10.99, 3);

 

After the above insert statement, the trigger is executed automatically and updates the "total_price" column in the "orders" table.

SELECT * FROM orders WHERE id = 1;

 

Output

id customer_id  order_date  total_price
1 1001 2023-03-11  32.97

 

DDL Triggers

DDL (Data Definition Language) triggers fire automatically in response to DDL statements such as CREATE, ALTER, and DROP operations.

Both types of triggers can be further classified into two categories based on the timing of their execution:

 

  1. AFTER Triggers
    These triggers execute after the DML or DDL operation is completed.
     
  2. INSTEAD OF Triggers
    These triggers execute instead of the DML operation and can be used to perform custom logic before the operation is completed.
     

AFTER Triggers

AFTER triggers in DDL trigger are executed after the DDL event has been completed successfully. 

Here is an example of creating an AFTER trigger in DDL trigger that will be triggered after a table is created:

//Create a table for demonstration
CREATE TABLE Employees (
   EmpID INT PRIMARY KEY,
   FirstName VARCHAR(50),
   LastName VARCHAR(50),
   Email VARCHAR(50),
   Salary DECIMAL(10,2)
);
GO

//Create an AFTER trigger in DDL trigger for table creation
CREATE TRIGGER trgAfterCreateTable
ON DATABASE
AFTER CREATE_TABLE
AS
BEGIN
   SET NOCOUNT ON;

   DECLARE @TableName AS NVARCHAR(128);
   SET @TableName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)');

   IF @TableName = 'Employees'
   BEGIN
      PRINT 'Table created successfully. Adding default records...';
      INSERT INTO Employees (EmpID, FirstName, LastName, Email, Salary)
      VALUES (1, 'John', 'Doe', 'john.doe@example.com', 50000.00),
             (2, 'Jane', 'Smith', 'jane.smith@example.com', 60000.00);
   END
END
GO

 

In this code, we first create a table called "Employees". 

We then create an AFTER trigger in DDL trigger that will be triggered after a table is created in the database. 

The trigger is created on the database level and specified to be executed after the CREATE_TABLE event. 

The trigger code retrieves the name of the object that triggered the event using the EVENTDATA() function and checks if the object name is "Employees". 

If the object name is "Employees", the trigger prints a message to the console and inserts two default records into the "Employees" table.

To test the trigger, we can create a new table:

CREATE TABLE Customers (
   CustomerID INT PRIMARY KEY,
   FirstName VARCHAR(50),
   LastName VARCHAR(50),
   Email VARCHAR(50)
);

 

After creating the table, the trigger will be triggered, and the following output will be printed to the console:

If we query the "Employees" table, we can see that the two default records have been added:

SELECT * FROM Employees;

 

Output

EmpID  FirstName  LastName  Email  Salary
1 John  Doe  john.doe@example.com   50000.00
2 Jane  Smith  jane.smith@example.com     60000.00

 

INSTEAD OF Triggers

INSTEAD OF triggers in DDL trigger are used to replace the standard behavior of a DDL event with a custom behavior defined by the trigger. When an INSTEAD OF trigger is created for a DDL event, the standard behavior of the DDL event is not executed, and the trigger code is executed instead.

Here is an example of creating an INSTEAD OF trigger in DDL trigger that will be triggered when a table is dropped:

//Create a table for demonstration
CREATE TABLE Employees (
   EmpID INT PRIMARY KEY,
   FirstName VARCHAR(50),
   LastName VARCHAR(50),
   Email VARCHAR(50),
   Salary DECIMAL(10,2)
);
GO

//Create an INSTEAD OF trigger in DDL trigger for table dropping
CREATE TRIGGER trgInsteadOfDropTable
ON DATABASE
INSTEAD OF DROP_TABLE
AS
BEGIN
   SET NOCOUNT ON;

   DECLARE @TableName AS NVARCHAR(128);
   SET @TableName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)');

   IF @TableName = 'Employees'
   BEGIN
      PRINT 'Table cannot be dropped.';
   END
   ELSE
   BEGIN
      DROP TABLE @TableName;
      PRINT 'Table dropped successfully.';
   END
END
GO

 

In this code, we first create a table called "Employees". We then create an INSTEAD OF trigger in DDL trigger that will be triggered when a table is dropped in the database. The trigger is created on the database level and specified to be executed instead of the DROP_TABLE event.

The trigger code retrieves the name of the object that triggered the event using the EVENTDATA() function and checks if the object name is "Employees". 

If the object name is "Employees", the trigger prints a message to the console saying that the table cannot be dropped. 

If the object name is not "Employees", the trigger drops the table and prints a message to the console saying that the table was dropped successfully. 

To test the trigger, we can try to drop the "Employees" table:

DROP TABLE Employees;

 

After executing the DROP TABLE statement, the trigger will be triggered, and the following output will be printed to the console:

Table cannot be dropped.

 

The "Employees" table will still exist in the database.

If we try to drop a different table, such as the "Customers" table:

DROP TABLE Customers;

 

After executing the DROP TABLE statement, the trigger will be triggered, and the following output will be printed to the console:

Output

Table dropped successfully.

Logon Triggers

A logon trigger is a special type of trigger in SQL Server that is fired in response to a LOGON event for a given user or login. 

A LOGON event occurs when a user or login connects to an instance of SQL Server. 

A logon trigger is defined at the server level and can be used to perform a variety of tasks, such as

  • Auditing logins 
    You can use a logon trigger to audit login activity, such as capturing information about who is logging in, when they are logging in, and from where they are logging in.
     
  • Enforcing security policies
    You can use a logon trigger to enforce security policies, such as restricting access to specific IP addresses or requiring users to use strong passwords.
     
  • Customizing user settings
    You can use a logon trigger to customize user settings, such as setting default database or language settings based on the user's role or department.

Display Triggers in SQL

To display the triggers defined on a table in SQL, you can use the SHOW TRIGGERS statement. 

Here's the syntax:

SHOW TRIGGERS FROM database_name LIKE 'table_name';

Replace database_name with the name of the database where the table is located, and table_name with the name of the table for which you want to display the triggers

This statement will return a result set that contains the following columns:

  1. Trigger name
  2. Event (INSERT, UPDATE, DELETE, or a combination of these)
  3. Table name
  4. Timing (BEFORE or AFTER)
  5. Status (ENABLED or DISABLED)
  6. Definer (the user who defined the trigger)
  7. Character set client and connection (the character set used by the client and connection)

 

For example, to display the triggers defined on the orders table in the sales database, you can execute the following command:

SHOW TRIGGERS FROM sales LIKE 'orders';

 

This will return a result set similar to the following:

Trigger name  Event Table name   Timing  Status  Definer 
tr_orders_ai  INSERT  orders  AFTER  ENABLED  user@localhost
tr_orders_au  UPDATE  orders  AFTER  ENABLED  user@localhost
tr_orders_ad  DELETE  orders  AFTER  ENABLED  user@localhost

 

In this example, there are three triggers defined on the orders table in the sales database, all of which are enabled. The Trigger name column displays the name of each trigger, and the other columns provide additional details about each trigger.

Drop Triggers in SQL

To drop a trigger in SQL, you can use the DROP TRIGGER statement. Here's the syntax:

DROP TRIGGER trigger_name;

 

Here, trigger_name is the name of the trigger you want to drop.

To demonstrate the usage of DROP TRIGGER commands, let's consider an example. Suppose we have a table named students with a trigger named trg_students.

Before running this statement, we can use the SHOW TRIGGERS command to verify the trigger. The SQL statement for displaying triggers of the students table would be:

SHOW TRIGGERS FROM mydatabase LIKE 'students';

 

Output

display triggers Output

 

To drop the trg_students trigger from the students table, we can use the following SQL statement:

DROP TRIGGER IF EXISTS trg_students;

 

After running this statement, we can use the SHOW TRIGGERS command to verify that the trigger has been dropped. The SQL statement for displaying triggers of the students table would be:

SHOW TRIGGERS FROM mydatabase LIKE 'students';

 

Output

display triggers Output

 

If the trg_students trigger was present in the table before, it should no longer appear in the output of this command after the DROP TRIGGER statement is executed.

Advantages of Triggers

Triggers in SQL have several advantages that make them useful for managing database operations. 

Some of the advantages of triggers include:

 

  1. Automated actions 
    Triggers automate actions in the database, such as inserting or updating data in one table based on changes made to another table. This can save time and reduce the risk of human error.
     
  2. Data integrity 
    Triggers can be used to enforce data integrity constraints, such as ensuring that certain fields are not left empty or that data in one table corresponds to data in another table. This helps to ensure the accuracy and consistency of the data in the database.
     
  3. Audit trails 
    Triggers can be used to create audit trails that track changes made to the database, such as who made the change, when it was made, and what was changed. This can be useful for compliance purposes and for detecting and investigating data breaches or unauthorized changes.
     
  4. Performance optimization
    Triggers can be used to optimize database performance by reducing the need for manual intervention in data processing and by automating routine tasks, such as archiving or deleting old data.
     
  5. Business logic implementation 
    Triggers can be used to implement business logic in the database, such as calculating discounts based on the number of items purchased or determining the availability of inventory. This can make it easier to manage complex business rules and calculations within the database.
     

Disadvantages of Triggers

Some of the main disadvantages of triggers include:

  1. Complexity
    Triggers can be complex to design and maintain, especially for large databases with many tables and triggers. This can make it difficult to debug errors or modify triggers as needed.
     
  2. Performance impact
    Triggers can have a negative impact on database performance, especially if they are poorly designed or executed frequently. This is because triggers must be executed whenever a certain event occurs, which can slow down database operations.
     
  3. Unintended consequences
    Triggers can have unintended consequences if they are not designed and implemented properly. For example, a trigger that updates a table based on changes made to another table could inadvertently cause data to be overwritten or deleted.
     
  4. Debugging challenges
    Debugging triggers can be challenging, especially if they are nested or have complex logic. This can make it difficult to identify and fix errors in the trigger code.
     
  5. Maintenance overhead
    Triggers can add to the maintenance overhead of a database, as they must be monitored and maintained to ensure they are functioning properly and not causing any issues.
     

Must Read SQL Clauses

Frequently Asked Questions

What is a SQL trigger?

A SQL trigger is a special type of stored procedure that automatically executes in response to certain events or changes in a database.

What events can trigger a SQL trigger?

A SQL trigger can be triggered by events such as INSERT, UPDATE, DELETE, and other database-related actions.

What are the benefits of using triggers in SQL?

SQL triggers can help automate database operations, enforce data integrity constraints, and create audit trails, among other benefits.

Can triggers in SQL cause performance issues?

Yes, poorly designed or executed triggers in SQL can cause performance issues in a database, so it's important to test and optimize triggers to minimize their impact on performance.

Conclusion

Hey Ninja! We hope you enjoyed reading this article. We discussed the triggers in SQL along with syntax, advantages, and disadvantages.

If you want to learn about SQL and DBMS you can also read the below-mentioned articles:

If you liked our article, do upvote our article and help other ninjas grow.  You can refer to our Guided Path on Coding Ninjas Studio to upskill yourself in Data Structures and AlgorithmsCompetitive ProgrammingSystem Design, and many more!

Happy Coding! 

Live masterclass