Triggers are an essential feature provided by SQL Server to ensure data integrity and enforce business rules. They are special stored procedures that are automatically executed when specific events occur on a table. In this article, we will focus on DML triggers, which are triggered by INSERT, UPDATE, and DELETE operations.

Types of Triggers

SQL Server includes three general types of triggers: DML trigger, DDL trigger, and logon trigger. DML triggers are specifically designed to handle data manipulation events such as INSERT, UPDATE, and DELETE operations.

Using DML Triggers

DML triggers are useful in various scenarios, including:

• Cascading changes through related tables in the database.
• Preventing malicious or erroneous insert, update, and delete operations.
• Enforcing restrictions that are more complex than those defined by check constraints.
• Evaluating the status of the table before and after a data modification and taking appropriate measures.

Working with DML Triggers

DML triggers use two special tables: the inserted and deleted tables. The inserted table stores copies of the new or changed rows after an INSERT or UPDATE statement, while the deleted table stores copies of the affected rows before they were changed by a DELETE or UPDATE statement.

Let’s consider an example to understand how DML triggers work:

CREATE TABLE [dbo].[Person](
[ID] [int] NOT NULL,
[Name] [varchar](50) NULL,
[Age] [int] NULL,
 CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED 
(
[ID] ASC
)
) ON [PRIMARY]

CREATE TABLE [dbo].[PersonLog](
[PersonID] [int] NOT NULL,
[Name] [varchar](50) NULL,
[Age] [int] NULL,
[AddDate] [datetime] NULL,
[UpdateDate] [datetime] NULL,
[DeleteDate] [datetime] NULL
) ON [PRIMARY]

In this example, we have a table called “Person” to store data and a table called “PersonLog” to store information about insert, update, and delete operations on the “Person” table.

Insert Operation Trigger

Let’s create a trigger for the INSERT operation:

CREATE TRIGGER [dbo].[Trigger_Insert]
   ON  [dbo].[Person]
   AFTER INSERT
AS 
BEGIN
    SET NOCOUNT ON;
    DECLARE @age int;
    SELECT @age = Age FROM inserted;
    
    -- If the age is less than 130, perform the insertion
    IF (@age < 130)
    BEGIN
        INSERT INTO PersonLog(PersonID, Name, Age, AddDate)
        SELECT ID, Name, Age, GETDATE() FROM inserted;
    END
    ELSE
    BEGIN
        PRINT('The age should be less than 130');
        ROLLBACK TRANSACTION; -- Rollback the transaction if the age is greater than or equal to 130
    END
END

In this trigger, we check the age value of the inserted row. If the age is less than 130, we insert the data into the “PersonLog” table along with the current date and time. Otherwise, we rollback the transaction and display an error message.

Update Operation Trigger

Let’s create a trigger for the UPDATE operation:

CREATE TRIGGER [dbo].[Trigger_Update]
   ON  [dbo].[Person]
   AFTER UPDATE
AS 
BEGIN
    SET NOCOUNT ON;
    SELECT PersonID, AddDate INTO #pel FROM PersonLog;
    DELETE FROM [PersonLog] WHERE [PersonID] IN (SELECT ID FROM inserted);
    
    INSERT INTO [PersonLog] ([PersonID], [Name], [Age], [AddDate], [UpdateDate])
    SELECT [ID], [Name], [Age], #pel.AddDate, GETDATE() FROM inserted
    LEFT JOIN #pel ON inserted.[ID] = #pel.PersonID;
    
    DROP TABLE #pel;
END

In this trigger, we temporarily store the existing rows in the “PersonLog” table according to the updated rows in the “Person” table. We then remove the corresponding rows from the “PersonLog” table. Finally, we insert the updated rows along with the previous add date and the current update date into the “PersonLog” table.

Delete Operation Trigger

Let’s create a trigger for the DELETE operation:

CREATE TRIGGER [dbo].[Trigger_Delete]
   ON  [dbo].[Person]
   AFTER DELETE
AS 
BEGIN
    SET NOCOUNT ON;
    SELECT PersonID, AddDate, UpdateDate INTO #pel FROM PersonLog;
    DELETE FROM [PersonLog] WHERE [PersonID] IN (SELECT ID FROM deleted);
    
    INSERT INTO [PersonLog] ([PersonID], [Name], [Age], [AddDate], [UpdateDate], [DeleteDate])
    SELECT [ID], [Name], [Age], #pel.AddDate, #pel.UpdateDate, GETDATE() FROM deleted
    LEFT JOIN #pel ON deleted.[ID] = #pel.PersonID;
    
    DROP TABLE #pel;
END

In this trigger, we temporarily store the existing rows in the “PersonLog” table according to the deleted rows in the “Person” table. We then remove the corresponding rows from the “PersonLog” table. Finally, we insert the operation date as the delete date along with the previous add date and update date into the “PersonLog” table.

Conclusion

DML triggers are powerful tools in SQL Server that allow you to enforce data integrity and implement complex business rules. However, it is important to be cautious when using triggers, as they can impact performance and introduce complexity. By understanding the concepts and best practices of DML triggers, you can effectively utilize them to maintain data integrity in your SQL Server databases.