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:

1. After triggers
2. 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.