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;