Source : https://www.c-sharpcorner.com/UploadFile/a53f1a/triggers-in-sql-server/ 


Types of Triggers

 
In SQL Server we can create the following 3 types of triggers:
  • Data Definition Language (DDL) triggers
  • Data Manipulation Language (DML) triggers
  • Logon triggers


DDL Triggers : CREATE, ALTER and DROP

 
In SQL Server we can create triggers on DDL statements (like CREATE, ALTER and DROP) and certain system-defined Stored Procedures that does DDL-like operations.

ALTER : Alter command will perform on structure level and not on the data level.
UPDATE : Update command will perform on the data level


DML Triggers : INSERT, UPDATE and DELETE

 
In SQL Server we can create triggers on DML statements (like INSERT, UPDATE and DELETE) and Stored Procedures that do DML-like operations. DML Triggers are of two types.

Logon Triggers :


Logon triggers are a special type of triggers that fire when a LOGON event of SQL Server is raised. This event is raised when a user session is being established with SQL Server that is made after the authentication phase finishes, but before the user session is actually established. Hence, all messages that we define in the trigger, such as error messages, will be redirected to the SQL Server error log. Logon triggers do not fire if authentication fails. We can use these triggers to audit and control server sessions, such as to track login activity or limit the number of sessions for a specific login.

Trigger syntax :

  1. CREATE TRIGGER triggerName ON TableName   
  2. AFTER INSERT |After Delete |After Upadte  
  3. AS BEGIN  
  4.   
  5. INSERT INTO dbo.UserHistory............  
  6.   
  7. END   

Example :

  1. CREATE TABLE Employee_Test  
  2. (  
  3. Emp_ID INT Identity,  
  4. Emp_name Varchar(100),  
  5. Emp_Sal Decimal (10,2)  
  6. )  

  1. INSERT INTO Employee_Test VALUES ('Anees',1000);  
  2. INSERT INTO Employee_Test VALUES ('Rick',1200);  
  3. INSERT INTO Employee_Test VALUES ('John',1100);  
  4. INSERT INTO Employee_Test VALUES ('Stephen',1300);  
  5. INSERT INTO Employee_Test VALUES ('Maria',1400);  

  1. CREATE TABLE Employee_Test_Audit  
  2. (  
  3. Emp_ID int,  
  4. Emp_name varchar(100),  
  5. Emp_Sal decimal (10,2),  
  6. Audit_Action varchar(100),  
  7. Audit_Timestamp datetime  
  8. )  

  1. CREATE TRIGGER trgAfterInsert ON [dbo].[Employee_Test]   
  2. FOR INSERT  
  3. AS  
  4. declare @empid int;  
  5. declare @empname varchar(100);  
  6. declare @empsal decimal(10,2);  
  7. declare @audit_action varchar(100);  
  8. select @empid=i.Emp_ID from inserted i;   
  9. select @empname=i.Emp_Name from inserted i;   
  10. select @empsal=i.Emp_Sal from inserted i;   
  11. set @audit_action='Inserted Record -- After Insert Trigger.';  
  12.   
  13. insert into Employee_Test_Audit  
  14. (Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)   
  15. values(@empid,@empname,@empsal,@audit_action,getdate());  
  16.   
  17. PRINT 'AFTER INSERT trigger fired.'  
  18. GO  

insert into Employee_Test values('Ravi',1500); 


Select * from Employee_Test_Audit