Hands-on Learning Event in Honolulu 1 : Introduction to Structured Query Language

A new session is going to start soon. Sign-up here.

Microsoft SQL Azure Database: SQL Server in Microsoft Cloud
Windows PowerShell for SQL Server

My sincere thanks and best wishes to my students in the previous meets of this course.

How do you create a data manipulation language trigger?

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.

What is a trigger in SQL Server?

A trigger is an event or an action that precipitates other events. However a trigger in SQL Server is a special kind of stored procedure that automatically executes when an event occurs in the database server. 

If you want to know what is a stored procedure, follow this link:
http://hodentekhelp.blogspot.com/2011/06/what-is-stored-procedure.html

There are different kinds of events that can occur in a database server such as the following:

• Data Manipulation language events such as,

Insert

Update

Delete

statements on a Table or View.

In these cases if a trigger is defined, it will fire (respond to the event) regardless of any changes are made to the rows of a table.

• Data Definition Language events such as,

Create

Alter

Drop

and certain stored procedures that perform DDL-like operations

• Log-on trigger

These fire when a session is established by a user.