Introduction to Triggers in SQL Server

SQL Server Triggers are used to execute after or before an INSERT, DELETE, or an UPDATE operation on a table. You can use these SQL triggers on Views, or Tables to perform any of the above-specified activities. Remember, you can associate a trigger to a single table only.

We can call the SQL Server triggers as a special type of stored procedure that runs automatically when the specified event occurred on the database object. That can be DDL or DML operations.

Types of Triggers in SQL Server

There are two types of Triggers in SQL Server, and they are AFTER, and INSTEAD OF.

AFTER TRIGGERS in SQL Server

The after/for triggers in SQL runs after an INSERT, DELETE, or an UPDATE on a table. It means, Before this stored procedure starts running, all the operations should execute, and the statement has to succeed in the constraint check as well.

After triggers are not supported on Views so, use them on tables only. It can further divide into

  1. AFTER INSERT: It will fire after the completion of Insert data operation on the Employee table. Once it completes inserting into the Employee table, it will start inserting into the audit table. Say, if it fails to insert into the Employee, then it won’t insert into the Audit table.
  2. AFTER UPDATE: This SQL server update trigger will fire after the Update operation completed on the Employees table. Once it ends Updating the Employee, it will start inserting/updating into the audit table. For instance, if it fails to update the Employees table, then it won’t insert into the Audit.
  3. AFTER DELETE: The After delete will fire after the completion of Delete statement operation on the Employee table. Once it completes deleting the records from the Employees, it will start inserting/deleting from the audit table. Say, if it fails to remove from the Employee, then it won’t insert into the Audit table

INSTEAD OF TRIGGERS in SQL Server

SQL Server Instead of Triggers fired before the execution of an INSERT, DELETE, or an UPDATE on a table start.

It means, Before the trigger starts running, it does not need any condition constraint check. So, this stored procedure will execute even the constraint check fails. It can further divide into

  • INSTEAD OF INSERT: This instead of insert trigger will fire before the Insert operation on Employee table starts. Once it completes inserting into the Employee Audit table, it will begin to insert into the Employee. And if it fails for some reason, it won’t insert into the Employee table.
  • INSTEAD OF UPDATE: This update trigger will fire before updating the records in the Employee table. Once it completes the execution, it will start updating the records in the Employee. And if it fails, it won’t update the table.
  • INSTEAD OF DELETE: This delete trigger will fire before deleting records from the Employee table starts. Once it executed successfully, it will begin deleting records from the Employees table. And if it fails, it won’t remove any record from the Employee.

Data Definition Language Triggers

The SQL Data definition language triggers fires in response to the DDL events, including CREATE, ALTER, DROP, etc. We can use these DDL triggers to prevent schema changes, record the events occurred in database schema. An example for this DDL triggers will be do something or fire when use drops a database or table.

Data Manipulation Language Triggers

SQL DML triggers fires in response to data manipulation language events, including INSERT, DELETE, and UPDATE statements. The DML triggers are the special type of stored procedure that are the first step to data integrity. If you further divide the data manipulation language triggers, they are Instead of and after.

Logon Triggers

As the name suggest, SQL Logon triggers will fire when a user trying to LOGON or establish a connection to the server.

Create Trigger Statement in SQL Server

The syntax of After Triggers in SQL Server is

CREATE [OR ALTER] TRIGGER [Schema_Name].Trig_Name
ON Table
AFTER INSERT | UPDATE | DELETE
AS
   BEGIN
      -- Statements
      -- Insert, Update, Or Delete Statements
   END
  • Schema_name: Please specify the schema name. For example, dbo, or Human Resource, etc.
  • Trig_Name: You can specify any name you wish to give other than the system reserved keywords. Please try to use meaningful names so that you can identify them easily.

The basic syntax of create Instead Of Triggers in SQL Server is as shown below:

CREATE [OR ALTER] TRIGGER [Schema_Name].Trig_Name
ON Table | View
INSTEAD OF INSERT | UPDATE | DELETE
AS
   BEGIN
      -- Statements
      -- Insert, Update, Or Delete Statements
   END

From the above create trigger statements, it is the same create statement that we used before but we added INSTEAD OF INSERT | UPDATE | DELETE.

Let us see how to Create trigger, Modify the existing one, and delete triggers in SQL Server with an example.

For the Sql Server triggers demonstration, we are working with the Employee and audit table. As you can see, both the emp and the audit tables are Empty

and our Audit is also empty

Employee and Audit Tables

Create Triggers in SQL Server Example

We can create them in two ways. In this example, we show you the steps involved in creating it using the SQL Server Management Studio (SSMS). Before we start generating any, let us see whether our database had any existing ones or not.

To view the existing, Please select the Database object -> Expand the specified Table Name (on which it exists) -> Go to Triggers Folder and expand it. From the below screenshot, you can observe that our Employee table under the database has none.

Right-click on the folder will open the context menu. Please select the New.. option from it as we shown below.

Create a New Trigger in SQL Server Management Studio

Once you click on that option it will open the new Query window with default Template as we have shown below

Auto Generated Create Trigger Code

Here, you can add the Schema name, Trig_name, TableName, Data modification statements, and the statements that you want to apply.

Create SQL Triggers using transact query

For this demonstration purpose, we are creating an After Insert Trigger in SQL Server to Insert the records into Employee Table Audit, along with the audit information

CREATE TRIGGER ExampleTR 
   ON  EmployeeTR 
   AFTER INSERT
AS 
BEGIN
     -- SET NOCOUNT ON added to prevent extra result sets from
     -- interfering with SELECT statements.
	SET NOCOUNT ON;

     -- Insert statements here
	INSERT INTO [Audit]( 
       [ID]
      ,[Name]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[ServerName]
      ,[ServerInstanceName]
      ,[Insert Time])
SELECT  ID,
	Name,
	Education,
	Occupation,
	YearlyIncome,
	Sales,
	CAST( SERVERPROPERTY('MachineName') AS VARCHAR(50)), 
	CAST( SERVERPROPERTY('ServerName') AS VARCHAR(50)), 
	GETDATE()
FROM INSERTED;
PRINT 'We Successfully Fired!.'
END
GO

We already explained the statements inside the SQL triggers in our previous article. I suggest you refer AFTER INSERT article to under this code.

Run the above query

Messages
--------
Command(s) completed successfully.

Let me show you the newly created one by opening the Object Explorer -> Go to the Database -> Go and expand the Employee -> and then expand the Folder

Create Triggers in SQL Server using Transact Query 6

Let me insert 5 random records into the Employee for the demonstration purpose. It will help us to check whether the After insert is fired or not.

INSERT INTO [EmployeeTR] (
		[Name]
	       ,[Education]
	       ,[Occupation]
	       ,[YearlyIncome]
	       ,[Sales]
	     )
VALUES ('Tutorial Gateway', 'Masters Degree', 'Admin', 250000, 1900)
      ,('Aamir Khan', 'Bachelors', 'Skilled Professional', 69000, 100)
      ,('Lara Diaz', 'Bachelors', 'Management', 85000, 60)
      ,('Mahesh kumar', 'Degree', 'Professional', 45000, 630)
      ,('Ruiz John', 'Post Graduate', 'Clerical', 40000, 220)
Insert Records into Table

From the above screenshot, you can see that our trigger is fired. And also, it is printing the message that we placed in it. Please use the following query to check the inserted records in the Employee.

SELECT [ID]
      ,[Name]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [EmployeeTable]

Next, check the records in the Audit table using the following query.

SELECT [ID]
      ,[Name]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[ServerName]
      ,[ServerInstanceName]
      ,[Insert Time]
  FROM [EmployeeTableAudit]
Employee and Audit Tables

Modify Triggers in SQL Server Example

The following examples will help you understand the steps involved in modifying the existing SQL Triggers using the Management Studio, and query.

Before we get into the SQL Trigger modification, let me show you the list of available options that the Management Studio is providing to you. As we show below, you can drop it, drop and recreate, alter it, and create it.

Available Options of Triggers in SQL Server

To modify it using the Management Studio, Please navigate to the table that holds them. Next, select the one that you want to modify (ExampleTR), and right-click on it will open the context menu. Here, select the Modify option

Modify Triggers in SQL Server Management Studio

Once you choose the Modify option, a new query window will open with auto-generated code. You can modify it as per your requirement.

Use SQL Server Alter Trigger to Modify example

The steps involved in modifying the existing one using the ALTER statement. To do so, Click New query and return the following query.

ALTER TRIGGER [dbo].[ExampleTR] 
   ON  [dbo].[EmployeeTR] 
   AFTER INSERT
AS 
BEGIN
 -- Modify as per Your requirement
END

Delete Triggers in SQL Server Example

The following examples will show you, How to drop, them using query, and Management Studio (SSMS). Here, we show you how to delete them using the SSMS. To delete in using the SSMS, right-click on the name that you want to delete will open the context menu. You can click on the Delete option

Delete or Drop Triggers in SQL Server

Here, we will show you how to delete it using the SQL Drop Trigger statement.

DROP TRIGGER [dbo].[ExampleTR]