Triggers in SQL Server

Triggers are stored code that executes automatically when some events occur, such as changes to data and data objects in the database.

Types of Triggers

DDL Triggers
DML Triggers
LOGON Triggers

DDL Triggers

DDL stands for Data Definition Language, which is a kind of trigger that occurs from a response when executing DDL commands such as CREATE, DROP, and ALTER.

In the below code, create a database, and after creating the trigger, it will return a successful response with a welcome message.

Create trigger sample
on all server
for create database
As
Begin
select 'New Database created successfully...' AS [Welcome Message]
Print'--New Database Created successfully--'

create database databasename;

DML Triggers

DML triggers stand for Data Manipulation Language, and this kind of trigger occurs as a response to executing DML commands such as INSERT, DELETE, and UPDATE. These triggers are widely used.

It classifies two types:

After triggers
Instead of triggers

After Triggers

Create tables named Employee1 and Employee2.

create table Employee1(Emp_id int Identity, Emp_name varchar(100), Emp_salary int);

Create a trigger for whenever inserting values from the Employee1 table occurs.

Go 
create trigger sample on employee1
for insert
As
declare @empid int
declare @empname varchar(100)
select @empid = Emp_id from inserted
select @empname = Emp_name from inserted
insert into Employee2(Emp_id, Emp_name) values (@empid, @empname)
print 'After Insert trigger  fired'.
Go

LOGON Triggers

Logon triggers occur when the user logs into a session to check whether the connection has been established successfully. It is used for users having limitations in login sessions, tracking login activity,

Create a trigger for a specific username and password with restricted timing slots, such as 9 a.m. to 6 p.m. only.

use [master]
Go
create login [testuser] with password = N'testuser@123', DEFAULT_DATABASE = [master], CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF
Go

Create trigger when user connect server with credentials and get back error message as triger execution.

create or alter trigger Logon_In_Timeslot
on all server
for logon
As
Begin
if((ORIGINAL_LOGIN()='testuser')
   AND
   ((DATEPART(HOUR,GETDATE()) between 18 and 24)
   OR
   (DATEPART(HOUR, GETDATE()) between 0 and 9)
   ))
   Begin
      ROLLBACK
   End
End

Conclusion

So the triggers are easy to maintain and save time by reducing client-side code. And we also don’t want to wait for scheduled tasks that run automatically before or after a change in the database. Then it triggered an event that was followed by a specific data operation from the database.