TABLOCK hint in an Update SQL Query

TABLOCK is one of several table hints in SQL Server which includes the following:

   FORCESCAN 
   FORCESEEK 
   HOLDLOCK  
   NOLOCK  
   NOWAIT 
   PAGLOCK  
   READCOMMITTED  
   READCOMMITTEDLOCK  
   READPAST  
   READUNCOMMITTED  
   REPEATABLEREAD  
   ROWLOCK  
   SERIALIZABLE  
   SNAPSHOT  
   SPATIAL_WINDOW_MAX_CELLS = integer 
   TABLOCK  
   TABLOCKX  
   UPDLOCK  
   XLOCK 

Table hints can be used with any of the following operations:
Applies to:
DELETE
INSERT
SELECT
UPDATE
MERGE

Here is a query that updates a value in the Northwind Shippers table:
----------
Use Northwind
GO
UPDATE  Shippers
WITH (TABLOCK)
SET CompanyName='Speedy Gonsalez'
WHERE ShipperID=1
--
SELECT * from Shippers
------------
The query updates the name of the shipping company whose ID=1 to a new name. When TABLOCK is used in the above manner it should be within the parenthesis.

Read this recommendation from Microsoft:

"An UPDATE statement always acquires an exclusive (X) lock on the table it modifies, and holds that lock until the transaction completes. With an exclusive lock, no other transactions can modify data. You can specify table hints to override this default behavior for the duration of the UPDATE statement by specifying another locking method, however, we recommend that hints be used only as a last resort by experienced developers and database administrators"

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.