Execution Order of Triggers In SQL

Triggers are stored programs that are automatically executed or fired when a specified event occurs. It is a database object that is bound to a table and is executed automatically. We cannot call triggers explicitly. Triggers provide data integrity and are used to access and check data before and after modification using DDL or DML queries.

Triggers are used mainly in the following events:

1. Insert Data into table
2. Delete data from table
3. Update table record

We can create more than one trigger for the same event (in other words an INSERT, DELETE, UPDATE transaction). These is one problem, however. Triggers don't have a specified execution order. Execution of triggers are performed randomly. Sometimes the business logic dictates that we need to define two triggers on a table that must fire in a specific order on the same table action. For example when we insert rows in a table (INSERT statement) two triggers must fire and the second must fire after the first one for our logic to be implemented correctly.

Today we learn how to define the execution order of triggers.

First we create a table as in the following:

1. GO  
2.   
3. CREATE TABLE [dbo].[Employee](  
4.     [Emp_ID] [int] NOT NULL,  
5.     [Emp_Name] [nvarchar](50) NOT NULL,  
6.     [Emp_Salary] [int] NOT NULL,  
7.     [Emp_City] [nvarchar](50) NOT NULL,  
8.  CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED   
9. (  
10.     [Emp_ID] ASC  
11. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
12. ) ON [PRIMARY]  
13.   
14. GO  

Now insert some values into the table.

1. Insert into Employee  
2. Select 1,'Pankaj',25000,'Alwar' Union All  
3. Select 2,'Rahul',26000,'Alwar' Union All  
4. Select 3,'Sandeep',25000,'Alwar' Union All  
5. Select 4,'Sanjeev',24000,'Alwar' Union All  
6. Select 5,'Neeraj',28000,'Alwar' Union All  
7. Select 6,'Naru',20000,'Alwar' Union All  
8. Select 7,'Omi',23000,'Alwar'   

Select all the values from the table.



Now we create two triggers for the insert event.

Example 1

1. CREATE TRIGGER TRIGGER_SECOND  
2. ON Employee  
3. AFTER INSERT  
4.   
5. AS  
6. BEGIN  
7.   
8.     PRINT ' MY EXECUTE ORDER IS SECOND'  
9. END  

Now create a another trigger.

1. CREATE  TRIGGER TRIGGER_FIRST  
2. ON Employee  
3. AFTER INSERT  
4.   
5. AS  
6. BEGIN  
7.   
8.     PRINT ' MY EXECUTE ORDER IS FIRST'  
9. END  

Now we insert data into the employee table.

1. INSERT INTO Employee VALUES(11,'DIV',24000,'JAIPUR')  

Output 

MY EXECUTE ORDER IS SECOND
MY EXECUTE ORDER IS FIRST

(1 row(s) affected)

We can see that the order of execution of the triggers may depend upon the order of their creation. By default, multiple triggers on a SQL Server table for the same action are not fired in a guaranteed order.

Now we learn how to define the execution order of triggers.

SQL Server contains a sp_settriggerorder Stored Procedure for defining the execution orders of triggers.

Syntax of sp_settriggerorder

1. sp_settriggerorder [ @triggername = ] ‘[ triggerschema. ] triggername‘  
2. , [ @order = ] ‘value‘  
3. , [ @stmttype = ] ‘statement_type‘  
4. [ , [ @namespace = ] { ‘DATABASE’ | ‘SERVER’ | NULL } ]   

A brief explanation of the arguments follows.

[ @triggername= ] '[ triggerschema.] triggername' : It defines the trigger name and schema name to which it belongs.

@order: defines the execution order of a trigger. The value is a varchar(10) and it can be any one of the following values.
 

Value	Order
First	Execute order is first
Last	Execution order is last
None	Execution order is undefined

@stmttype: defines the type of trigger, whether insert, delete or update trigger, LOGON, or any Transact-SQL statement event listed in DDL Events. 

@namespace: SQL Server 2005 specific and indicates whether a DDL trigger was created on the database or on the server. If set to NULL, it indicates that the trigger is a DML trigger.

Now to see some examples.

Example 2
In the preceding example we create two triggers, TRIGGER_FIRST and TRIGGER_SECOND. Now we define the order of both triggers.

First we set the order of TRIGGER_FIRST.

1. EXEC sys.sp_settriggerorder @triggername = 'TRIGGER_FIRST',  
2.    @order = 'FIRST',  
3.    @stmttype = 'INSERT',  
4.    @namespace = NULL  

Now we set the order of TRIGGER_SECOND.

1. EXEC sys.sp_settriggerorder @triggername = 'TRIGGER_SECOND',  
2.    @order = 'LAST',  
3.    @stmttype = 'INSERT',  
4.    @namespace = NULL  

After defining the order of execution now we insert some data into the table and examine the result.

1. INSERT INTO Employee  
2.     VALUES (10, 'DEV', 25000, 'JAIPUR')  

Output

MY EXECUTE ORDER IS FIRST
MY EXECUTE ORDER IS SECOND

(1 row(s) affected)

As we expect, trigger TRIGGER_FIRST executes first then trigger TRIGGER_SECOND executes.

Example 3

Now we create 2 more triggers and define their order.

1. CREATE  TRIGGER TRIGGER_FOURTH  
2. ON Employee  
3. AFTER INSERT  
4.   
5. AS  
6. BEGIN  
7.   
8.     PRINT ' MY EXECUTE ORDER IS FOURTH'  
9. END  

And

1. CREATE  TRIGGER TRIGGER_THIRD  
2. ON Employee  
3. AFTER INSERT  
4.   
5. AS  
6. BEGIN  
7.   
8.     PRINT ' MY EXECUTE ORDER IS THIRD'  
9. END  

Now we define the orders of these two triggers.

1. EXEC sys.sp_settriggerorder @triggername = 'TRIGGER_FOURTH',  
2.    @order = 'NONE',  
3.    @stmttype = 'INSERT',  
4.    @namespace = NULL  
5.   
6. EXEC sys.sp_settriggerorder @triggername = 'TRIGGER_THIRD',  
7.    @order = 'NONE',  
8.    @stmttype = 'INSERT',  
9.    @namespace = NULL  

Now insert some data into the table and examine the result.

1. INSERT INTO Employee  
2. VALUES (10, 'DEV', 25000, 'JAIPUR')  

Output

MY EXECUTE ORDER IS FIRST
MY EXECUTE ORDER IS FOURTH
MY EXECUTE ORDER IS THIRD
MY EXECUTE ORDER IS SECOND

We can see that order of TRIGGER_FIRST and TRIGGER_LAST is define but order of TRIGGER_THIRD and TRIGGER_FOURTH is not define so these both trigger execute in random order b/w TRIGGER_FIRST and TRIGGER_SECOND.

Example 4

Let us see another example.

1. EXEC sys.sp_settriggerorder @triggername = 'TRIGGER_FIRST',  
2.      @order = 'FIRST',  
3.      @stmttype = 'INSERT',  
4.      @namespace = NULL  
5.   
6. EXEC sys.sp_settriggerorder @triggername = 'TRIGGER_THIRD',  
7.      @order = 'FIRST',  
8.      @stmttype = 'INSERT',  
9.      @namespace = NULL  

Output

 

Msg 15130, Level 16, State 1, Procedure sp_settriggerorder, Line 163
There already exists a 'FIRST' trigger for 'INSERT'.
 

When we run the preceding query SQL Server throws an error because we can't provide FIRST and LAST order to more than one trigger. If a first trigger is already defined on the table, database, or server, we cannot designate a new trigger as first for the same table, database, or server for the same statement type. This restriction also applies to last triggers.

Example 5

1. SELECT  
2.     sys.TABLES.name,  
3.     sys.TRIGGERS.name,  
4.     sys.TRIGGER_EVENTS.type,  
5.     sys.TRIGGER_EVENTS.TYPE_DESC,  
6.     IS_FIRST,  
7.     IS_LAST,  
8.     sys.TRIGGERS.CREATE_DATE,  
9.     sys.TRIGGERS.MODIFY_DATE  
10. FROM sys.TRIGGERS  
11. INNER JOIN sys.TRIGGER_EVENTS  
12.     ON sys.TRIGGER_EVENTS.object_id = sys.TRIGGERS.object_id  
13. INNER JOIN sys.TABLES  
14.     ON sys.TABLES.object_id = sys.TRIGGERS.PARENT_ID  
15. ORDER BY MODIFY_DATE  

Output

SQL SERVER – Fragmentation – Detect Fragmentation and Eliminate Fragmentation

Q. What is Fragmentation? How to detect fragmentation and how to eliminate it?

A. Storing data non-contiguously on disk is known as fragmentation. Before learning to eliminate fragmentation, you should have a clear understanding of the types of fragmentation. We can classify fragmentation into two types:

• Internal Fragmentation: When records are stored non-contiguously inside the page, then it is called internal fragmentation. In other words, internal fragmentation is said to occur if there is unused space between records in a page. This fragmentation occurs through the process of data modifications (INSERT, UPDATE, and DELETE statements) that are made against the table and therefore, to the indexes defined on the table. As these modifications are not equally distributed among the rows of the table and indexes, the fullness of each page can vary over time. This unused space causes poor cache utilization and more I/O, which ultimately leads to poor query performance.
• External Fragmentation: When on disk, the physical storage of pages and extents is not contiguous. When the extents of a table are not physically stored contiguously on disk, switching from one extent to another causes higher disk rotations, and this is called Extent Fragmentation.

Index pages also maintain a logical order of pages inside the extent. Every index page is linked with previous and next page in the logical order of column data. However, because of Page Split, the pages turn into out-of-order pages. An out-of-order page is a page for which the next physical page allocated to the index is not the page pointed to by the next-pagepointer in the current leaf page. This is called Logical Fragmentation.

Ideal non-fragmented pages are given below:

Statistics for table scan are as follows:

• Page read requests: 2
• Extent switches: 0
• Disk space used by table: 16 KB
• avg_fragmentation_in_percent: 0
• avg_page_space_used_in_percent: 100

Following are fragmented pages:

In this case, the statistics for table scan are as follows:

• Page read requests: 6
• Extent switches: 5
• Disk space used by table: 48 KB
• avg_fragmentation_in_percent > 80
• avg_page_space_used_in_percent: 33

How to detect Fragmentation: We can get both types of fragmentation using the DMV: sys.dm_db_index_physical_stats. For the screenshot given below, the query is as follows:

SELECT OBJECT_NAME(OBJECT_ID), index_id,index_type_desc,index_level,avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_countFROM sys.dm_db_index_physical_stats(DB_ID(N'AdventureWorksLT'), NULL, NULL, NULL , 'SAMPLED')ORDER BY avg_fragmentation_in_percent DESC

Along with other information, there are two important columns that for detecting fragmentation, which are as follows:

• avg_fragmentation_in_percent: This is a percentage value that represents external fragmentation. For a clustered table and leaf level of index pages, this is Logical fragmentation, while for heap, this is Extent fragmentation. The lower this value, the better it is. If this value is higher than 10%, some corrective action should be taken.
• avg_page_space_used_in_percent: This is an average percentage use of pages that represents to internal fragmentation. Higher the value, the better it is. If this value is lower than 75%, some corrective action should be taken.

Reducing fragmentation:

• Reducing Fragmentation in a Heap: To reduce the fragmentation of a heap, create a clustered index on the table. Creating the clustered index, rearrange the records in an order, and then place the pages contiguously on disk.
• Reducing Fragmentation in an Index: There are three choices for reducing fragmentation, and we can choose one according to the percentage of fragmentation:
  • If avg_fragmentation_in_percent > 5% and < 30%, then use ALTER INDEXREORGANIZE: This statement is replacement for DBCC INDEXDEFRAG to reorder the leaf level pages of the index in a logical order. As this is an online operation, the index is available while the statement is running.
  • If avg_fragmentation_in_percent > 30%, then use ALTER INDEX REBUILD: This is replacement for DBCC DBREINDEX to rebuild the index online or offline. In such case, we can also use the drop and re-create index method.
  • (Update: Please note this option is strongly NOT recommended)Drop and re-create the clustered index: Re-creating a clustered index redistributes the data and results in full data pages. The level of fullness can be configured by using the FILLFACTOR option in CREATE INDEX.

SQL SERVER – Difference Between Index Rebuild and Index Reorganize Explained with T-SQL Script

Index Rebuild : This process drops the existing Index and Recreates the index.
USE AdventureWorks;GOALTER INDEX ALL ON Production.Product REBUILD
GO
Index Reorganize : This process physically reorganizes the leaf nodes of the index.
USE AdventureWorks;GOALTER INDEX ALL ON Production.Product REORGANIZE
GO
Recommendation: Index should be rebuild when index fragmentation is great than 40%. Index should be reorganized when index fragmentation is between 10% to 40%. Index rebuilding process uses more CPU and it locks the database resources. SQL Server development version and Enterprise version has option ONLINE, which can be turned on when Index is rebuilt. ONLINE option will keep index available during the rebuilding.

CONTAINS is a predicate used in the WHERE clause of a Transact-SQL

CONTAINS (Transact-SQL)

Other Versions

Searches for precise or fuzzy (less precise) matches to single words and phrases, words within a certain distance of one another, or weighted matches in SQL Server. CONTAINS is a predicate used in the WHERE clause of a Transact-SQL SELECT statement to perform SQL Server full-text search on full-text indexed columns containing character-based data types.

CONTAINS can search for:

• A word or phrase.
• The prefix of a word or phrase.
• A word near another word.
• A word inflectionally generated from another (for example, the word drive is the inflectional stem of drives, drove, driving, and driven).
• A word that is a synonym of another word using a thesaurus (for example, the word "metal" can have synonyms such as "aluminum" and "steel").

For information about the forms of full-text searches that are supported by SQL Server, see Query with Full-Text Search.

Applies to: SQL Server (SQL Server 2008 through current version).

 Transact-SQL Syntax Conventions

Syntax

CONTAINS ( 
     { 
        column_name | ( column_list ) 
      | * 
      | PROPERTY ( { column_name }, 'property_name' )  
     } 
     , '<contains_search_condition>'
     [ , LANGUAGE language_term ]
   ) 

<contains_search_condition> ::= 
  { 
      <simple_term> 
    | <prefix_term> 
    | <generation_term> 
    | <generic_proximity_term> 
    | <custom_proximity_term> 
    | <weighted_term> 
    } 
  | 
    { ( <contains_search_condition> ) 
        [ { <AND> | <AND NOT> | <OR> } ] 
        <contains_search_condition> [ ...n ] 
  } 
<simple_term> ::= 
     { word | "phrase" }

<prefix term> ::= 
  { "word*" | "phrase*" }

<generation_term> ::= 
  FORMSOF ( { INFLECTIONAL | THESAURUS } , <simple_term> [ ,...n ] ) 

<generic_proximity_term> ::= 
  { <simple_term> | <prefix_term> } { { { NEAR | ~ } 
     { <simple_term> | <prefix_term> } } [ ...n ] }

<custom_proximity_term> ::= 
  NEAR ( 
     {
        { <simple_term> | <prefix_term> } [ ,…n ]
     |
        ( { <simple_term> | <prefix_term> } [ ,…n ] ) 
      [, <maximum_distance> [, <match_order> ] ]
     }
       ) 

      <maximum_distance> ::= { integer | MAX }
      <match_order> ::= { TRUE | FALSE } 

<weighted_term> ::= 
  ISABOUT 
   ( { 
        { 
          <simple_term> 
        | <prefix_term> 
        | <generation_term> 
        | <proximity_term> 
        } 
      [ WEIGHT ( weight_value ) ] 
      } [ ,...n ] 
   ) 

<AND> ::= 
  { AND | & }

<AND NOT> ::= 
  { AND NOT | &! }

<OR> ::= 
  { OR | | }

Examples






A. Using CONTAINS with <simple_term>


The following example finds all products with a price of $80.99 that contain the word "Mountain".










USE AdventureWorks2012;
GO
SELECT Name, ListPrice
FROM Production.Product
WHERE ListPrice = 80.99
   AND CONTAINS(Name, 'Mountain');
GO







B. Using CONTAINS and phrase with <simple_term>


The following example returns all products that contain either the phrase "Mountain" or "Road".










USE AdventureWorks2012;
GO
SELECT Name
FROM Production.Product
WHERE CONTAINS(Name, ' Mountain OR Road ')
GO







C. Using CONTAINS with <prefix_term>


The following example returns all product names with at least one word starting with the prefix chain in the Name column.










USE AdventureWorks2012;
GO
SELECT Name
FROM Production.Product
WHERE CONTAINS(Name, ' "Chain*" ');
GO







D. Using CONTAINS and OR with <prefix_term>


The following example returns all category descriptions containing strings with prefixes of either "chain" or "full".










USE AdventureWorks2012;
GO
SELECT Name
FROM Production.Product
WHERE CONTAINS(Name, '"chain*" OR "full*"');
GO







E. Using CONTAINS with <proximity_term>






Applies to: SQL Server 2012 through SQL Server 2014.




The following example searches the Production.ProductReview table for all comments that contain the word "bike" within 10 terms of the word "control" and in the specified order (that is, where "bike" precedes "control").










USE AdventureWorks2012;
GO
SELECT Comments
FROM Production.ProductReview
WHERE CONTAINS(Comments , 'NEAR((bike,control), 10, TRUE)');
GO







F. Using CONTAINS with <generation_term>


The following example searches for all products with words of the form ride: "riding," "ridden," and so on.










USE AdventureWorks2012;
GO
SELECT Description
FROM Production.ProductDescription
WHERE CONTAINS(Description, ' FORMSOF (INFLECTIONAL, ride) ');
GO







G. Using CONTAINS with <weighted_term>


The following example searches for all product names containing the words performance, comfortable, or smooth, and different weights are given to each word.










USE AdventureWorks2012;
GO
SELECT Description
FROM Production.ProductDescription
WHERE CONTAINS(Description, 'ISABOUT (performance weight (.8), 
comfortable weight (.4), smooth weight (.2) )' );
GO







H. Using CONTAINS with variables


The following example uses a variable instead of a specific search term.










USE AdventureWorks2012;
GO
DECLARE @SearchWord nvarchar(30)
SET @SearchWord = N'Performance'
SELECT Description 
FROM Production.ProductDescription 
WHERE CONTAINS(Description, @SearchWord);
GO







I. Using CONTAINS with a logical operator (AND)


The following example uses the ProductDescription table of the AdventureWorks2012 database. The query uses the CONTAINS predicate to search for descriptions in which the description ID is not equal to 5 and the description contains both the word Aluminum and the word spindle. The search condition uses the AND Boolean operator.










USE AdventureWorks2012;
GO
SELECT Description
FROM Production.ProductDescription
WHERE ProductDescriptionID <> 5 AND
   CONTAINS(Description, 'Aluminum AND spindle');
GO







J. Using CONTAINS to verify a row insertion


The following example uses CONTAINS within a SELECT subquery. Using the AdventureWorks2012 database, the query obtains the comment value of all the comments in the ProductReview table for a particular cycle. The search condition uses the AND Boolean operator.










USE AdventureWorks2012;
GO
INSERT INTO Production.ProductReview 
(ProductID, ReviewerName, EmailAddress, Rating, Comments) 
VALUES
(780, 'John Smith', 'john@fourthcoffee.com', 5, 
'The Mountain-200 Silver from AdventureWorks2008 Cycles meets and exceeds expectations. I enjoyed the smooth ride down the roads of Redmond');
 
-- Given the full-text catalog for these tables is Adv_ft_ctlg, 
-- with change_tracking on so that the full-text indexes are updated automatically.
WAITFOR DELAY '00:00:30';   
-- Wait 30 seconds to make sure that the full-text index gets updated.
 
SELECT r.Comments, p.Name
FROM Production.ProductReview r
JOIN Production.Product p 
ON
 r.ProductID = p.ProductID
 
AND r.ProductID = (SELECT ProductID
                  FROM Production.ProductReview
                  WHERE CONTAINS (Comments, 
                                 ' AdventureWorks2008 AND 
                                   Redmond AND 
                                   "Mountain-200 Silver" '));

GO







K. Querying on a document property






Applies to: SQL Server 2012 through SQL Server 2014.




The following query searches on an indexed property, Title, in the Document column of the Production.Document table. The query returns only documents whose Title property contains the string Maintenance or Repair.


 Note

For a property-search to return rows, the filter or filters that parse the column during indexing must extract the specified property. Also, the full-text index of the specified table must have been configured to include the property. For more information, see Search Document Properties with Search Property Lists.













Use AdventureWorks2012;
GO
SELECT Document FROM Production.Document
  WHERE CONTAINS(PROPERTY(Document,'Title'), 'Maintenance OR Repair');
GO