Introduction to SQL Server Trigger

In this SQL Server tutorial, we will learn and understand how SQL Server TRIGGER works. Moreover, we will discuss and learn some examples so that you can understand the concept much better. Here is the complete list of topics that we will cover.

  • SQL Server Trigger
  • SQL Server Trigger Types
  • SQL Server Triggers List
  • SQL Server Triggers Pros And Cons
  • SQL Server Disable a Trigger
  • SQL Server Drop a Trigger
  • SQL Server Create a Trigger

SQL Server Trigger

In this SQL Server section, we will learn and understand Trigger in depth.

The stored procedures known as SQL Server triggers are executed automatically in response to events that occur in the database object, database, or server in SQL Server. There are three types of triggers in the SQL Server:

  • Data Manipulation Language (DDL) is invoked automatically in response to DELETE, INSERT and UPDATE statements which are used against tables.
  • Data Definition Language(DDL) is used in response to CREATE, DROP and ALTER statements. It also fires in response to some system store procedure that performs like DDL operations in the table.
  • Logon triggers are events that are triggered in response to LOGON events.

Effective use of Triggers in the SQL Server:

  • To create a trigger in the SQL Server- it shows how to create a trigger in the INSERT and DELETE events.
  • Creating of INSTEAD OF Trigger- learn about the INSTEAD OF trigger and its practical applications.
  • Creating a DDL Trigger- in this, we will learn and understand how to monitor the changes which are made to the structures of the database such as tables, views, and indexes.
  • Disable Triggers- in this, we will learn how to disable triggers temporarily from the table so that it can’t fire the associates that occur.
  • Enable Triggers- how to enable a trigger.
  • Viewing the definition of a trigger- provides various ways to view the definition of a trigger.
  • Limit of all triggers in the SQL Server- shows how to list all triggers in a SQL Server by querying the data from the sys. triggers view.
  • Removing Triggers- it guides you on how to drop more than one trigger.

We hope you have thoroughly understood the SQL Server Triggers concept.

Read SQL Server First Day Of Month

SQL Server Trigger Types

In this section, we will learn and understand the types of triggers that allow blaze the triggers in detail.

There are three types of SQL Server Triggers in the database which are given below:

  • DML (Data Manipulation Language) triggers
  • Logon Triggers
  • DDL (Data Definition Language) triggers

Code is run using DML Triggers in response to data processing. In other words, they allow us to execute extra code in response to the execution of an insert, delete or update statement.

DDL Triggers enable us to execute code in response to server events, such as when a user checks in, as well as database structure changes, such as dropping or creating a table. Depending on where they are utilized, DDL Triggers can be divided into two groups.

  • Server Scoped DDL Triggers
  • Database Scoped DDL Triggers

The LOGON event, which is triggered when a different user event is created, leads to the release of Server Scoped DDL Triggers, a specific case of the User login Triggers.

We hope that you have understood the types of triggers in the SQL Server in depth.

Read: SQL Server Datetime functions examples

SQL Server Triggers List

We will learn and understand how to list all SQL Server Triggers from the database by the query. And which will be explained with the help of an illustrated example.

It contains each row for each object in the trigger having the type TA or TR. The DML Trigger is named as the scheme scoped and DDL Trigger is named as the parent entity. One trigger is visible in the sys. objects and another is visible in the view.

The PARENT_CLASS and NAME columns uniquely identify the triggers in the database.

Here is a demonstrated example of SQL Server triggers a list of the database by using the SELECT statement from the below query:

EXAMPLE:

SELECT NAME, PARENT_CLASS, PARENT_ID,TYPE_DESC,
IS_INSTEAD_OF_TRIGGER
FROM SYS.TRIGGERS
WHERE TYPE=' TR';

Before we move to the example explanation, we want to explain expressions uses in the SYS.TRIGGERS from the database.

The main purpose of the NAME is used for the trigger_name from the database which carries the SYSNAME datatype with it. The PARENT_CLASS refers to a class of the parent of the trigger whereas the 0 defines the database for the DDL triggers and 1 defines the column for the DML triggers and it carries the TINYINT data type.

The TYPE_DESC use with the NVARCHAR(60) and defines the description of object types which are CLR_TRIGGER and SQL_TRIGGER.

The IS_INSTEAD_OF_TRIGGER is used with the BIT data type and 1 is used for INSTEAD OF TRIGGERS and 0 is used for AFTER TRIGGERS.

As we see in the above query, the SELECT statement is used to retrieve all records of the NAME, PARENT_CLASS, PARENT_ID and TYPE_DESC columns from the SYS.TRIGGERS database with the WHERE condition. In the WHERE condition, the TYPE refers to object type and it is used with the EQUAL operator to find SQL trigger as TR from the database.

If the WHERE condition finds the SQL trigger as TR from the database and sets it to be TRUE then it will the SELECT statement will retrieve the list of triggers name from the system database under the NAME column.

If virtually the WHERE condition isn’t able to find the trigger name from the database then it will provide the column list with the empty records by the SELECT statement.

Sql Server trigger list example
Example of SQL Server Trigger List

We hope that you have understood how to list all triggers from the system triggers by using the SELECT statement. For a better explanation, we have used an example and explained it in detail.

Read: Temp table in stored procedure in SQL Server

SQL Server Triggers Pros And Cons

The Advantages and Disadvantages of triggers in SQL Server will be covered in detail in this section.

We trust you now fully comprehend the syntax and operation of the triggers. Triggers are simple to use, as we all know. They appear to the observer to be a unique class of store procedure that is carried out on desire. And each DML statement’s execution includes their execution.

The trigger as STORE PROCEDURE has the flaw of concealing a number of advantages and disadvantages. It has the ability to perform tasks that we can perform independently using store methods.

PROS of Server Triggers

  • Coding the triggers is simple. It is true that they are coded similarly to store operations, which makes using the triggers simple, to begin with.
  • From inside a trigger, we can call the store procedures and triggers.
  • We can use the external code as CLR triggers. This type of trigger defines the .NET assembly method that will be used to bond with the trigger.
  • It allows recursion. Triggers are iterative when a trigger on a table takes an action on the base table, causing another trigger instance to fire. When you need to solve a self-referencing relation, this comes in handy (i.e. a constraint to itself).
  • Up to 32 levels can be added to it. When it executes an action that sets off another trigger, it is seen as nested.
  • You may establish simple auditing with triggers. You may design a suitable audit solution by employing the deleted table inside a trigger that inserts the contents of the delete table data into an audit table that holds the data that is being erased by a DELETE statement or altered by an UPDATE statement.

CONS of Triggers in SQL Server:

  • Recursive Triggers are even more difficult to debug than nested triggers.
  • If we use triggers to enforce referential integrity then we have to be aware that the triggers can be disabled by users that have the ALTER TABLE statement on the table. To avoid the mistake, we have to review the user permissions.
  • Triggers are not activated when we use the BULK INSERT statement to add data to the table unless we also utilise the FIRE_TRIGGERS option. Keep in mind if you’re using the BULK INSERT statement even without the FIRE_TRIGGERS option and your business logic includes triggers on the table, the data consistency will be lost. The problem of using the triggers is used for audit purposes only when the triggers are enabled, they are executed regardless of the circumstances of the trigger to be fired.
  • If there are so many triggers then it becomes harder to debug and troubleshoot, which means it will consume the development time and resources.
  • Overheard is added to DML statements by triggers. Every time we execute the DML statement that is linked to triggers. It implies that the trigger and DML statement can be used. However, the DML statement won’t end by definition until the trigger execution is finished. Production may suffer as a result of this.

We hope that you have understood the subtopic “SQL Server Triggers Pros and Cons” in detail.

Read: SQL Server User Permissions

SQL Server Disable a Trigger

By employing the SQL Server DISABLE TRIGGER statement on the database by the query, we will discover and comprehend how to disable a trigger. And that will be clarified with the aid of an example.

Sometimes, we wish to block a trigger temporarily due to the obvious debugging or data retrieving goals. The DISABLE trigger statement by the following query has the following syntax:

SYNTAX:

DISABLE TRIGGER [SCHEMA_NAME].[TRIGGER_NAME]
ON [OBJECT_NAME | DATABASE | ALL SERVER]; 

The syntax explanation is given below:

  • After the DISABLE TRIGGER statement, first, indicate the schema_name whereby the trigger corresponds and the trigger_name that you want to disable.
  • Second, if the trigger is a DML trigger, the schema_name or view that it was linked to. If the trigger is a DDL database scoped trigger, we will just use the DATABASE keyword; if it is a DDL server scoped trigger, then will use the SERVER keyword.

Here is an example of the DISABLE TRIGGER statement to disable a trigger from the database by the following query:

EXAMPLE:

DISABLE TRIGGER INSERTEMP_TRIGGER
ON HARVARD_UNIVERSITY;

INSERT INTO HARVARD_UNIVERSITY (STUDENT_ID,STUDENT_FIRSTNAME,STUDENT_LASTNAME,EMAIL,GENDER,
STUDENT_ADMITDATE,STUDENT_ENDDATE)
VALUES (12,'Mike','Tyson','tyson.70.mike@gmail.com','Male',
'2021-06-09','2024-08-10');

In this preceding query, the DISABLE TRIGGER statement is used to disable a trigger name as INSERTEMP_TRIGGER which is based on the HARVARD_UNIVERSITY table. After that, the trigger will not be fired if we try to insert new records in the HARVARD_UNIVERSITY table.

We hope that you have understood how to use the “SQL Server Disable a Trigger” by using the DISABLE TRIGGER statement on the database by the query. For a better explanation, we have used an illustrated example and explained it in deepness.

Read: Difference between table and view in SQL Server

SQL Server Drop a Trigger

In this section, we will learn and understand how to use SQL Server DROP TRIGGER statement to remove existing triggers.

The SQL Server DROP TRIGGER is used to drop more than one trigger from the database. Here is a syntax of the DROP TRIGGER which removes DML triggers:

SYNTAX:

DROP TRIGGER [IF EXISTS] [SCHEMA_NAME].TRIGGER_NAME [, ..... N];

The syntax explanation:

  • Only when the trigger is present in the database is it removed using the IF EXISTS clause.
  • The DML trigger’s schema is identified by its name in the SCHEMA_NAME field.
  • The trigger we wish to remove is called “trigger name.” Use the commas at that point if we wish to eliminate several triggers at once.

One more variation of the DROP TRIGGER statement can be used to get rid of one or more DDL triggers, and it looks like this:

DROP TRIGGER [IF EXISTS] [SCHEMA_NAME].TRIGGER_NAME [, ..... N]
[ ON DATABASE | ALL SERVER];

In this syntax explanation:

  • The word “DATABASE” denotes the DDL trigger idea as it applies to the current database.
  • The DDL trigger idea, as applied to the present server, is denoted by the phrase ALL SERVER.

Here is an illustrated example of the SQL Server DROP TRIGGER by the following query:

EXAMPLE:

DROP TRIGGER IF EXISTS INSERTEMP_TRIGGER;

in this preceding query, the DROP TRIGGER statement is used to drop the trigger name as INSERTEMP_TRIGGER from the SQLSERVERGUIDES database. The main purpose of the IF EXISTS clause in the database is that it will check whether the trigger_name as INSERTEMP_TRIGGER already existed in the database or not.

By utilising the DROP TRIGGER statement in the database by the query, we hope you have comprehended the subtopic “SQL Server drop a trigger.” We gave a detailed explanation and presented an example to help with understanding.

Read: Alter view in SQL Server

SQL Server Create a Trigger

In this SQL Server section, we will learn and understand the creation of a trigger in detail and which will be explained with the help of an illustrated example and syntax.

To understand how triggers work in the table, first, we will create a table called HARVARD_UNIVERSITY table by the following query:

EXAMPLE:

CREATE TABLE HARVARD_UNIVERSITY (
	STUDENT_ID INT,
	STUDENT_FIRSTNAME VARCHAR(50),
	STUDENT_LASTNAME VARCHAR(50),
	EMAIL VARCHAR(50) UNIQUE,
	GENDER VARCHAR(50),
	STUDENT_ADMITDATE DATE UNIQUE,
	STUDENT_ENDDATE DATE
);

INSERT INTO HARVARD_UNIVERSITY (STUDENT_ID, STUDENT_FIRSTNAME, STUDENT_LASTNAME, EMAIL, GENDER, STUDENT_ADMITDATE, STUDENT_ENDDATE)
 VALUES (1, 'Elonore', 'Rosenschein', 'erosenschein0@ucoz.com', 'Genderfluid', '2021-07-11 09:30:03', '2022-09-29 07:07:32'),
(2, 'Algernon', 'Joblin', 'ajoblin1@house.gov', 'Male', '2021-07-22 10:14:31', '2024-01-18 20:00:28'),
(3, 'Dieter', 'Serginson', 'dserginson2@wired.com', 'Male', '2021-08-27 08:38:04', '2022-11-24 16:36:51'),
(4, 'Buddy', 'Zavittieri', 'bzavittieri3@paginegialle.it', 'Male', '2021-08-30 19:13:15', '2022-03-31 03:37:28'),
(5, 'Todd', 'Bullan', 'tbullan4@amazon.de', 'Male', '2021-07-05 20:23:22', '2023-04-13 16:39:03'),
(6, 'Coletta', 'Dabbs', 'cdabbs5@hubpages.com', 'Non-binary', '2021-07-25 08:58:38', '2025-03-12 09:35:11'),
(7, 'Jacinda', 'Matzkaitis', 'jmatzkaitis6@wikipedia.org', 'Female', '2021-08-02 08:53:09', '2022-09-08 22:31:19'),
(8, 'Gerek', 'Greim', 'ggreim7@uol.com.br', 'Non-binary', null, NULL),
(9, 'Anthiathia', 'Huggins', 'ahuggins8@storify.com', 'Female', null, NULL),
(10, 'Keefer', 'Westcott', 'kwestcott9@jalbum.net', 'Male', '2021-08-12 22:34:07', '2024-03-23 16:52:10');

SELECT * FROM HARVARD_UNIVERSITY; 

In the above queries, first, we have created a table called the HARVARD_UNIVERSITY table by using the INSERT INTO statement. To insert some records in the HARVARD_UNIVERSITY table, we have used the INSERT INTO statements. And then we will use the SELECT statement to retrieve all records of the HARVARD_UNIVERSITY table.

Sql Server create a trigger
SQL Server SELECT statement for HARVARD_UNIVERSITY table

Now we will create another table called HARVARD_AUDIT__SHEET which will store automatically store records of each operation such as INSERT, DELETE and UPDATE statements on the HARVARD_UNIVERSITY table.

CREATE TABLE HARVARD_AUDIT_SHEET(
STUDENT_ID INT IDENTITY,
AUDIT_ON_ACTION TEXT);

Now we will create a trigger that will store the trade of each INSERT operation on the HARVARD_UNIVERSITY table into the HARVARD_AUDIT_SHEET. Now, we will create an INSERT trigger by the below query:

CREATE TRIGGER INSERTEMP_TRIGGER
ON HARVARD_UNIVERSITY
FOR INSERT 
AS 
BEGIN 
DECLARE @STUDENT_ID INT 
SELECT @STUDENT_ID= STUDENT_ID FROM INSERTED 
INSERT INTO HARVARD_AUDIT_SHEET 
VALUES (' New Student With ID'+ CAST(@STUDENT_ID AS VARCHAR(20)))
END;

INSERT INTO HARVARD_UNIVERSITY (STUDENT_ID,STUDENT_FIRSTNAME,STUDENT_LASTNAME,EMAIL,GENDER,
STUDENT_ADMITDATE,STUDENT_ENDDATE)
VALUES (11,'Jane','Minson','minson.76.jane@yahoomail.com','Female',
'2021-07-08','2024-05-06');

SELECT * FROM HARVARD_AUDIT_SHEET;

As we see in the above query, we have created a trigger called INSERTEMP_TRIGGER for the HARVARD_UNIVERSITY table which is based on the INSERT statement. In the AS statement, the STUDENT_ID value will be inserted into the same column in the HARVARD_AUDIT_SHEET table.

Once the trigger is created, we will try to insert some records into the HARVARD_UNIVERSITY table which means that the value of the STUDENT_ID column will same inserted into the HARVARD_AUDIT_SHEETS table.

If we will found no error by using the CREATE TRIGGER statement, then we will check the HARVARD_AUDUT_SHEETS table by using the SELECT statement.

To check the new records which are inserted by using the INSERT INTO statement on the HARVARD_UNIVERSITY table, is it same records of the STUDENT_ID column has been inserted or not in the HARVARD_AUDIT_SHEET table by using the SELECT statement.

Sql Server create a trigger example
Example of SQL Server Create a Trigger

We hope that you have understood the subtopic “SQL Server Create a trigger”. For better understanding, we have used an example and explained it in depth.

You may also like to read the following SQL Server tutorials.

So, in this SQL Server tutorial, we understood how SQL Server TRIGGER works. Moreover, we have also discussed some examples so that you can understand the concept much better. Here is the complete list of topics that we have covered.

  • SQL Server Trigger
  • SQL Server Trigger Types
  • SQL Server Triggers List
  • SQL Server Triggers Pros And Cons
  • SQL Server Disable a Trigger
  • SQL Server Drop a Trigger
  • SQL Server Create a Trigger