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
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
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
SET NOCOUNT ON;
INSERT INTO Employee_Log (EmployeeID, LastName, FirstName, UpdateDate)
SELECT EmployeeID, LastName, FirstName, GETDATE()
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.