Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
SQL Server Triggers
3.
Types of SQL Server Triggers
3.1.
Creating a DML SQL Server Trigger
3.2.
Creating a SQL Server DDL Trigger
3.3.
Disabling a Trigger
3.4.
Removing a Trigger
4.
Frequently Asked Questions
4.1.
Explain BIDS.
4.2.
Do you anything about SQLCMD?
4.3.
What is Full-Text Search Service available in Microsoft SQL Server?
4.4.
List some discontinued editions of Microsoft SQL Server.
5.
Conclusion
Last Updated: Mar 27, 2024
Medium

MS SQL Server Triggers

Author Rupal Saluja
0 upvote
Leveraging ChatGPT - GenAI as a Microsoft Data Expert
Speaker
Prerita Agarwal
Data Specialist @
23 Jul, 2024 @ 01:30 PM

Introduction

When we talk about databases, SQL is something that we cannot neglect. From the time databases have gained importance, the need to manage them better has also gained vitality. The unique features of SQL that make it so popular are its flexibility, its power, its accessibility, and its ease to learn. There are several database products that support SQL, but the two major and most widely used are the Oracle database and the Microsoft SQL Server.

In this blog, we will learn about various SQL Server Triggers available and their utilization.

SQL Server Triggers

By the word trigger, we understand that something is being executed or released. In SQL Server, Triggers are very similar to stored procedures. Triggers such objects that are executed or released automatically in response to any database object, database, or when a certain event occurs in the database. Some events that activate a trigger are inserting or deleting rows from a table, creation or dropping of a table, or any updation made in the table.

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

Types of SQL Server Triggers

There are three types of SQL Server Triggers, namely:

  • DML SQL Server Triggers

 

  • DDL SQL Server Triggers

 

  • Logon SQL Server Trigger

 

Now we will learn about these three triggers one by one.

DML SQL Server Triggers

Here, DML stands for Data manipulation language . The DML commands that we use in SQL to manipulate the data are responsible for firing an event. An event is fired on its own when commands such as insert, update or delete are pushed in the form of a query. 

DDL SQL Server Triggers

Here, DDL stands for Data Definition Language. The DDL commands that we use in SQL to define the table structure are responsible for firing an event. An event is fired on its own when commands such as create, alter or drop are pushed in the form of a query. This type of event is known as server scoped or database scoped events.

Logon SQL Server Trigger

When any user tries to log on to some database using a password or any other security check, then an event occurs which is known as a Logon event. This logon event is responsible to invoke the Logon Trigger.

Creating a DML SQL Server Trigger

To create a DML SQL Server trigger, we will use CREATE TRIGGER statement. The syntax for the same is written below.

CREATE TRIGGER [schema]trigger
ON { table | view }
{ FOR | AFTER | INSTEAD OF } {[INSERT],[UPDATE],[DELETE]}
[NOT FOR REPLICATION]
AS {sql_statements}

 

To understand which parameter in the above syntax means what, refer to the table present below.

Creating a SQL Server DDL Trigger

To create a DDL SQL Server trigger, we will again use CREATE TRIGGER statement. The syntax for the same is written below.

CREATE TRIGGER trigger
ON { DATABASE |  ALL SERVER}
[WITH ddl_trigger]
FOR {event_type | event_group }
AS {sql_statement}

 

To understand which parameter in the above syntax means what, refer to the table present below.

Disabling a Trigger

To disable a trigger in SQL Server, we will again use DISABLE TRIGGER statement. The syntax for the same is written below.

DISABLE TRIGGER [schema][trigger] 
ON [object | DATABASE | ALL SERVER]

 

To understand which parameter in the above syntax means what, refer to the table present below.

Must Read SQL Clauses

Removing a Trigger

To remove a trigger in SQL Server, we will again use the DROP TRIGGER statement. The syntax for the same is written below.

DROP TRIGGER [ IF EXISTS ] [schema.]trigger [,...n ];

 

To understand which parameter in the above syntax means what, refer to the table present below.

Also Read - TCL Commands In SQL

Frequently Asked Questions

Explain BIDS.

BIDS stands for Business Intelligence Development Studio. It is a framework developed by Microsoft which is used to provide data analysis and business intelligence solutions using Microsoft SQL Server Analysis Services (SSAS). It forms its basis from the Microsoft Visual Studio development environment, but it is more customized as it uses SQL Server services-specific extensions, projects, controls, and tools.

Do you anything about SQLCMD?

A command-line application that demonstrates the management features of SQL Server is known as SQLCMD. It is such an application that allows you to write and execute SQL queries from the command prompt. It also acts as a scripting language and thus helps in the creation and working of the script, which is a set of SQL statements. Such scripts are stored with the ‘.sql’ extension.

What is Full-Text Search Service available in Microsoft SQL Server?

As the name suggests, Full-Text Search Service is a specialized service used for indexing and querying unstructured data stored in SQL Server databases.

The Full-Text Search Engine is divided into two phases, namely the Filter Daemon phase and the Search phase. These phases interact with the SQL Server one at a time.

List some discontinued editions of Microsoft SQL Server.

Some discontinued editions of Microsoft SQL Server are listed below.

  • Microsoft SQL Server Data Engine (MSDE)
  • Personal Edition
  • Datacenter

Conclusion

In a nutshell, we understood what are SQL Server Triggers and learned about various HA technologies. We also saw the steps to create these SQL Server Triggers.

We hope the above discussion helped you understand SQL Server Triggers in clearer terms and can be used for future reference whenever needed. For a crystal understanding of Microsoft SQL Server, you can refer to our blogs on Microsoft SQLSQL Server Interview Questions, and Azure SQL by clicking on the respective links.

To grasp more knowledge about MongoDBDatabasesOperational Databases, and Non-Relational Databases, you can pay attention to our blogs on those topics by clicking on the respective links. Visit our website to read more such blogs. Make sure that you enroll in the courses provided by us, take mock tests and solve problems available and interview puzzles. Also, you can pay attention to interview stuff- interview experiences and an interview bundle for placement preparations. 

Do upvote our blog to help fellow ninjas grow.

Happy Coding!

Live masterclass