A SQL Server Trigger is a specific kind of stored procedure that executes automatically whenever a database server event occurs. So, in this SQL Server tutorial, we will discuss how to define and use a SQL Server Trigger.
As a Database Administrator, I usually get requirements where I need to maintain the integrity constraints in the database, track changes in all the tables, maintaining logs, etc. And triggers in SQL Server play an important part in implementing all these tasks.
So, this tutorial will focus on the use and execution of Triggers in SQL Server. And here is a complete list of topics we will cover in this SQL Server tutorial.
- Introduction to SQL Server Trigger
- SQL Server Trigger Types
- SQL Server Trigger Syntax
- SQL Server Trigger Example
Introduction to SQL Server Trigger
As discussed earlier, a trigger in SQL Server is a database object. This database object is just like a stored procedure in SQL Server that is executed automatically based on an event in the SQL Server instance.
There are various types of events in SQL Server, for example adding or removing rows from a table, logging into a database server instance, updating a table column, creating, modifying, or deleting a table, etc., might set off a trigger.
Let us understand the use of triggers in SQL Server by taking a scenario.
Consider an eCommerce database that consists of the Products table. Now, in the Products table, multiple product entries with their price are available. However, before we change the price of a product from the Products table, we also want to insert an entry in the log table of the database.
In such situations, we can create a trigger in SQL Server that will automatically make an entry in the log table whenever we update the Products table.
Also, check: MySQL vs SQL Server
What is SQL Server Trigger Types
In SQL Server, we can define 3 different types of triggers based on our requirements. Let us discuss each one of them in detail.
- DML Triggers
- DDL Triggers
- Logon Triggers
DDL Triggers in SQL Server
As the name suggests, these type of triggers in SQL Server executes whenever a user attempts to edit or modify data using a data manipulation language (DML) statement.
DML operations are statements that INSERT, UPDATE, or DELETE data from a table or view. So, whenever the table rows are affected, these triggers are triggered whenever a legitimate event occurs.
DDL Triggers in SQL Server
DDL triggers in SQL Server are the triggers that get executed whenever a data definition language event occurs.
These events generally relate to Transact-SQL CREATE, ALTER, and DROP statements as well as some system stored procedures that do DDL-like activities.
Logon Triggers in SQL Server
These triggers are executed automatically based on LOGON events, which are raised whenever a session of a user is established.
Transact-SQL statements or methods of assemblies developed in the Microsoft.NET Framework common language runtime (CLR) and uploaded to a SQL Server instance can be used directly to build triggers. Moreover, we can define multiple triggers for any one statement in SQL Server.
Read: INSERT INTO SELECT Statement in SQL Server
SQL Server Trigger Syntax
Till now, in this tutorial, we have discussed why to use triggers in SQL Server and we also discussed different types of SQL Server Triggers. Next, we will take a step further and understand how to define a trigger in SQL Server.
For this, we need to understand the syntax to define a trigger in SQL Server which is given below.
CREATE TRIGGER [schema_name.]trigg_name
ON { tbl_name | v_name }
{ FOR | AFTER | INSTEAD OF } {[INSERT],[UPDATE],[DELETE]}
[NOT FOR REPLICATION]
AS
{sql_statements}
In the above syntax,
- schema_name is an optional keyword used to define the name of the schema where the trigger will be created.
- trigg_name is utilized to define the name of the new trigger in the database.
- After this, the ON { table_name | view_name } keyword is utilized to specify either the table or view name on which you want to define the trigger.
- Next, the AFTER clause is utilized to define the after-event and whether the trigger will be initialized after INSERT, UPDATE, or DELETE statement.
- Now, just like the AFTER clause, we can also use the INSTEAD OF clause. This clause is utilized to run other trigger-defined actions in place of an INSERT, UPDATE, or DELETE statement to a table. Therefore, nothing at all happens when you actually insert, update, or delete a statement.
- The [NOT FOR REPLICATION] clause instructs the SQL Server not to run the trigger when a replication agent alters the table.
- In the last, after the AS keyword, we can define the set of T-SQL statements that we want to execute.
Note: Please note that we cannot use the INSEAD OF clause while defining a DDL trigger.
Read: How to add column in SQL Server
SQL Server Trigger Example-1
After discussing the syntax, let us bring the syntax of defining a SQL Server Trigger into action and implement an example.
Here in this section, we will discuss the sample eCommerce database and Products table example that we discussed earlier.
Here is the Products table for our example execution and this Products table is located in the eShop database.

Here is the code for creating a trigger that will make an entry in the ProductsLog table based on entries we made in the Products table.
USE eShop
GO
CREATE TRIGGER dbo.trgProductInsert
ON dbo.Products
FOR INSERT
AS
BEGIN
INSERT INTO dbo.ProductsLog(product_id, operation, updatedDate)
SELECT id ,'INSERT',GETDATE() FROM INSERTED;
END
In this example, we have created a trigger that fetches the product id from the INSERTED table. And this trigger will make an entry in the ProductsLog table whenever we execute an INSERT statement on the Products table.
Note: Here in the example INSERTED is a virtual table in SQL Server that hold the values that we are inserting in our table.
Now, on every successful insertion in the Products table, the trigger will also make an entry in the ProductsLog table.
Here is the sample SQL query for the insert operation in the Products table.
USE eShop
GO
INSERT INTO Products
(id, name, supplier_id, inventory_id,
category_id, brand_id, price)
VALUES (6, 'Asus E410', 102, 206, 301, 2, '$137.50');
From the result of the above query, we can observe that 2 entries have been made.

Out of these 2 entries, one entry has been made in the Products table and another one has been made in the ProductsLog table.
And we can check the data of the ProductsLog table using the SELECT statement in SQL Server.

By the end of this section, we discussed how to use the syntax of defining a trigger in SQL Server. And we have also discussed an example of defining a trigger in SQL Server which will be executed based on the INSERT operation performed on a table.
Read: SQL Server function return table
SQL Server Trigger Example-2
In the previous example, we have seen how to create a trigger for a single operation in SQL Server. But, now we will take a step further and discuss how to create a trigger in SQL Server for multiple operations at once.
Let us discuss an example where we will create a trigger which will be executed for INSERT as well as DELETE operation. And here we are going to use the States table from the GeoNames database in SQL Server.
Here is the SQL for the States table in SQL Server.
USE GeoNames
GO
CREATE TABLE States (
id INT NOT NULL PRIMARY KEY,
name varchar(30) NOT NULL,
country_id INT NOT NULL
)
INSERT INTO States (id, name, country_id) VALUES
(1, 'California', 2),
(2, 'Texas', 2),
(3, 'Florida', 2),
(4, 'Alaska', 2),
(5, 'Georgia', 2)
Next, we are going to create a table which will keep a track of INSERT and DELETE operation performed on the States table. And the trigger will maintain the track of the operations in the StatesLog table.
Here is the T-SQL code for the trigger in SQL Server.
USE GeoNames
GO
CREATE TRIGGER dbo.trgStateInsertDelete
ON dbo.States
FOR INSERT, DELETE
AS
BEGIN
IF EXISTS (SELECT 1 FROM inserted)
BEGIN
INSERT INTO dbo.StatesLog(state_id, operation, updatedDate)
SELECT id ,'INSERT',GETDATE() FROM INSERTED;
END ELSE
IF EXISTS (SELECT 1 FROM deleted)
BEGIN
INSERT INTO dbo.StatesLog(state_id, operation, updatedDate)
SELECT id ,'DELETE',GETDATE() FROM DELETED;
END
END
In the above code, we have created a trigger named trgStateInsertDelete. And this trigger will insert a entry in the StatesLog table based upon INSERT or DELETE operation performed on the States table.
Now, if we perform either a INSERT operation or a DELETE operation on the States table, the trigger will get executed. Here is a sample DELETE operation on the States table.
USE GeoNames
GO
DELETE FROM States
WHERE name = 'California';
GO
In the above query, we are using the DELETE statement in SQL Server to delete a record from the States table where state name is California. And once we execute the above statement, the trigger will a entry in the StatesLog table.

In this section, we have discussed how we can create a trigger in SQL Server for multiple operations. And to understand this concept, we have also taken an example of the States table. This table contains various state names from the United States like California, Florida, Texas, etc.
Watch my YouTube video for a better understanding of the topic:
Conclusion
In this SQL Server tutorial, we have learned what are Triggers in SQL Server and how to create triggers in SQL Server. Additionally, we have discussed multiple examples of how to create SQL Server Triggers.
Here is the complete list of topics that we have discussed in this tutorial.
- Introduction to SQL Server Trigger
- SQL Server Trigger Types
- SQL Server Trigger Syntax
- SQL Server Trigger Example
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.