Basics of Triggers in SQL Server

SQL Triggers:

A trigger is a procedural code like stored procedure that is automatically executed on certain actions (like insert, delete, and update) occurs on table, view or database. CREATE TRIGGER statement is used to create triggers.
A trigger can be fire on following statements

DDL statements like

• drop table, 
• alter table, 
• create table.

DML Statements like

• INSERT, 
• DELETE, and 
• UPDATE.

If you want to see triggers on your database.you can use below query

1. select * from databasename.sys.triggers   

Why use Triggers?
There are several reasons to use triggers.some of them listed below

• Log database activity
• Implement Business Rule
• Enforce referential integrity. Example: When you delete a customer, you can use a trigger to delete corresponding rows in the orders table.
• Triggers can access both old and changed values on insert, delete, update operations.

Syntax to write trigger

1. CREATE TRIGGER trigger_name  
2. ON  
3. table_name | view_name  
4. FOR | AFTER | INSTEAD OF  
5. [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ]  
6.  AS  
7.  BEGIN  
8.  --INSERT INTO dbo.CustomeractivityHistory.......  
9.  --Your action statements.........  
10.  END  

Example:
The following trigger in below example is used to log insert activity of CustomerHistory table.

1. CREATE TRIGGER TRG_CUSTOMER_INSERT  
2.  ON dbo.CustomerDetails  
3. AFTER INSERT  
4. AS  
5. BEGIN  
6.  INSERT INTO dbo.CustomerHistory(UserID, ActionTaken, ActivityDate, Remarks)  SELECT i.CustomerID, 'INSERT', getdate(), 'Customer inserted into table'  FROM  Inserted i  
7. END    

DDL Triggers

DDL triggers are a special kind of trigger that fire in response to Data Definition Language (DDL) statements. They can be used to perform administrative tasks in the database such as auditing and regulating database operations.

Example 1: 
The below trigger in example is used to prevent database from Drop on SERVER.

1. CREATE TRIGGER trg_preventionDrop  
2. ON all SERVER  
3.  FOR DROP_DATABASE  
4. AS  
5. print 'Drop Database not permitted'  
6. ROLLBACK tran  
7. Go   

Example 2:

to prevent table  from Drop on SERVER

1. CREATE TRIGGER trg_preventDropTable  
2.  ON all SERVER  
3. FOR DROP_TABLE  
4. as  
5. print 'Drop table not allowed'  
6. ROLLBACK tran    
7. Go  

DML Triggers

A DML trigger is an action programmed to execute when a data manipulation language (DML) event occurs in the database server. DML events include UPDATE, INSERT, or DELETE statements issued against a table or view. DML triggers are used to enforce business rules when data is modified and to extend the integrity checking logic of Microsoft SQL Server constraints, defaults, and rules.

AFTER Trigger

Example:

1. CREATE TRIGGER TRG_CUSTOMER_INSERT  
2.  ON dbo.CustomerDetails  
3. AFTER INSERT  
4. AS  
5. BEGIN  
6.  INSERT INTO dbo.CustomerHistory(UserID, ActionTaken, ActivityDate, Remarks)  SELECT i.CustomerID, 'INSERT', getdate(), 'Customer inserted into table'  FROM  Inserted i  
7.  END    

INSTEAD OF Trigger

It is like BEFORE trigger in other RDBMS systems.In this case trigger fires before insert/update/delete actions occurred means no constraints checked.

1. CREATE TRIGGER TRG_CUSTOMER_INSERT    
2. ON dbo.CustomerDetails   
3. INSTEAD OF INSERT   
4. AS   
5. BEGIN    
6. INSERT INTO dbo.CustomerHistory(UserID, ActionTaken, ActivityDate, Remarks)  SELECT i.CustomerID, 'INSERT', getdate(), 'Customer inserted into table'  FROM  Inserted i    
7. END  

LOGON Triggers

Logon trigger are special type of trigger that fires on logon events occures in sql server. 

Syntax

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

---

Example 1: Write a Trigger to log activity on Database

Create a table that stores activity on database.

1. CREATE TABLE [dbo].[databaseActivityInfo]( [EVNTYPE] [varchar](100) NULL, [OBJNAME] [varchar](100) NULL, [EVNTDT] [datetime] NULL, [DBVERSION] [varchar](30) NULL ) ON [PRIMARY] GO  

Now write a trigger on database as

1. CREATE TRIGGER [TRG_db_customer]   
2. ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS   
3. AS  
4. DECLARE @v_Evntype VARCHAR(MAX);   
5. DECLARE @v_Objname VARCHAR(MAX);   
6. SELECT @v_Evntype = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)'),  
7.        @v_Objname = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)')   
8.        INSERT INTO databaseActivityInfo(EVNTYPE ,OBJNAME,EVNTDT,DBVERSION) VALUES(@v_Evntype,@v_Objname,GETDATE())   
9.  GO  

Example 2:
Trigger to prevent drop,alter table in database.

1. CREATE TRIGGER TRG_CUSTOMER_ALT_DROP   
2. ON   
3. DATABASE FOR DROP_TABLE, ALTER_TABLE   
4. AS   
5. PRINT 'You must disable Trigger " TRG_CUSTOMER_ALT_DROP " to drop or alter tables!'  
6.   
7. ROLLBACK