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 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 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
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 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
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.