DML Triggers in SQL Server

By: Daniel Farina

Overview

In this chapter we will see the most common type of triggers, DML triggers.

DML Triggers

This type of trigger is the most known and used by developers. As you may already know, DML stands for Data Manipulation Language and refers to the SQL instructions that changes data. Those instructions are INSERT, UPDATE and DELETE. Basically, DML triggers can be defined as pieces of code written mostly in Transact SQL language whose execution is not performed manually by the user and instead is run automatically in response to DML events.

DML Triggers are associated to a table or view and to any of the DML events (INSERT, UPDATE and DELETE). Something to remark is that a trigger can only be associated with one single table or view, but can be associated to more than one DML event. For instance, you could have a trigger that is associated with a table that fires in response to INSERT and UPDATE events, we will see this further on in the tutorial.

To create a trigger we need to use the CREATE TRIGGER statement.

In this code section you will see the basic CREATE TRIGGER syntax.

CREATE TRIGGER trigger_name   
ON { Table name or view name }   
{ FOR | AFTER | INSTEAD OF }   
{ [INSERT], [UPDATE] , [DELETE] }   

Additionally the next table describes each of the arguments of the CREATE TRIGGER syntax.

Let's create a sample table, so we can create our first trigger.

USE [SampleDB]
GO

CREATE TABLE Employees(
EmployeeID      INT IDENTITY(1,1) PRIMARY KEY,
EmployeeName    VARCHAR(50) NOT NULL,
EmployeeAddress VARCHAR(50) NOT NULL,
MonthSalary     NUMERIC(10,2) NOT NULL
)
 
INSERT INTO dbo.Employees
(
    EmployeeName,
    EmployeeAddress,
    MonthSalary
)
VALUES
(   'Mark Smith',    
    'Ocean Dr 1234', 
    10000            
    ),
(   'Joe Wright',    
    'Evergreen 1234',
    10000            
),
(   'John Doe',             
    'International Dr 1234',
    10000                   
),
(   'Peter Rodriguez', 
    '74 Street 1234',  
    10000              
);

In the code below you will see the most basic DML trigger we can make, which will return "Hello World". This trigger will show a "Hello World!" line every time a row is added to the Employees table.

USE [SampleDB]
GO
CREATE TRIGGER [dbo].[TR_Employees] ON [dbo].[Employees]
FOR INSERT
AS
SELECT 'Hello World!'
GO

In order to test this trigger we need to insert some data into the Employees table. We can do so with the next script.

SELECT * FROM dbo.Employees
GO

INSERT INTO dbo.Employees ( EmployeeName , EmployeeAddress ,MonthSalary )
VALUES ( 'Paul Martinez' , '22 Street 4217', 4000)
GO

SELECT * FROM dbo.Employees
GO

On the next image you can see the execution of the previous script. Notice that when the INSERT statement executes it prints a "Hello World!" message.

Additional Information

• SQL Server Trigger Alternatives with the OUTPUT Clause