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.
CREATE TRIGGER d2c_trgInsertAuditInfo ON d2c_tblStudents AFTER INSERT
SET NOCOUNT ON;
declare @studentName varchar(100)
select @studentName=tmp.studentName from INSERTED tmp
insert into d2c_tblStudentsAuditInfo
VALUES('New record inserted Student Name: '+@studentName+')
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.
CREATE TRIGGER d2c_trgDatabaseChange ON ALL SERVER FOR CREATE_DATABASE
SET NOCOUNT ON;
PRINT 'Database Created...'
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.
/* Create Audit Database */
CREATE DATABASE d2c_AuditDb
/* Create Audit Table */
CREATE TABLE d2c_ServerLogonLog
(SystemUser VARCHAR(512),DBUser VARCHAR(512),SPID INT,LogonTime DATETIME)
/* Create Logon Trigger */
CREATE TRIGGER d2c_trgServerLogon ON ALL SERVER FOR LOGON
INSERT INTO d2c_AuditDb.dbo.d2c_ServerLogonLog
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.