Triggers in SQL Server

A trigger in SQL Server is a special kind of stored procedure that automatically executes when an event occurs in the database server. There are following types of triggers available in SQL Server,

  • DML Triggers
  • DDL Triggers
  • Logon Triggers

DML Triggers: DML Triggers get executed when a user tries to modify data through data manipulation language (DML) like INSERT, UPDATE, or DELETE statements on a table or view. DML triggers fire when any valid event is performed, regardless of whether or not any table rows are affected.

Example: 

CREATE TRIGGER d2c_trgInsertAuditInfo ON d2c_tblStudents AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
declare @studentName varchar(100)
select @studentName=tmp.studentName from INSERTED tmp
insert into d2c_tblStudentsAuditInfo
(auditInfo)
VALUES('New record inserted Student Name: '+@studentName+')
END

Explanation:
In above example we have created d2c_trgInsertAuditInfo trigger on d2c_tblStudents table.
Whenever any new record will insert into d2c_tblStudents table, d2c_trgInsertAuditInfo trigger
will fire and mentioned query will execute.

DDL Triggers:DDL triggers fire in response to a variety of Data Definition Language (DDL) events. These events primarily correspond to Transact-SQL statements that start with the keywords CREATE, ALTER, DROP, GRANT, DENY, REVOKE or UPDATE STATISTICS.Real word use of DDL Triggers is to track database changes or all database in server.

Example:

CREATE TRIGGER d2c_trgDatabaseChange ON ALL SERVER FOR CREATE_DATABASE
AS
BEGIN
SET NOCOUNT ON;
PRINT 'Database Created...'
END

Explanation:
In above example we have created d2c_trgDatabaseChange trigger on all server for creation of new database.Whenever any new database will create in server, d2c_trgDatabaseChange trigger will fire and mentioned query will execute.

Logon Triggers: Logon Triggers get executed in response to the LOGON event that is raised when a user sessions is being established. These are useful in cases where we want to track which all users are connecting to the SQL Server instance and and write that information in to a table which can be later used to review. General use is for auditing purposes and sometimes to prevent actions when login happens from a suspicious computer.

Example:

/* Create Audit Database */
CREATE DATABASE d2c_AuditDb
GO
USE d2c_AuditDb
GO
/* Create Audit Table */
CREATE TABLE d2c_ServerLogonLog
(SystemUser VARCHAR(512),DBUser VARCHAR(512),SPID INT,LogonTime DATETIME)
GO
/* Create Logon Trigger */
CREATE TRIGGER d2c_trgServerLogon ON ALL SERVER FOR LOGON
AS
BEGIN
INSERT INTO d2c_AuditDb.dbo.d2c_ServerLogonLog
SELECT SYSTEM_USER,USER,@@SPID,GETDATE()
END
GO

Explanation:
In above example we have created d2c_trgServerLogon trigger on all server for LOGON.
Whenever any new session will created d2c_trgServerLogon trigger will fire and mentioned query will execute.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s