Introduction to Triggers in SQL Server

SQLServerF1

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 different types of triggers available in SQL Server which include DML triggers, DDL triggers , or logon triggers. In SQL Server, Triggers can be created directly from Transact-SQL (T-SQL) statements or from methods of assemblies that are created in the Microsoft .NET Framework common language runtime (CLR) and uploaded to an instance of SQL Server. SQL Server allows for creating multiple number triggers for any specific statement.

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. DML triggers are mostly used for enforcing business rules and data integrity. If constraints exist on the trigger table, they are checked after the INSTEAD OF trigger execution and before the AFTER trigger execution.  If the constraints are violated, the INSTEAD OF trigger actions are rolled back and the AFTER trigger is not fired.

Example:

CREATE TRIGGER TestTrig
ON Sales.Customer
AFTER INSERT, UPDATE
AS RAISERROR (‘Customer Relations’, 16, 10);

DDL Triggers get executed in response to data definition language (DDL) events. These DDL events primarily correspond to Transact-SQL CREATE, ALTER, and DROP statements, and certain system stored procedures that perform DDL-like operations. DDL triggers do not fire in response to events that affect local or global temporary tables and stored procedures. DDL triggers are not scoped to schemas. Therefore, functions such as OBJECT_ID, OBJECT_NAME, OBJECTPROPERTY, and OBJECTPROPERTYEX cannot be used for querying metadata about DDL triggers.

Example:

CREATE TRIGGER DDLTrig
ON ALL SERVER
FOR CREATE_DATABASE
AS
PRINT ‘Database Created.’
GO

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 you 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. Logon triggers execute stored procedures in response to a LOGON event. This event is raised when a user session is established with an instance of SQL Server. Logon triggers fire after the authentication phase of logging in finishes, but before the user session is actually established. Therefore, all messages originating inside the trigger that would typically reach the user, such as error messages and messages from the PRINT statement, are diverted to the SQL Server error log.

Example:

CREATE TRIGGER LoginTrig
ON ALL SERVER WITH EXECUTE AS ‘testlogin’
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= ‘testlogin’ AND
(SELECT COUNT(*) FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND
original_login_name = ‘login_test’) > 3
ROLLBACK;
END;

 

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Leave a Reply

Your email address will not be published. Required fields are marked *