T-SQL Tutorial

T-SQL DML triggers

SQL Server DML (Data Manipulation Language) triggers are special types of triggers that are activated in response to certain Data Manipulation Language (DML) events such as INSERT, UPDATE, and DELETE statements on a specific table or view.

DML triggers can be used to enforce business rules and maintain data integrity, as well as to audit and track changes to the data in a table. They can also be used to prevent unauthorized changes to the data by disabling certain DDL statements for specific users or roles.

DML triggers are created using the CREATE TRIGGER statement, and are executed in response to a DML event within the context of the current session. The trigger can be defined to fire either before or after the DML event, or both.

The triggers that fire before the DML event, known as FOR or INSTEAD OF triggers, can be used to validate data or change the data before it is committed to the database. The triggers that fire after the DML event, known as AFTER triggers, can be used to update related tables, perform additional data validation, or log the changes.

Here is an example of a DML trigger in SQL Server that tracks changes to a table called "Employees":

CREATE TRIGGER Employee_Update_Log
ON Employees
INSERT INTO Employee_Log (EmployeeID, LastName, FirstName, UpdateDate)
SELECT EmployeeID, LastName, FirstName, GETDATE()
FROM inserted;

This trigger is activated AFTER UPDATE on the "Employees" table. It will insert a new record into the "Employee_Log" table with the EmployeeID, LastName, FirstName, and current date and time every time a record in the "Employees" table is updated.

inserted is a special table that contains the new data after the update.
GETDATE() is a SQL Server function that returns the current date and time.
SET NOCOUNT ON; is used to prevent the trigger from returning the number of rows affected by the INSERT statement.