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
- DDL Trigger
- DML Trigger
- 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: -
