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

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

CREATE TRIGGER trigger_name
ON
table_name | view_name
FOR | AFTER | INSTEAD OF
[ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ]
 AS
 BEGIN
 --INSERT INTO dbo.CustomeractivityHistory.......
 --Your action statements.........
 END

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

CREATE TRIGGER TRG_CUSTOMER_INSERT
 ON dbo.CustomerDetails
AFTER INSERT
AS
BEGIN
 INSERT INTO dbo.CustomerHistory(UserID, ActionTaken, ActivityDate, Remarks)  SELECT i.CustomerID, 'INSERT', getdate(), 'Customer inserted into table'  FROM  Inserted i
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.

CREATE TRIGGER trg_preventionDrop
ON all SERVER
 FOR DROP_DATABASE
AS
print 'Drop Database not permitted'
ROLLBACK tran
Go 

Example 2:

to prevent table  from Drop on SERVER

CREATE TRIGGER trg_preventDropTable
 ON all SERVER
FOR DROP_TABLE
as
print 'Drop table not allowed'
ROLLBACK tran  
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:

CREATE TRIGGER TRG_CUSTOMER_INSERT
 ON dbo.CustomerDetails
AFTER INSERT
AS
BEGIN
 INSERT INTO dbo.CustomerHistory(UserID, ActionTaken, ActivityDate, Remarks)  SELECT i.CustomerID, 'INSERT', getdate(), 'Customer inserted into table'  FROM  Inserted i
 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.

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

LOGON Triggers

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

Syntax

CREATE TRIGGER trigger_logon_name 
 ON
 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.

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

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

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

CREATE TRIGGER TRG_CUSTOMER_ALT_DROP 
ON 
DATABASE FOR DROP_TABLE, ALTER_TABLE 
AS 
PRINT 'You must disable Trigger " TRG_CUSTOMER_ALT_DROP " to drop or alter tables!'

ROLLBACK

Further Readings:
MSDN





Prev--->Functions in T-SQL                                                 Next---> Basics of Indexes in SQL Server