This article gives introduction to Triggers in Sql Server. This is the first article in the series of articles on Triggers in Sql Server. Below is the complete list of articles in this series. Articles in this series of articles on Trigger explain the various types of triggers with extensive list of easy to understand simple examples

• Data Manipulation Language (DML) Triggers
• INSERTED and DELETED Logical Tables
• Data Definition Language (DDL) Triggers
• LOGON Triggers

What is Trigger?

Trigger is a Special kind of Stored Procedure or an operation that gets executed automatically when an event occurs in the database.

Types of Triggers

Following are different types of triggers in Sql Server

• Data Manipulation Language (DML) Triggers

  DML triggers are executed when a DML operation like INSERT, UPDATE OR DELETE is fired on a Table or View.

  DML Triggers are of the following two types
  AFTER Triggers

  AFTER Triggers are executed after the DML statement completes but before it is committed to the database. AFTER Triggers if required can rollback it’s actions and source DML statement which invoked it.

  INSTEAD OF Triggers

  INSTEAD OF Triggers are the triggers which gets executed automatically in place of triggering DML (i.e. INSERT, UPDATE and DELETE) action. It means if we are inserting a record and we have a INSTEAD OF trigger for INSERT then instead of INSERT whatever action is defined in the trigger that gets executed.

• Data Definition Language (DDL) Triggers

  DDL Triggers are executed when a DDL Statements like CREATE, ALTER, DROP, GRANT, DENY, REVOKE, and UPDATE STATISTICS statements are executed. Certain system stored procedures that perform DDL like operations can also fire DDL triggers.

  DDL triggers can be DATABASE scoped or SERVER scoped. The DDL triggers with Server level scope gets fired in response to a DDL statement with server scope like CREATE DATABASE, CREATE LOGIN, GRANT_SERVER, ALTER DATABASE, ALTER LOGIN etc. Where as DATABASE scoped DDL triggers fire in response to DDL statement with DATABASE SCOPE like CREATE TABLE, CREATE PROCEDURE, CREATE FUNCTION, ALTER TABLE, ALTER PROCEDURE, ALTER FUNCTION etc.

• LOGON Triggers

  Logon Triggers gets executed automatically in response to a LOGON event. They get executed only after the successful authentication but before the user session is established. If authentication fails the logon triggers will not be fired.

• CLR Triggers

  CLR Triggers are based on the Sql CLR. We can write DML and DDL triggers by using the Supported .NET CLR languages like C#, VB.NET etc. CLR triggers will useful if require heavy computation in the trigger or require reference to object outside SQL.

[ALSO READ]
Data Manipulation Language (DML) Triggers
Data Definition Language (DDL) Triggers
INSERTED and DELETED Logical Tables
LOGON Triggers