Different Types of SQL Server Triggers and Explanations

Triggers are database object. Basically these are special type of stored procedure that are automatically fired/executed when a DDL or DML command statement related with the trigger is executed. Triggers are used to assess/evaluate data before or after data modification using DDL and DML statements. These are also used to preserve data integrity, to control server operations, to audit a server and to implement business logic or business rule.

Types of Triggers

In Sql Server we can create four types of triggers Data Definition Language (DDL) triggers, Data Manipulation Language (DML) triggers, CLR triggers and Logon triggers.

1. DDL Triggers

   In SQL Server we can create triggers on DDL statements (like CREATE, ALTER, and DROP) and certain system defined stored procedures that perform DDL-like operations.
   Example : If you are going to execute the CREATE LOGIN statement or the sp_addlogin stored procedure to create login user, then both these can execute/fire a DDL trigger that you can create on CREATE_LOGIN event of Sql Server.
   We can use only FOR/AFTER clause in DDL triggers not INSTEAD OF clause means we can make only After Trigger on DDL statements.
   DDL trigger can be used to observe and control actions performed on the server, and to audit these operations. DDL triggers can be used to manage administrator tasks such as auditing and regulating database operations.

2. DML Triggers

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

   1. After Trigger (using FOR/AFTER CLAUSE)

      This type of trigger fires after SQL Server finish the execution of the action successfully that fired it.
      Example : If you insert record/row in a table then the trigger related/associated with the insert event on this table will fire only after the row passes all the constraints, like as primary key constraint, and some rules. If the record/row insertion fails, SQL Server will not fire the After Trigger.

   2. Instead of Trigger (using INSTEAD OF CLAUSE)

      This type of trigger fires before SQL Server starts the execution of the action that fired it. This is differ from the AFTER trigger, which fires after the action that caused it to fire. We can have an INSTEAD OF insert/update/delete trigger on a table that successfully executed but does not include the actual insert/update/delete to the table.
      Example : If you insert record/row in a table then the trigger related/associated with the insert event on this table will fire before the row passes all the constraints, such as primary key constraint and some rules. If the record/row insertion fails, SQL Server will fire the Instead of Trigger.

3. CLR Triggers

   CLR triggers are special type of triggers that based on the CLR (Common Language Runtime) in .net framework. CLR integration of triggers has been introduced with SQL Server 2008 and allows for triggers to be coded in one of .NET languages like C#, Visual Basic and F#.
   We coded the objects(like trigger) in the CLR that have heavy computations or need references to objects outside the SQL Server. We can write code for both DDL and DML triggers, using a supported CLR language like C#, Visual basic and F#. I will discuss CLR trigger later.

4. Logon Triggers

   Logon triggers are special type of trigger that fire when 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.
   Synatx for Logon Trigger

   
   CREATE TRIGGER trigger_name
   ON ALL SERVER
   [WITH ENCRYPTION]
   {FOR|AFTER} LOGON
   AS
   sql_statement [1...n ] 
   
   

   
   
   
   

   Syntax for Trigger

   
   CREATE TRIGGER trigger_name
   ON {table|view} 
   [WITH ENCRYPTION|EXECUTE AS] 
   {FOR|AFTER|INSTEAD OF} {[CREATE|ALTER|DROP|INSERT|UPDATE|DELETE ]} 
   [NOT FOR REPLICATION] 
   AS 
   sql_statement [1...n ] 
   
   

   1. trigger_name

      This is the name of the trigger. It should conform to the rules for identifiers in Sql Server.

   2. table|view

      This is the table/view on which the trigger is to be created.

   3. ENCRYPTION

      This option is optional. If this option is specified, original text of the CREATE TRIGGER statement will be encrypted.

   4. EXECUTE AS

      This option is optional. This option specifies, the security context under which the trigger is executed.

   5. FOR/AFTER

      FOR/AFTER specifies that the trigger is After Trigger. AFTER is the default, if FOR is the only keyword specified.AFTER triggers cannot be defined on views.

   6. INSTEAD OF

      INSTEAD OF specifies that the trigger is Instead Of Trigger.

   7. CREATE|ALTER|DROP|INSERT|UPDATE|DELETE

      These keywords specify on which action the trigger should be fired. One of these keywords or any combination of these keywords in any order can be used.

   8. NOT FOR REPLICATION

      Indicates that the trigger should not be executed when a replication process modifies the table involved in the trigger.

   9. AS

      After this we specifies the actions and condition that the trigger perform.

   10. sql_statement

       These are the trigger conditions and actions. The trigger actions specified in the T-SQL statements.
       
       

       Note

       1. The name of a trigger should follow the rules for identifiers.
       2. DML trigger can be composed by any T-SQL statements, except CREATE DATABASE, ALTER DATABASE, DROP DATABASE, LOAD DATABASE, LOAD LOG, RECONFIGURE, RESTORE DATABASE, and RESTORE LOG statements.
       3. You cannot create triggers against system tables or dynamic management views. Moreover, the TRUNCATE TABLE statement does not fire a trigger because this operation does not log individual row deletions.
       4. If you use the DATABASE option, the scope of your DDL trigger will be the current database. If you use the ALL SERVER option, the scope of your DDL triggers to the current server.
       5. AFTER triggers cannot be defined on views.
       6. AFTER is the default, if FOR is the only keyword specified
          
          
          

          Triggers are special type of stored procedure that automatically execute when a DDL or DML statement associated with the trigger is executed. DML Triggers are used to evaluate data after data manipulation using DML statements. We have two types of DML triggers.

          Types of DML Triggers

          1. After Trigger (using FOR/AFTER CLAUSE)

             This trigger fires after SQL Server completes the execution of the action successfully that fired it.
             Example :If you insert record/row in a table then the trigger associated with the insert event on this table will fire only after the row passes all the checks, such as primary key, rules, and constraints. If the record/row insertion fails, SQL Server will not fire the After Trigger.

          2. Instead of Trigger (using INSTEAD OF CLAUSE)

             This trigger fires before SQL Server starts the execution of the action that fired it. This is much more different from the AFTER trigger, which fires after the action that caused it to fire. We can have an INSTEAD OF insert/update/delete trigger on a table that successfully executed but does not include the actual insert/update/delet to the table.
             Example :If you insert record/row in a table then the trigger associated with the insert event on this table will fire before the row passes all the checks, such as primary key, rules, and constraints. If the record/row insertion fails, SQL Server will fire the Instead of Trigger.

          Example

          
           -- First create table Employee_Demo
          CREATE TABLE Employee_Demo
          (
           Emp_ID int identity,
           Emp_Name varchar(55),
           Emp_Sal decimal (10,2)
          )
          -- Now Insert records 
          Insert into Employee_Demo values ('Amit',1000);
          Insert into Employee_Demo values ('Mohan',1200);
          Insert into Employee_Demo values ('Avin',1100);
          Insert into Employee_Demo values ('Manoj',1300);
          Insert into Employee_Demo values ('Riyaz',1400);
          --Now create table Employee_Demo_Audit for logging/backup purpose of table Employee_Demo create table Employee_Demo_Audit
          (
           Emp_ID int,
           Emp_Name varchar(55),
           Emp_Sal decimal(10,2),
           Audit_Action varchar(100),
           Audit_Timestamp datetime
          ) 
          
          

          Now I am going to explain the use of After Trigger using Insert, Update, Delete statement with example

          1. After Insert Trigger

             
              -- Create trigger on table Employee_Demo for Insert statement
             CREATE TRIGGER trgAfterInsert on Employee_Demo
             FOR INSERT
             AS declare @empid int, @empname varchar(55), @empsal decimal(10,2), @audit_action varchar(100);
             select @empid=i.Emp_ID from inserted i;
             select @empname=i.Emp_Name from inserted i;
             select @empsal=i.Emp_Sal from inserted i;
             set @audit_action='Inserted Record -- After Insert Trigger.'; insert into Employee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
             values (@empid,@empname,@empsal,@audit_action,getdate());
             PRINT 'AFTER INSERT trigger fired.'
             --Output will be 
             
             

             

             
              --Now try to insert data in Employee_Demo table
             insert into Employee_Demo(Emp_Name,Emp_Sal)values ('Shailu',1000);
             --Output will be 
             
             

             

             
              --now select data from both the tables to see trigger action
             select * from Employee_Demo
             select * from Employee_Demo_Audit
             --Output will be 
             
             

             
             Trigger have inserted the new record to Employee_Demo_Audit table for insert statement. In this way we can trace a insert activity on a table using trigger.

          2. After Update Trigger

             
              -- Create trigger on table Employee_Demo for Update statement
             CREATE TRIGGER trgAfterUpdate ON dbo.Employee_Demo
             FOR UPDATE
             AS
             declare @empid int, @empname varchar(55), @empsal decimal(10,2), @audit_action varchar(100);
             select @empid=i.Emp_ID from inserted i; 
             select @empname=i.Emp_Name from inserted i;
             select @empsal=i.Emp_Sal from inserted i; if update(Emp_Name)
              set @audit_action='Update Record --- After Update Trigger.';
             if update (Emp_Sal)
              set @audit_action='Update Record --- After Update Trigger.';
             insert intoEmployee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
             values (@empid,@empname,@empsal,@audit_action,getdate());
             PRINT 'AFTER UPDATE trigger fired.'
             --Output will be 
             
             

             

             
              --Now try to upadte data in Employee_Demo table
             update Employee_Demo set Emp_Name='Pawan' Where Emp_ID =6;
             --Output will be 
             
             

             

             
              --now select data from both the tables to see trigger action
             select * from Employee_Demo
             select * from Employee_Demo_Audit
             --Output will be 
             
             

             
             Trigger have inserted the new record to Employee_Demo_Audit table for update statement. In this way we can trace a update activity on a table using trigger.

          3. After Delete Trigger

             
              -- Create trigger on table Employee_Demo for Delete statement
             CREATE TRIGGER trgAfterDelete ON dbo.Employee_Demo
             FOR DELETE
             AS
             declare @empid int, @empname varchar(55), @empsal decimal(10,2), @audit_action varchar(100); select @empid=d.Emp_ID FROM deleted d;
             select @empname=d.Emp_Name from deleted d;
             select @empsal=d.Emp_Sal from deleted d;
             select @audit_action='Deleted -- After Delete Trigger.';
             insert into Employee_Demo_Audit (Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
             values (@empid,@empname,@empsal,@audit_action,getdate());
             PRINT 'AFTER DELETE TRIGGER fired.'
             --Output will be 
             
             

             

             
              --Now try to delete data in Employee_Demo table
             DELETE FROM Employee_Demo where emp_id = 5
             --Output will be 
             
             

             

             
              --now select data from both the tables to see trigger action
             select * from Employee_Demo
             select * from Employee_Demo_Audit
             --Output will be 
             
             

             
             Trigger have inserted the new record to Employee_Demo_Audit table for delete statement. In this way we can trace a delete activity on a table using trigger.

          Now I am going to explain the use of Instead of Trigger using Insert, Update, Delete statement with example

          1. Instead of Insert Trigger

             
              -- Create trigger on table Employee_Demo for Insert statement
             CREATE TRIGGER trgInsteadOfInsert ON dbo.Employee_Demo
             INSTEAD OF Insert
             AS
             declare @emp_id int, @emp_name varchar(55), @emp_sal decimal(10,2), @audit_action varchar(100);
             select @emp_id=i.Emp_ID from inserted i;
             select @emp_name=i.Emp_Name from inserted i;
             select @emp_sal=i.Emp_Sal from inserted i;
             SET @audit_action='Inserted Record -- Instead Of Insert Trigger.';
             BEGIN 
              BEGIN TRAN
              SET NOCOUNT ON
              if(@emp_sal>=1000)
              begin
              RAISERROR('Cannot Insert where salary < 1000',16,1); ROLLBACK; end
              else begin Insert into Employee_Demo (Emp_Name,Emp_Sal) values (@emp_name,@emp_sal); Insert into Employee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp) values(@@identity,@emp_name,@emp_sal,@audit_action,getdate());
              COMMIT;
              PRINT 'Record Inserted -- Instead Of Insert Trigger.'
             END
             --Output will be 
             
             

             

             
              --Now try to insert data in Employee_Demo table
             insert into Employee_Demo values ('Shailu',1300)
             insert into Employee_Demo values ('Shailu',900) -- It will raise error since we are checking salary >=1000
             --Outputs will be 
             
             

              

             
              --now select data from both the tables to see trigger action
             select * from Employee_Demo
             select * from Employee_Demo_Audit
             --Output will be 
             
             

             
             Trigger have inserted the new record to Employee_Demo_Audit table for insert statement. In this way we can apply business validation on the data to be inserted using Instead of trigger and can also trace a insert activity on a table.

          2. Instead of Update Trigger

             
              -- Create trigger on table Employee_Demo for Update statement
             CREATE TRIGGER trgInsteadOfUpdate ON dbo.Employee_Demo
             INSTEAD OF Update
             AS
             declare @emp_id int, @emp_name varchar(55), @emp_sal decimal(10,2), @audit_action varchar(100);
             select @emp_id=i.Emp_ID from inserted i;
             select @emp_name=i.Emp_Name from inserted i;
             select @emp_sal=i.Emp_Sal from inserted i;
             BEGIN
              BEGIN TRAN
             if(@emp_sal>=1000)
              begin
              RAISERROR('Cannot Insert where salary < 1000',16,1); ROLLBACK; end
              else begin 
              insert into Employee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp) values(@emp_id,@emp_name,@emp_sal,@audit_action,getdate());
              COMMIT;
              PRINT 'Record Updated -- Instead Of Update Trigger.'; END
             --Output will be 
             
             

             

             
              --Now try to upadte data in Employee_Demo table
             update Employee_Demo set Emp_Sal = '1400' where emp_id = 6
             update Employee_Demo set Emp_Sal = '900' where emp_id = 6
             --Output will be 
             
             

              

             
              --now select data from both the tables to see trigger action
             select * from Employee_Demo
             select * from Employee_Demo_Audit
             --Output will be 
             
             

             
             Trigger have inserted the updated record to Employee_Demo_Audit table for update statement. In this way we can apply business validation on the data to be updated using Instead of trigger and can also trace a update activity on a table.

          3. Instead of Delete Trigger

             
              -- Create trigger on table Employee_Demo for Delete statement
             CREATE TRIGGER trgAfterDelete ON dbo.Employee_Demo
             INSTEAD OF DELETE
             AS
             declare @empid int, @empname varchar(55), @empsal decimal(10,2), @audit_action varchar(100); select @empid=d.Emp_ID FROM deleted d;
             select @empname=d.Emp_Name from deleted d;
             select @empsal=d.Emp_Sal from deleted d;
             BEGIN TRAN if(@empsal>1200) begin
              RAISERROR('Cannot delete where salary > 1200',16,1);
              ROLLBACK;
              end
              else begin
              delete from Employee_Demo where Emp_ID=@empid;
              COMMIT;
              insert into Employee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
              values(@empid,@empname,@empsal,'Deleted -- Instead Of Delete Trigger.',getdate());
              PRINT 'Record Deleted -- Instead Of Delete Trigger.' end END
             --Output will be 
             
             

             

             
              --Now try to delete data in Employee_Demo table
             DELETE FROM Employee_Demo where emp_id = 1
             DELETE FROM Employee_Demo where emp_id = 3
             --Output will be 
             
             

              

             
              --now select data from both the tables to see trigger action
             select * from Employee_Demo
             select * from Employee_Demo_Audit
             --Output will be 
             
             

             
             Trigger have inserted the deleted record to Employee_Demo_Audit table for delete statement. In this way we can apply business validation on the data to be deleted using Instead of trigger and can also trace a delete activity on a table.