Triggers Explained in SQL Server

In this blog, I will explain SQL Triggers.

Introduction

 
In this blog, I will explain SQL Triggers. Triggers are stored programs, which are automatically executed or fired when some events occur (insert, update, delete). Triggers are stored in and managed by the DBMS. They are used to maintain the referential integrity of data by changing the data in a systematic fashion. DBMS automatically fires the trigger as a result of a data modification to the associated table. Stored procedures are explicitly executed by invoking a call to the procedure while triggers are implicitly executed. In addition, triggers can also execute stored procedures. Triggers are used in the following events:
  • DML-INSERT, UPDATE, DELETE
  • DDL-CREATE, ALTER, DROP
Types of DML Triggers
  1. Before Triggers
  2. After Triggers
  3. Instead of Triggers
After Triggers
 
After triggers are invoked after DML (insert, update and delete) operations.
  1. After Insert
  2. After Update
  3. After Delete
After Insert
  1. CREATE TRIGGER TRIGGER_NAME on TB_NAME  
  2. FOR INSERT  
  3. AS  
  4. declare @COLUMN1 int, @COLUMN2 varchar(55), @audit_action varchar(100);  
  5.   
  6. select @COLUMN1 =i.TB_Columnname1 from inserted i;  
  7. select @COLUMN2 =i.TB_Columnname2 from inserted i;  
  8.   
  9. set @audit_action='Inserted Record -- After Insert Trigger.';  
  10.   
  11. insert into TB_NAME(TB_Columnname1, TB_Columnname2,Audit_Action)  
  12. values(@COLUMN1@COLUMN2);  
  13. PRINT 'AFTER INSERT trigger fired.'  
After Update
  1. CREATE TRIGGER TRIGGER_NAME ON TB_NAME  
  2. FOR UPDATE  
  3. AS  
  4. Declare @COLUMN1 int, @COLUMN2 varchar(55), @audit_action varchar(100);  
  5. select @COLUMN1 =i.TB_Columnname1 from inserted i;  
  6. select @COLUMN2 =i.TB_Columnname2 from inserted i;  
  7. if update(@COLUMN1)  
  8. set @audit_action='Update Record empname --- After Update Trigger.';  
  9. if update (@COLUMN2)  
  10. set @audit_action='Update Record emp sal --- After Update Trigger.';  
  11. insert into TB_NAME(TB_Columnname1, TB_Columnname2,Audit_Action)  
  12. values(@COLUMN1@COLUMN2);  
  13. PRINT 'AFTER UPDATE trigger fired.' 
After Delete
  1. CREATE TRIGGER TRIGGER_NAME ON TB_NAME  
  2. FOR DELETE  
  3. AS  
  4. Declare @COLUMN1 int, @COLUMN2 varchar(55), @audit_action varchar(100);  
  5. Select @COLUMN1 =i.TB_Columnname1 FROM deleted d;  
  6. Select @COLUMN2 =i.TB_Columnname2 from deleted d;  
  7. select @audit_action='Deleted -- After Delete Trigger.';  
  8. insert into TB_NAME(TB_Columnname1, TB_Columnname2,Audit_Action)  
  9. values(@COLUMN1@COLUMN2);  
  10. PRINT 'AFTER DELETE TRIGGER fired.'  
Benefits
  1. Generating some derived column values automatically.
  2. Enforcing referential integrity.
  3. Event logging and storing information on table access.
  4. Synchronous replication of tables.
  5. Imposing security authorizations.