Creating a simple DML trigger

An action is taken when a trigger is fired. DML stands for Data Manipulation Language and DML Triggers are initiated automatically whenever a DML event occurs.

DML events include events that takes place in a Table or View and they are:

INSERT
DELETE
UPDATE statements.

Why do you need a DML Trigger?

In order to enforce business rules in the following cases/situations:

Enforce Data Integrity
Querying other tables
Auditing data changes

These can include complex Transact SQL statements.

As triggers are created for (on) a table or view, a 'Triggers' node exist for all tables in a database.

Presently there are no triggers set up for the Person.PersonPhone table in the AdventureWorks 2014 database as its Triggers node is empty as shown.

DML_00

In order to create a trigger you need to provide a name, and the SQL Statement that starts the trigger; and the table on which the trigger will fire. Execute the following statement which creates a trigger called PlNotify that triggers whenever a Insert or an Update statement is run:

CREATE TRIGGER PlNotify 
ON Person.PersonPhone
AFTER INSERT, UPDATE  
AS RAISERROR ('Notify Customer Relations', 16, 10); 
GO

When the trigger is created it gets into the Triggers node as shown.

DML_01

Let us see what happens when we try to update a phone number. Here are some of the phone numbers (only 5 from top is shown):

DML_02

Now let us update a phone number in the above table using the following:

UPDATE dbo.Person.PersonPhone
SET PhoneNumer='808-722-6655'
WHERE BusinessEntityID=1
--Print 'After update trigger fired'

The PhoneNumber of Business Entity with BusinessEntityID was 697-555-0142 before the trigger fired.

And after the above update statement is run it becomes 808-722-6655 and the trigger is completed.
With the following response:

"Msg 50000, Level 16, State 10, Procedure PlNotify,
Line 4 [Batch Start Line 8]
Notify Customer Relations(1 row(s) affected)
After update trigger fired"

The trigger fires after the update and the changed table is as shown:

DML_03

While this is a very simple example of creating a data manipulation trigger there is a lot things happening in the database. Two temporary, memory resident tables- inserted and deleted are created to take stock of the changes being made.

Also only the After Insert ,Update trigger was considered and there are other types of triggers as well.