--- title: "What are the types of Triggers in the SQL server?" description: "What are the types of Triggers in the SQL server?" author: "Ashutosh Patel" published: 2023-03-22 updated: 2023-03-22 canonical: https://www.mindstick.com/forum/157530/what-are-the-types-of-triggers-in-the-sql-server category: "mssql server" tags: ["mssql server", "sql server", "sql"] reading_time: 3 minutes --- # What are the types of Triggers in the SQL server? What are the types of [Triggers in the SQL](https://www.mindstick.com/interview/33959/why-are-use-triggers-in-the-sql-server-database) [server](https://www.mindstick.com/articles/43769/what-is-serverless-architecture-is-it-worth-switching-over)? ## Replies ### Reply by Krishnapriya Rajeev TRIGGER statements in [SQL](https://www.mindstick.com/articles/13115/types-of-keys-in-sql-or-oracle-database) servers can be classified into 3 main types: 1. Data Definition Language (DDL) Trigger These triggers are fired in response to events caused by DDL statements like CREATE, ALTER, and DROP. These can be created at either the database level or server level, and can also be triggered by system-defined stored procedures that perform operations similar to DDL statements. These triggers are useful: 1. When it's necessary to avoid altering the database schema. 2. When we must examine modifications performed on the database schema. 3. When we must react to a modification made in the database schema. Example: ![Text Description automatically generated](https://www.mindstick.com/mindstickforums/ae6a67d1-bac9-4d12-ac78-30f886cbcc1a/images/e9efb562-9a86-4c4a-b397-aa8ea38ef4c0.png) 2. Data Manipulation Language (DML) Trigger These triggers are fired in response to events caused by DML statements like INSERT, UPDATE, and DELETE. They can also be triggered by system-defined stored procedures that perform operations similar to DML statements. There are two types of DML triggers: 1. AFTER Trigger It is fired when the SQL server action that triggered it is completed. It is usually executed when a table completes an INSERT, UPDATE, or DELETE operation. Example: ![Text Description automatically generated](https://www.mindstick.com/mindstickforums/ae6a67d1-bac9-4d12-ac78-30f886cbcc1a/images/8ebdf782-3af0-41d2-b822-c320a3b80060.png) b. INSTEAD OF Trigger It is fired before the SQL server begins executing the action that triggers it. Hence, the trigger will fire even if the constraint check fails. Example: ![Text Description automatically generated](https://www.mindstick.com/mindstickforums/ae6a67d1-bac9-4d12-ac78-30f886cbcc1a/images/6d848a00-62c0-4cc2-a719-cd6f273e3222.png) 3. LOGON Trigger These triggers are fired in response to LOGON events, which occur during the creation of a user session with an SQL server instance following the authentication process of logging in but before the user session is actually established. This results in the SQL server error log displaying all the messages created by the trigger, including error messages and PRINT statement messages. These triggers do not fire if the authentication fails. ### Reply by Eden Wheeler Hello there! It's great to see your interest in [SQL Server](https://www.mindstick.com/articles/12999/what-is-table-valued-function-in-sql-server) and [triggers](https://www.mindstick.com/articles/337000/what-are-sql-triggers-and-ways-of-using-them-effectively). AS an expert of this field, I can help you understand the types of triggers in SQL Server. In SQL Server, there are two types of triggers: DML (Data Manipulation Language) triggers and DDL (Data Definition Language) triggers. DML triggers fire in response to DML events, such as INSERT, UPDATE, or DELETE statements, while DDL triggers fire in response to DDL events, such as CREATE, ALTER, or DROP statements. Let me share a personal story about triggers. A few years ago, I was working on a project where we needed to track changes made to a database table. We decided to use a DML trigger to capture the changes. We created an AFTER INSERT, UPDATE, and DELETE trigger on the table, and every time a row was inserted, updated, or deleted, the trigger fired and wrote the details of the change to an audit table. This allowed us to easily track changes made to the table and investigate any issues that arose. In conclusion, triggers are a powerful feature of SQL Server that can help you automate tasks and track changes to your database. Understanding the different types of triggers and how they work can be very useful in developing efficient and effective database solutions. I hope this will help you. --- Original Source: https://www.mindstick.com/forum/157530/what-are-the-types-of-triggers-in-the-sql-server Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.