Introducing Trigger

A trigger is a block of code that constitutes a set of T-SQL statements activated in response to certain action, such as insert or delete.

Triggers are used to ensure data integrity before or after performing data manipulations.

Types of Triggers in SQL Server

In SQL Server there are various kinds of triggers can be use for different types of data manipulation operations. In sql Server there are two types of triggers:

Data Modification language(DML )Triggers
Data Definition Language(DDL) triggers

DML Triggers

A DML trigger is fired when data in the underlying table is affected by DML statement such as Insert, Update or Delete.
A DML trigger can not explicitly invoked or executed, as in the case of the store procedures.
A DML Trigger cannot return data to the user.

DDL Triggers

A DDL Trigger is fired in response to DDL statement such as Create table or alter table.
A DDL Trigger can we use to perform administrative work such as database auditing.

For example I crate a trigger on employee table of the kvch database.

Create taigger {trgdepartement} on Hr. Department AFTER UPDATE AS
BEGIN
UPDATE Hr.Department set HR. Department.ModifiedDate=getdate() from Inserted where
Inserted.DepartementID=Hr. Department.DepartmentID;
END;

In the above example statement create trigger named trgDepartment that is fired on every successfully executed update statement on the Hr.Department table. The Trigger updates the ModifiedDate column of every updated value with the current date.