MS SQL Server Triggers

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 SQL, SQL Server Interview Questions, and Azure SQL by clicking on the respective links.

To grasp more knowledge about MongoDB, Databases, Operational 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!