Introduction to Triggers in SQL Server

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

We can call the SQL Server Trigger a special type of stored procedure that runs automatically when the specified event occurs 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.

SQL Server AFTER TRIGGERS

The SQL Server after/for triggers run on a table after an INSERT, DELETE, or an UPDATE. It means, Before this stored procedure starts running, all the operations should execute, and the statement must also succeed in the constraint check.

In SQL Server, the After triggers are not supported on Views, so use them on tables only. It can be further divided into

  1. AFTER INSERT: It will fire after the completion of the 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: The SQL Server update triggers will fire after the Update operation is 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 it into the Audit.
  3. AFTER DELETE: The After delete will fire after completing the 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.

SQL Server INSTEAD OF TRIGGERS

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

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

  • INSTEAD OF INSERT: Instead of insert trigger will fire before the Insert operation on the 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: It 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: It will fire before deleting records from the Employee table starts. Once it executes 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 in SQL Server

The Data definition language triggers fires in response to the DDL events, including CREATE, ALTER, DROP, etc. We can use them to prevent schema changes and record the events that occurred in the database schema. An example of this DDL trigger will be doing something or firing when dropping a database or table.

Data Manipulation Language Triggers in SQL Server

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

Logon Triggers

As the name suggests, the SQL server Logon triggers will fire when a user tries 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 Resources, 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 creating SQL Server Instead Of Triggers 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 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 SQL Server triggers, Modify the existing ones, and delete them with an example.

We are working with the Employee and audit table for the demonstration. As you can see, the emp and the audit tables are Empty.

Employee and Audit Tables

Create Triggers in SQL Server Example

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

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 SQL Server Triggers folder will open the context menu. Please select the New.. option from it, as we have 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 the default Template, as we have shown below.

Auto Generated Code

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

Create using transact query

For this demonstration, 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 it in our previous article. I suggest you refer article AFTER INSERT to understand 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 check whether the After insert is fired.

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 SQL Server trigger is fired. And also, it prints 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

Alter Triggers in SQL Server to Modify Example

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

Before we get into the modification, let me show you the available options that Management Studio provides. As shown below, you can drop, drop, recreate, alter, and create triggers in SQL ServerManagement studio.

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 you want to modify (ExampleTR), and right-click on it to 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 an auto-generated code. You can modify it as per your requirement.

Use Alter Trigger to Modify

The steps involved in modifying the existing SQL Server trigger using the ALTER statement. To do so, Click the 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 a query and Management Studio (SSMS). Here, we show you how to delete them using SSMS. To delete using the SSMS, right-click the name you want to delete to 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 Server Drop Trigger statement.

DROP TRIGGER [dbo].[ExampleTR]
Categories SQL