Trigger and its types in SQL

Shristi
Shristi
Nov 5 · 2 min read

A trigger is a set of SQL statement which is fired when a DDL or DML command is executed

A trigger is similar to the stored procedure as both of them store a group of SQL statements under a name so it gets executed but the trigger is not fired by the execute statement it gets trigger by DDL or DML statement.

Types of Triggers

  1. DDL Trigger
  2. DML Trigger
  3. Logon Trigger

1.DDL Trigger

Data definition language trigger is the trigger that gets fired when any of the DDL Statements like CREATE, ALTER, and DROP.

This trigger can be used to monitor and control the actions that are performed.

2.DML Trigger

DML is an acronym for Data Manipulation Language Trigger is triggered when the DML statements are fired like INSERT, UPDATE, and DELETE.

This trigger is of two types:

AFTER Clause

The AFTER clause is used to execute the statement after the DML statement is executed

for example: if we are inserting a record into the table then this trigger will get executed after the data as been entered into the table

INSTEAD OF Clause

The INSTEAD OF trigger is fired before the statements get executed it is different from the AFTER clause as it is fired after the execution.

We can also describe it as the trigger which is executed instead of the triggering statement that is Insert, Update, or Delete.

3.Logon Trigger

Logon trigger is fired when the LOGON event of SQL is raised.

This trigger is raised when a user begins a session in the SQL Server

It is executed only if the user finished the authentication process if the authentication is failed then the trigger is not fired.

It is used to not only monitor but also to audit and control the server session.

If you are new to SQL Server start with the following must-watch video: -

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade