SQL Server trigger tutorial with examples

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